Error when posting change to stored procedure dataset (SQL Server)

[stevel]

Using UniDAC 5.01, Delphi XE2 Update 4.1, SQL Server 2012

I have UniStoredProc with a SELECT stored procedure, and SQLDelete, SQLInsert and SQLUpdate properties set to respective stored procedures.

The respective stored procedures were created with a database tool (create SUID stored procedures).

I have form with controls (DBEdit) linked to the UniStoredProc. I open the form, then open the UniStoredProc. Then I make a change to one data field, then attempt to Post on the UniStoredProc.

Through DBMonitor I can see that it is calling the update stored procedure, but this is the error returned:

The formal parameter "@DatabaseID" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

DatabaseID is the primary key in the table.


Please help me to resolve this error urgently!

[stevel]

The way I solved this and removed the error was by doing this:

Code: Select all

procedure Tdatamod.uspCompanyData_SBeforeUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
var
  c: Integer;
begin
  if stUpdate in StatementTypes then
    for c := 1 to Params.Count - 1 do
      Params[c].ParamType := ptInput;

end;

But this is not the correct way to do it. It should work without doing this. You must investigate this issue and make a fix soon. This is an urgent issue for me.

Some more information:-

SQLUpdate property of the UniStoredProc component:

Code: Select all

{:RETURN_VALUE = CALL CompanyData_U;1 (:DatabaseID, :BusinessName, :OfficeAddress1, :OfficeAddress2, :OfficeSuburb, :OfficeState, :OfficePostcode, :PostalAddress1, :PostalAddress2, :PostalSuburb, :PostalState, :PostalPostcode, :PhoneNumber, :FaxNumber, :EmailAddress, :Website, :ABN, :ACL, :Notes)}

SQL being passed as seen in DBMonitor:

Code: Select all

{:RETURN_VALUE = CALL CompanyData_U;1 (:DatabaseID, :BusinessName, :OfficeAddress1, :OfficeAddress2, :OfficeSuburb, :OfficeState, :OfficePostcode, :PostalAddress1, :PostalAddress2, :PostalSuburb, :PostalState, :PostalPostcode, :PhoneNumber, :FaxNumber, :EmailAddress, :Website, :ABN, :ACL, :Notes)}

The stored procedure DDL in SQL Server:

Code: Select all

CREATE PROCEDURE CompanyData_U 
    @DatabaseID Int,
    @BusinessName NVarChar(200),
    @OfficeAddress1 NVarChar(200),
    @OfficeAddress2 NVarChar(200),
    @OfficeSuburb NVarChar(200),
    @OfficeState NVarChar(200),
    @OfficePostcode NVarChar(200),
    @PostalAddress1 NVarChar(200),
    @PostalAddress2 NVarChar(200),
    @PostalSuburb NVarChar(200),
    @PostalState NVarChar(200),
    @PostalPostcode NVarChar(200),
    @PhoneNumber NVarChar(200),
    @FaxNumber NVarChar(200),
    @EmailAddress NVarChar(200),
    @Website NVarChar(200),
    @ABN NVarChar(200),
    @ACL NVarChar(200),
    @Notes NVarChar(2000) AS
BEGIN
  UPDATE dbo.CompanyData SET
    BusinessName = @BusinessName,
    OfficeAddress1 = @OfficeAddress1,
    OfficeAddress2 = @OfficeAddress2,
    OfficeSuburb = @OfficeSuburb,
    OfficeState = @OfficeState,
    OfficePostcode = @OfficePostcode,
    PostalAddress1 = @PostalAddress1,
    PostalAddress2 = @PostalAddress2,
    PostalSuburb = @PostalSuburb,
    PostalState = @PostalState,
    PostalPostcode = @PostalPostcode,
    PhoneNumber = @PhoneNumber,
    FaxNumber = @FaxNumber,
    EmailAddress = @EmailAddress,
    Website = @Website,
    ABN = @ABN,
    ACL = @ACL,
    Notes = @Notes
  WHERE
    DatabaseID = @DatabaseID
END

The table DDL in SQL Server:

Code: Select all

CREATE TABLE dbo.CompanyData (
    DatabaseID Int IDENTITY NOT NULL,
    BusinessName NVarChar(200),
    OfficeAddress1 NVarChar(200),
    OfficeAddress2 NVarChar(200),
    OfficeSuburb NVarChar(200),
    OfficeState NVarChar(200),
    OfficePostcode NVarChar(200),
    PostalAddress1 NVarChar(200),
    PostalAddress2 NVarChar(200),
    PostalSuburb NVarChar(200),
    PostalState NVarChar(200),
    PostalPostcode NVarChar(200),
    PhoneNumber NVarChar(200),
    FaxNumber NVarChar(200),
    EmailAddress NVarChar(200),
    Website NVarChar(200),
    ABN NVarChar(200),
    ACL NVarChar(200),
    Notes NVarChar(2000), 
    CONSTRAINT [PK__Company __54F91D84414EAC47] PRIMARY KEY CLUSTERED (
      DatabaseID
    )
)
GO

Looking forward to your quickest response.

[AndreyZ]

Thank you for the information. We reproduced the problem and the investigation is in progress. We will notify you when we have any results.

[AndreyZ]

We have fixed this problem. This fix will be included in the next UniDAC build.