Anyone?? Stored Procedure Parameter Not Supplied
I have a typed DataSet with say 50 fields in it. On the form, I only show 40. I have a stored procedure that takes in all 50 parameters. When I call SqlDataAdapter.Update(ds.Tables(0).GetChanges(DataRowState.Modified)), I get a SQL error back that Procedure XYZ is expecting Parameter '@myParameter' which was not supplied.
In looking at Profiler, I can see that this parameter is being passed to SQL Server as Default, not as the value that the field has in the Original DataSet.
Any suggestions?
- I know that I can set the Default values inside of SQL Server, but that isn't going to work for this situation.
Thanks in advance
Re: Stored Procedure Parameter Not Supplied
Re: Stored Procedure Parameter Not Supplied
I have been playing around trying to find a solution to this to no avail.
I realize that I can create another SP that takes in the parameters that are used on the form, but I wanted a generic SP for all of the fields in the table.
Surely, I cannot be the first one that has tried this.
Re: Stored Procedure Parameter Not Supplied
Specify the default to use if no parameter is passed.
Note that all you optional params must go at the end ...e.g.
Code:
CREATE PROCEDURE Foo (@Id As String,
@Comment As String = '' )
AS
--8< -----------------------
This proc must have an @Id parameter but if you don't pass in a @Comment parameter and empty string will be used.
Re: Stored Procedure Parameter Not Supplied
From my original post
Quote:
- I know that I can set the Default values inside of SQL Server, but that isn't going to work for this situation.
I realize that I can do this, but the data is being loaded from the database into the DataSet. Then the DataSet is bound to the controls on the form. When I go to update the database using the DataRowState.Modified DataTable, the fields that I do not show on my form do not have values in the DataTable. Setting defaults in SQL Server would mean overwriting data that should already be in the DataSet.
Let's say I have Customer_ID, CustomerName and DummyField in the table. I load the form and show Customer_ID and CustomerName on the form, but not DummyField (but DummyField is in my DataSet). The DataTable generated by DataRowState.Modified only has values for Customer_ID and CustomerName in it. I would also like for it to have the original value for DummyField that was loaded from the database.
Is this an unorthodox means of approaching this? Is there a better way?
Hopefully that makes more sense.
Re: Stored Procedure Parameter Not Supplied
Someone must have something to offer on this subject...
Re: Stored Procedure Parameter Not Supplied
This is really holding me up in the application that I am currently developing. Surely someone must have some information about this.