January 6, 2009 at 6:09 pm
I use the following code to update a table:
Dim username As String
username = User.Identity.Name
Dim sql As String = "SELECT Games FROM UserStats WHERE username='" & username & "'"
Dim conn As New SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("CrackerConnectionString").ConnectionString
Dim cmd As New SqlCommand(sql, conn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)
Dim m As Integer = CInt(dt.Rows(0).Item(0))
Dim addcount As Integer
addcount = m + 1
cmd.Connection.Close()
sql = "UPDATE Userstats SET Games=" & addcount & " WHERE username='" & username & "'"
cmd.CommandText = sql
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Dispose()
da.Dispose()
dt.Dispose()
What is weird is that when I debug and go through the code, the second sql statement says
"UPDATE Userstats SET Games=1 WHERE Username='User'"
However when the sqlcommand is executed, 1 is not being inserted into the table. Instead it inserted 11! When I did it again, it was supposed to be 2 but the number was 22! Another time it updated it to 5!
What the heck is going on with this? Has anyone experienced these weird updates? Please help. Thanks a lot.
January 7, 2009 at 6:13 am
Probably, in this line of code:
addcount = m + 1
"m" is being treated as a string, not as an integer. That would explain how you would get "11" when you meant "1+1"
It's not something SQL Server is doing, so it must be in your code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2009 at 6:34 am
Is there a trigger on the table?
Maybe it's just a phrasing issue, but an update statement is not going to insert anything into the database. It's going to modify an existing row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply