Unable to update a field that has a NULL value

I'm unable to update a field from page 1 if the starting value is NULL. Once the value has been set, by a different page, then I can go back to page 1 and update that field.

I know just above zero about SQL.

My update string, generated by Visual Web Studio is:

UpdateCommand="UPDATE [WaferTrack2] SET [Location] = @Location, [Slot] = @Slot, [Customer] = @Customer, [Fab] = @Fab, [Layer] = @Layer, [DR] = @DR, [Size] = @Size, [Notes] = @Notes, [Returned] = @Returned, [DateTimeStamp] = @DateTimeStamp, [Type] = @Type, [Owner] = @Owner, [AppsEng] = @AppsEng WHERE [ID] = @original_ID AND [Location] = @original_Location AND [Slot] = @original_Slot AND [Customer] = @original_Customer AND (([Fab] = @original_Fab) OR ([Fab] IS NULL AND @original_Fab IS NULL)) AND [Layer] = @original_Layer AND (([DR] = @original_DR) OR ([DR] IS NULL AND @original_DR IS NULL)) AND [Size] = @original_Size AND (([Notes] = @original_Notes) OR ([Notes] IS NULL AND @original_Notes IS NULL)) AND [Returned] = @original_Returned AND (([DateTimeStamp] = @original_DateTimeStamp) OR ([DateTimeStamp] IS NULL AND @original_DateTimeStamp IS NULL)) AND (([Type] = @original_Type) OR ([Type] IS NULL AND @original_Type IS NULL)) AND (([Owner] = @original_Owner) OR ([Owner] IS NULL AND @original_Owner IS NULL)) AND (([AppsEng] = @original_AppsEng) OR ([AppsEng] IS NULL AND @original_AppsEng IS NULL))"

Any ideas what I'm missing?

Thanks

Re: Unable to update a field that has a NULL value

The code is correct, please check the parameters values before the update, make sure that the parameters carrying the original values are null

Re: Unable to update a field that has a NULL value

Thanks for the reply. I've verified that the value in the table itself is NULL by going direclty to the table, but I don't know how to check the parameter and see its value. There is no code behind the page to set a breakpoint so I'm at a loss here.

Thanks

Re: Unable to update a field that has a NULL value

Lupin-III

I'm unable to update a field from page 1 if the starting value is NULL. Once the value has been set, by a different page, then I can go back to page 1 and update that field.

I know just above zero about SQL.

My update string, generated by Visual Web Studio is:

UpdateCommand="UPDATE [WaferTrack2] SET [Location] = @Location, [Slot] = @Slot, [Customer] = @Customer, [Fab] = @Fab, [Layer] = @Layer, [DR] = @DR, [Size] = @Size, [Notes] = @Notes, [Returned] = @Returned, [DateTimeStamp] = @DateTimeStamp, [Type] = @Type, [Owner] = @Owner, [AppsEng] = @AppsEng WHERE [ID] = @original_ID AND [Location] = @original_Location AND [Slot] = @original_Slot AND [Customer] = @original_Customer AND (([Fab] = @original_Fab) OR ([Fab] IS NULL AND @original_Fab IS NULL)) AND [Layer] = @original_Layer AND (([DR] = @original_DR) OR ([DR] IS NULL AND @original_DR IS NULL)) AND [Size] = @original_Size AND (([Notes] = @original_Notes) OR ([Notes] IS NULL AND @original_Notes IS NULL)) AND [Returned] = @original_Returned AND (([DateTimeStamp] = @original_DateTimeStamp) OR ([DateTimeStamp] IS NULL AND @original_DateTimeStamp IS NULL)) AND (([Type] = @original_Type) OR ([Type] IS NULL AND @original_Type IS NULL)) AND (([Owner] = @original_Owner) OR ([Owner] IS NULL AND @original_Owner IS NULL)) AND (([AppsEng] = @original_AppsEng) OR ([AppsEng] IS NULL AND @original_AppsEng IS NULL))"

Any ideas what I'm missing?

Thanks

First, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

Now, about this machine generated code, let's try to make it easier to read.

WHERE [ID] = @original_ID

AND [Location] = @original_Location

AND [Slot] = @original_Slot

AND [Customer] = @original_Customer

AND (([Fab] = @original_Fab) OR ([Fab] IS NULL AND @original_Fab IS NULL))

AND [Layer] = @original_Layer

AND (([DR] = @original_DR) OR ([DR] IS NULL AND @original_DR IS NULL)) AND [Size] = @original_Size AND (([Notes] = @original_Notes) OR ([Notes] IS NULL AND @original_Notes IS NULL))

AND [Returned] = @original_Returned

AND (([DateTimeStamp] = @original_DateTimeStamp) OR ([DateTimeStamp] IS NULL AND @original_DateTimeStamp IS NULL))

AND (([Type] = @original_Type) OR ([Type] IS NULL AND @original_Type IS NULL))

AND (([Owner] = @original_Owner) OR ([Owner] IS NULL AND @original_Owner IS NULL))

AND (([AppsEng] = @original_AppsEng) OR ([AppsEng] IS NULL AND @original_AppsEng IS NULL))"

You state that you are unable to update a field if the starting value is null. To which field do you refer?

Re: Unable to update a field that has a NULL value

Three suggestions for you Lupin-III

1In your aspx pagethere should be something like UpdateParametersplease set DefaultValue="" for all the stringsand set ConvertEmptyStringToNull=True

2Check whether you've set DataKeyNames for GridViewor DataKeys for other controls like Repeater or something else

3Converting the Update to TemplateFieldand then handle GridView_RowUpdatingplz use something like e.Command,e.Parameters to debug to see what's happening

Reguards

Re: Unable to update a field that has a NULL value

Dan - Thanks for helping on this. The AppsEng field is the one giving me trouble. It was added to the table after release.

Edit: Apparently any field that does not have data already is not saving the update.

Re: Unable to update a field that has a NULL value

Decker - Thanks for the suggestions. I'll take a look at this.

Decker Dong - MSFT

Three suggestions for you Lupin-III

1In your aspx pagethere should be something like UpdateParametersplease set DefaultValue="" for all the stringsand set ConvertEmptyStringToNull=True

2Check whether you've set DataKeyNames for GridViewor DataKeys for other controls like Repeater or something else

3Converting the Update to TemplateFieldand then handle GridView_RowUpdatingplz use something like e.Command,e.Parameters to debug to see what's happening

Reguards

Re: Unable to update a field that has a NULL value

Hello again

Welcome your feedback again

Re: Unable to update a field that has a NULL value

Dan Bracuk

Now, about this machine generated code, let's try to make it easier to read.

WHERE [ID] = @original_ID

AND [Location] = @original_Location

AND [Slot] = @original_Slot

AND [Customer] = @original_Customer

AND (([Fab] = @original_Fab) OR ([Fab] IS NULL AND @original_Fab IS NULL))

AND [Layer] = @original_Layer

AND (([DR] = @original_DR) OR ([DR] IS NULL AND @original_DR IS NULL))

AND [Size] = @original_Size AND (([Notes] = @original_Notes) OR ([Notes] IS NULL AND @original_Notes IS NULL))

AND [Returned] = @original_Returned

AND (([DateTimeStamp] = @original_DateTimeStamp) OR ([DateTimeStamp] IS NULL AND @original_DateTimeStamp IS NULL))

AND (([Type] = @original_Type) OR ([Type] IS NULL AND @original_Type IS NULL))

AND (([Owner] = @original_Owner) OR ([Owner] IS NULL AND @original_Owner IS NULL))

AND (([AppsEng] = @original_AppsEng) OR ([AppsEng] IS NULL AND @original_AppsEng IS NULL))"

What is the primary key of the table? If it's ID, once you have this:

WHERE [ID] = @original_ID

why is the rest of that stuff in your where clause?

Re: Unable to update a field that has a NULL value

Dan Bracuk

What is the primary key of the table? If it's ID, once you have this:

WHERE [ID] = @original_ID

why is the rest of that stuff in your where clause?

ID is the primary key and I don't know why all the extra stuff, as I said, i'm an SQL noob.

I removed the "stuff" and it works now.

Thanks