Anyone?? Stored Procedure Parameter Not Supplied

[Aspnot]

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

[Aspnot]

Any thoughts here?

[Aspnot]

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.

[Merrion]

Specify the default to use if no parameter is passed.
Note that all you optional params must go at the end ...e.g.

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.

[Aspnot]

From my original post

- 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.

[Aspnot]

Someone must have something to offer on this subject...

[Aspnot]

This is really holding me up in the application that I am currently developing. Surely someone must have some information about this.