locked
Unable to update a field that has a NULL value RRS feed

  • Question

  • User673814180 posted

    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

    Tuesday, February 21, 2012 3:32 PM

Answers

  • User-1407477457 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2012 9:48 PM
  • User673814180 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 29, 2012 4:30 PM

All replies

  • User1950091109 posted

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

    Tuesday, February 21, 2012 3:48 PM
  • User673814180 posted

    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

    Tuesday, February 21, 2012 5:32 PM
  • User-1407477457 posted

    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?

    Tuesday, February 21, 2012 5:41 PM
  • User3866881 posted

    Three suggestions for you  Lupin-III:)

    1)In your aspx page,there should be something like UpdateParameters,please set DefaultValue="" for all the strings,and set ConvertEmptyStringToNull=True。

    2)Check whether you've set DataKeyNames for GridView,or DataKeys for other controls like Repeater or something else。

    3)Converting the Update to TemplateField,and then handle GridView_RowUpdating,plz use something like e.Command,e.Parameters to debug to see what's happening……

    Reguards!

    Wednesday, February 22, 2012 8:42 PM
  • User673814180 posted

    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.

    Thursday, February 23, 2012 10:18 AM
  • User673814180 posted

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

    Three suggestions for you  Lupin-III:)

    1)In your aspx page,there should be something like UpdateParameters,please set DefaultValue="" for all the strings,and set ConvertEmptyStringToNull=True。

    2)Check whether you've set DataKeyNames for GridView,or DataKeys for other controls like Repeater or something else。

    3)Converting the Update to TemplateField,and then handle GridView_RowUpdating,plz use something like e.Command,e.Parameters to debug to see what's happening……

    Reguards!

    Thursday, February 23, 2012 10:19 AM
  • User3866881 posted

    Hello again:)

    Welcome your feedback again!

    Thursday, February 23, 2012 7:56 PM
  • User-1407477457 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2012 9:48 PM
  • User673814180 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 29, 2012 4:30 PM