EDN Admin
Well-known member
Hi,<br/>
I ran into a weird problem and I am staring myself blind. If anyone can help out would be great. Thank you in advance.<br/>
I am using VISUAL BASIC 2010, SQL Server 2008. I need to update a Table with SQlDbType Varbinary. Just for sake of Murphy loving me I gave it Varbinary(100) - while max. field lenght is 20 Non Ascii Characters on the UI Side.
<br/>
<br/>
User is changing data in a form, data gets encrypted (into a Byte Array) then sent to the BL of the program, the business logic talks to SQL Server and fields in the Table are updated nicely with new binary values.<br/>
<br/>
A problem arises however with a field that is allowed NULL values in the data table. I can retrieve NULL values from the Database and assign them the "Nothing" value. (User Interface shows a nice blank field). Once the user however changes that empty
field (containing "Nothing" as value) with any data then my cmdSql.ExecuteNonquery reports no changes. (rowsaffected = 0)
<br/>
<br/>
I strongly suspect that I am making a mistake in the parameters of the SQlCommand of the OldFullname value, but as I said I am staring myself blind. Please review my code below and tell me where I am making that mistake...<br/>
<br/>
FYI: OldFullname cant be "Nothing", and its current field is SQL NULL. All fields (except UserId) are Varbinary. Update succeeds as long as OldFullname SQL field contains data - only not when it is NULL. I dont want to set a standard value on the
column (NULL or such). <br/>
<br/>
<pre> Public Function SaveToDatabase(ByVal OldUser As blProgramAccess.userEncrypted, ByVal NewUser As blProgramAccess.userEncrypted) As Boolean
Dim bSaved As Boolean
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
.Add("Fullname", SqlDbType.VarBinary, -1)
.AddWithValue("Fullname", DBNull.Value)
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
.Add("OldFullname", SqlDbType.VarBinary, -1)
.AddWithValue("OldFullname", DBNull.Value)
Else
.AddWithValue("OldFullname", OldUser.Fullname)
End If
.AddWithValue("OldType", OldUser.Type)
.AddWithValue("OldAccountstatus", OldUser.Accountstatus)
End With
Try
conSql.Open()
If cmdSql.ExecuteNonQuery() = 0 Then
set event concurrency error
bSaved = False
Else
bSaved = True
End If
Catch eSql As SqlClient.SqlException
set event sql error
bSaved = False
Catch ex As Exception
set event general exceptions
bSaved = False
Finally
conSql.Close()
End Try
Return bSaved
End Function[/code]
<br/>
View the full article
I ran into a weird problem and I am staring myself blind. If anyone can help out would be great. Thank you in advance.<br/>
I am using VISUAL BASIC 2010, SQL Server 2008. I need to update a Table with SQlDbType Varbinary. Just for sake of Murphy loving me I gave it Varbinary(100) - while max. field lenght is 20 Non Ascii Characters on the UI Side.
<br/>
<br/>
User is changing data in a form, data gets encrypted (into a Byte Array) then sent to the BL of the program, the business logic talks to SQL Server and fields in the Table are updated nicely with new binary values.<br/>
<br/>
A problem arises however with a field that is allowed NULL values in the data table. I can retrieve NULL values from the Database and assign them the "Nothing" value. (User Interface shows a nice blank field). Once the user however changes that empty
field (containing "Nothing" as value) with any data then my cmdSql.ExecuteNonquery reports no changes. (rowsaffected = 0)
<br/>
<br/>
I strongly suspect that I am making a mistake in the parameters of the SQlCommand of the OldFullname value, but as I said I am staring myself blind. Please review my code below and tell me where I am making that mistake...<br/>
<br/>
FYI: OldFullname cant be "Nothing", and its current field is SQL NULL. All fields (except UserId) are Varbinary. Update succeeds as long as OldFullname SQL field contains data - only not when it is NULL. I dont want to set a standard value on the
column (NULL or such). <br/>
<br/>
<pre> Public Function SaveToDatabase(ByVal OldUser As blProgramAccess.userEncrypted, ByVal NewUser As blProgramAccess.userEncrypted) As Boolean
Dim bSaved As Boolean
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
.Add("Fullname", SqlDbType.VarBinary, -1)
.AddWithValue("Fullname", DBNull.Value)
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
.Add("OldFullname", SqlDbType.VarBinary, -1)
.AddWithValue("OldFullname", DBNull.Value)
Else
.AddWithValue("OldFullname", OldUser.Fullname)
End If
.AddWithValue("OldType", OldUser.Type)
.AddWithValue("OldAccountstatus", OldUser.Accountstatus)
End With
Try
conSql.Open()
If cmdSql.ExecuteNonQuery() = 0 Then
set event concurrency error
bSaved = False
Else
bSaved = True
End If
Catch eSql As SqlClient.SqlException
set event sql error
bSaved = False
Catch ex As Exception
set event general exceptions
bSaved = False
Finally
conSql.Close()
End Try
Return bSaved
End Function[/code]
<br/>
View the full article