none
SQL Varbinary Update Command (OldValue = Null) RRS feed

  • Question

  • I am programming in VB2010. My project is an n-Tier, SQL 2008, WPF, WCF program.

    I am having the following problem: Once Sql DataTable Field "Fullname" has been changed in the UI, encrypted into a Byte Array, inserted into the Sql 2008 Datatable. Then changed again by user into "Nothing", and the UPDATE query enters the Empty data as SqlType.Binary.Null then afterwards I cannot update field "Fullname" anymore. (0 rows affected).

    Some background information:
    • A user clicks on an account name. WCF passes this request to the Business Logic Layer. Binary data is transferred back to the User Interface (UI), there it is decrypted and presented in a WPF Form. If field "Fullname" is NULL or Nothing a blanck Textbox is shown and the varibale holding Field: "Fullname" is set to Nothing. So far all is fine.
    • Next A user changes data in the UI, clicks save. Data is encrypted and savely transferred as Byte() to the Business Logic Layer. The UPDATE command is executed, data is saved as Varbinary(200) in the SQL Datatable. If Field "Fullname" is anything it will be encrypted in the UI, send to the Business Logic Layer and then entered into the SQL Datatable as Varbinary(200). If Field "Fullname is Nothing then it will enter System.Data.SqlType.Binary.Null. The UI gets updated with the new information. SO far all working as expected.
    • BUT when Field: "Fullname" contains NULL in the Sql DataTable and User is changing that in the UI into any data then my UPDATE command returns 0 rows affected. No errors, no exceptions, just no changes commited. I have no clue why? Maybe you have stumbled and struggled with this and can offer me some help.

    Please note: I don't want any workarounds like setting SQL column default values, or entering alternative data instead of binary.null (e.g. "ThisFieldIsNULL" and then work my way around.) I'd like to be able to enter a Binary.Null, and UPDATE that field with information proper.

    Please find below the Sql Update Command, and SqlCommand.

      Dim conSql As SqlClient.SqlConnection = DbAccess.GetImProAccessDbConnection 
    
      Dim sUpdateCommand As String = "UPDATE admUsers SET Username = @Username, Password = @Password, Fullname = @Fullname, Type = @Type, Accountstatus = @Accountstatus WHERE UserId = @UserId AND Username = @OldUsername AND Password = @OldPassword AND Fullname = @OldFullname AND Type = @OldType AND Accountstatus = @OldAccountstatus" 
    
      Dim cmdSql As New SqlClient.SqlCommand(sUpdateCommand, conSql) 
    
      With cmdSql.Parameters 
    
       .AddWithValue("UserId", OldUser.UserId) 
    
       .AddWithValue("Username", NewUser.Username) 
    
       .AddWithValue("Password", NewUser.Password) 
    
       If IsNothing(NewUser.Fullname) Then 
    
        .AddWithValue("Fullname", System.Data.SqlTypes.SqlBinary.Null) 
    
       Else 
    
        .AddWithValue("Fullname", NewUser.Fullname) 
    
       End If 
    
       .AddWithValue("Type", NewUser.Type) 
    
       .AddWithValue("Accountstatus", NewUser.Accountstatus) 
    
       .AddWithValue("OldUsername", OldUser.Username) 
    
       .AddWithValue("OldPassword", OldUser.Password) 
    
       If IsNothing(OldUser.Fullname) Then 
    
        .AddWithValue("OldFullname", System.Data.SqlTypes.SqlBinary.Null) 
    
       Else 
    
        .AddWithValue("OldFullname", OldUser.Fullname) 
    
       End If 
    
       .AddWithValue("OldType", OldUser.Type) 
    
       .AddWithValue("OldAccountstatus", OldUser.Accountstatus) 
    
      End With
    
    

    I also run SQL Profiler during development. Here is what happens:

    exec sp_executesql N'UPDATE admUsers SET Username = @Username, Password = @Password, Fullname = @Fullname, Type = @Type, Accountstatus = @Accountstatus WHERE UserId = @UserId AND Username = @OldUsername AND Password = @OldPassword AND Fullname = @OldFullname AND Type = @OldType AND Accountstatus = @OldAccountstatus',N'@UserId int,@Username varbinary(8),@Password varbinary(16),@Fullname varbinary(16),@Type varbinary(8),@Accountstatus varbinary(8),@OldUsername varbinary(8),@OldPassword varbinary(16),@OldFullname varbinary(8000),@OldType varbinary(8),@OldAccountstatus varbinary(8)',@UserId=2,@Username=0x842F0A8FBF793F3B,@Password=0xC267B089CF229EDCE0538BA932FC1FE9,@Fullname=0x2F3A3D5AD18A58554E8C0D4FAC86E2C4,@Type=0x0B887D303694F4C3,@Accountstatus=0x47E9C953D3D2F395,@OldUsername=0x842F0A8FBF793F3B,@OldPassword=0xC267B089CF229EDCE0538BA932FC1FE9,@OldFullname=NULL,@OldType=0x0B887D303694F4C3,@OldAccountstatus=0x47E9C953D3D2F395 
    
    

    I was wondering why a system.data.sqlTypes.Binary.Null value in the datatable is is initially recognized as varbinary(8000) and not as coded a ...Binary.Null But then I found out that in fixed-length binary column (BINARY), a value is padded with zeros to the full length of the field meaning (varbinary max = 8000). So that couldn't possibly be the reason why it is not allowing an Update of that field with any value other than 0x0000000000. However when I go into SSMS the value registered was not 0x0000000000 but rather NULL (not italics), so I dug further: Since I am not using SQl Enterprise but rather Std Edition I shouldn't have to worry about the data being truncated. So there could maybe be a padding issue since Std does use padding for a Binary Null, while it does not truncate and not pad for a varbinary.null

    I really don't know anymore where to search. I have posted this on MSDN, SQL Forums, Experts Exchange but there is nobody able to provide a clear solution.


    Tsadok
    • Moved by Bob Beauchemin Sunday, January 9, 2011 4:21 AM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Saturday, January 8, 2011 8:49 PM

Answers

  • BUT when Field: "Fullname" contains NULL in the Sql DataTable and User is changing that in the UI into any data then my UPDATE command returns 0 rows affected. No errors, no exceptions, just no changes commited. I have no clue why? Maybe you have stumbled and struggled with this and can offer me some help.

    SqlBinary.Null us basically just a strongly-typed DBNull.Value.  Comparisons against a NULL database value evaluate to unknown instead of true for false.  This three-way logic is dictated by ANSI standards and often causes confusion.

    Consider your UPDATE statement WHERE clause:

    UPDATE admUsers 
    SET ...
    WHERE ...
      AND Fullname = @OldFullname ...
    

    This will always update no rows when FullName is NULL becuase the "FullName = @OldFullName" will return UNKNOWN rather than true of false.  One way to address this is to use a compound expression with IS NULL:

    UPDATE admUsers 
    SET ...
    WHERE ...
    	AND ((Fullname = @OldFullname) OR (FullName IS NULL AND @OldFullname IS NULL))
    

    Alternatively, you could build your UPDATE statement predicate dynamically, including either "FullName = @OldFullName" or "FullName IS NULL" depending on whether or not the old value is NULL.

    I assume the purpose of comparing the old values is to implement optimistic concurrency.  If that is the case, consider adding a rowversion column to your table instead so that you need only check one (non-nullable) column value for the concurrency check. 

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Tsadok Blok Sunday, January 9, 2011 7:13 AM
    Saturday, January 8, 2011 9:20 PM

All replies

  • BUT when Field: "Fullname" contains NULL in the Sql DataTable and User is changing that in the UI into any data then my UPDATE command returns 0 rows affected. No errors, no exceptions, just no changes commited. I have no clue why? Maybe you have stumbled and struggled with this and can offer me some help.

    SqlBinary.Null us basically just a strongly-typed DBNull.Value.  Comparisons against a NULL database value evaluate to unknown instead of true for false.  This three-way logic is dictated by ANSI standards and often causes confusion.

    Consider your UPDATE statement WHERE clause:

    UPDATE admUsers 
    SET ...
    WHERE ...
      AND Fullname = @OldFullname ...
    

    This will always update no rows when FullName is NULL becuase the "FullName = @OldFullName" will return UNKNOWN rather than true of false.  One way to address this is to use a compound expression with IS NULL:

    UPDATE admUsers 
    SET ...
    WHERE ...
    	AND ((Fullname = @OldFullname) OR (FullName IS NULL AND @OldFullname IS NULL))
    

    Alternatively, you could build your UPDATE statement predicate dynamically, including either "FullName = @OldFullName" or "FullName IS NULL" depending on whether or not the old value is NULL.

    I assume the purpose of comparing the old values is to implement optimistic concurrency.  If that is the case, consider adding a rowversion column to your table instead so that you need only check one (non-nullable) column value for the concurrency check. 

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Tsadok Blok Sunday, January 9, 2011 7:13 AM
    Saturday, January 8, 2011 9:20 PM
  • And here is a link to handling NULL values in ADO.NET: http://msdn.microsoft.com/en-us/library/ms172138.aspx

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, January 8, 2011 9:25 PM
  • Dan, amazing - thank you for your quick reply. I have been waiting for some help for 8 days now. Tomorrow (GMT+2) I will adjust my code and apply your suggestions. I will report back once I am done. Thank you for your time!
    Tsadok
    Saturday, January 8, 2011 10:18 PM