Re: SQL Server Parameters
- From: Aspnot <NOSPAM_Aspnot_NOSPAM@xxxxxxxxx>
- Date: Fri, 21 Oct 2005 17:04:01 -0700
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.
"W.G. Ryan - MVP" wrote:
> 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
> "Aspnot" <NOSPAM_Aspnot_NOSPAM@xxxxxxxxx> wrote in message
> news:49487383-181B-444A-B0D7-00FA9CE50F5D@xxxxxxxxxxxxxxxx
> >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
>
>
>
.
- Follow-Ups:
- Re: SQL Server Parameters
- From: Aspnot
- Re: SQL Server Parameters
- Prev by Date: Help with using DataView to insert Record..Please!
- Next by Date: Re: Help with using DataView to insert Record..Please!
- Previous by thread: Help with using DataView to insert Record..Please!
- Next by thread: Re: SQL Server Parameters
- Index(es):
Relevant Pages
|
