SQL Server Parameters

[Guest]

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

 

[W.G. Ryan - MVP]

Aspnot - if I understand your problem correctly, it soudns like your update
statement is the problem. When you say that it's being passed as default,
not as the dataset field, I'm not sure I follow you based on your second
statement about defaults. If you have a parameter specified, but it's null
(not DbNull.Value, but null) nad you don't have a default value set up in
the procedure or t-sql block, then you'll get this error. Similarly, if you
have a missing param or misspelled it, same thing. The latter doesn't
appear to be the case, but it sounds like you have a null value. Trap
RowUpdating
(http://msdn2.microsoft.com/en-us/library/t8k4cyxy(en-US,VS.80).aspx) for
isntance on your adapter and do a
Debug.Assert(ValueForTheColumnThatMapstoMyParameter != null, "Whatever
Column has a null value"); // You may want to stick an identifier from the
key or another value that will help you trap the specific row down)

Also, i may have gotten ahead of myself... Are any rows updating or does
this fail immediately? You may just be missing the parameter/ misspelled
it, or missing the columnmapping in your update statement. If you would
post the Update code, that might show the problem.

HTH,

Bill

 

[Guest]

I set default values in the SP and that made it process fine. However, I
don't want to do that as there aren't "default" values. The Modified row of
the DataSet doesn't show any values for the fields that are not bound to any
controls on the form. These are the fields that are giving me the problems.

Let's say the DataSet has Customer_ID, CustomerName, Address, DumbField and
the form only has controls for Customer_ID, CustomerName and Address. When I
pass this DataSet back to the SP with my DataAdapter, DumbField is passed in
as a Null value. I understand what SQL Server is doing and it is doing as
expected. I am just unsure of how to best submit this information from my
application. I realize that I could just create a New SP that only takes in
Parameters for the fields that I show on the form, but since 99% of the
fields are in use on the form, I just figured it was easier to make a generic
Insert and Update SP that takes in all of the fields in the table as
parameters.

Is this what others have experienced with DataSets?

Hopefully this will help to explain the situation.

PS - There is only one row in this update, so I know exactly where this is
coming from.

 

[Guest]

Is this not the means that others use to do updates? Should I just Merge the
DataSet so that the DataTable that I submit to my DataAdapter shows all of
the data?

Any additional guidance would be greatly appreciated.

Thanks in advance.

 

[Guest]

Someone must have some insight on this...

Is this not the means that others use to do updates? Should I just Merge the
DataSet so that the DataTable that I submit to my DataAdapter shows all of
the data?

Any additional guidance would be greatly appreciated.

Thanks in advance.