Increment a entry in an sql table via asp.net by 1
I have a database containing usernames and "points". I need to increment the "points" at will. I have the table connected to a sqldatasource.
Here is my current code:
With Users
.UpdateCommand = "UPDATE Users SET points = points + 1"
.Update()
End With
This works great, except, instead of just changing the entry with the username specified by a QueryString, it changes all of the entries and adds one.
The sqldatasource has the following select command:
SelectCommand="SELECT * FROM [Users] WHERE ([username] = @username)"
This command makes it so that only entries with the querystring specified username are displayed. This is really my first attempt at SQL. Any ideas or solutions would be appreciated.
--EDIT--
Thanks for the help Oded, here is my final solution that worked :)
开发者_开发技巧With Users
.UpdateParameters.Add(New Parameter("username", System.Data.DbType.String, My.Request.QueryString("username")))
.UpdateCommand = "UPDATE Users SET points = points + 1 WHERE ([username] = @username)"
.Update()
End With
You need to specify that only that user row should be updated:
.UpdateCommand = "UPDATE Users SET points = points + 1 WHERE ([username] = @username)"
When a WHERE
clause is not specified, the UPDATE
will apply to all existing rows, as you have seen.
Edit:
This is an example of how to add a username parameter:
.UpdateCommand.Parameters.Add("@username", SqlDbType.NVarChar, 40, "The User Name");
Add a where clause to your UpdateCommand:
UpdateCommand = "UPDATE Users SET points = points + 1 WHERE ([username] = @username)"
精彩评论