(VB) SQL not updating my database

Question

I have 2 Update SQLs in my program, one for a Students table and one for a Users table. the SQL for my students table works fine and saved the new data to the database but the SQL to input my data into the users table doesn't. It doesn't give me any errors when I run the program but when I end the program and check my database nothing has changed.

Dim UpdateComm As New OleDb.OleDbCommand
Dim dbAdap As New OleDb.OleDbDataAdapter

ConnectToDB()

Dim sqlUpdateUser As String = "UPDATE usersTable SET usersTable.[Password] = " & _
  Chr(39) & txtNewPass.Text & Chr(39) & _
  ", usersTable.Username = " & Chr(39) & Username & Chr(39) & _
  " WHERE (((userTable.UserID) = " & Chr(39) & CurrentUID & Chr(39) & "));"

UpdateComm = New OleDb.OleDbCommand(sqlUpdateUser, dbConn)
UpdateComm.Parameters.AddWithValue("@p1", txtNewPass.Text)
UpdateComm.Parameters.AddWithValue("@p2", Username)
UpdateComm.ExecuteNonQuery()

Thanks.

Learn how to use **parametrized queries** in order to **avoid SQL injection attacks** and improve your performance! See [this other SO question and answer](http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements/7505842#7505842) — marc_s, Feb 10 '13 at 17:37
Have you analysed what are you getting in your Where clause? See what CurrentUID are you getting and if that CurrentUID is present in database? If possible try to have string named as Qry and put update statement in it and then debug to see what Qry returns to you and try to run same update statement in database directly — DevelopmentIsMyPassion, Feb 10 '13 at 17:41
Sorry can you just print sqlUpdateUser and try to run same query in database — DevelopmentIsMyPassion, Feb 10 '13 at 17:42
I made a query and copied the SQL into the database (changing the variables to database records). I set the User ID and pressed run. I got a message box asking for a parameter value for my User ID so I entered 7 (the same value i used in the SQL query so is there an error here?) then I got told i was about to edit 5 rows which I pressed no to as that didn't seem right. — Edd B, Feb 10 '13 at 17:52
Also your Update statement bit odd to me. Why dont you write like link provide by user marc_s. Just pass parameter to update statement. — DevelopmentIsMyPassion, Feb 10 '13 at 18:07
sorry for the late response, heres the query: UPDATE usersTable SET usersTable.[Password] = 'qwertyuiop', usersTable.Username = '06JSmith' WHERE (((userTable.UserID) = 9)); Also, what do you mean "link provide by user marc_s. Just pass parameter to update statement"? — Edd B, Feb 10 '13 at 18:13
check your where clause.. It says (userTable.UserID) but in your update query you are referring to usersTable.. is it a typo? — DevelopmentIsMyPassion, Feb 10 '13 at 18:21
You are setting the UserID between single quotes. Is it really a text column? — Olivier Jacot-Descombes, Feb 10 '13 at 18:31
@EddB If possible acknowledge my answer so it would make me happy :) — DevelopmentIsMyPassion, Feb 10 '13 at 18:33
You are adding parameters (@p1 and @p2) but your query text does not contains such parameters. — Olivier Jacot-Descombes, Feb 10 '13 at 18:42
@EddB you can see that i have posted answer. you can see that there is number 0 and up and down arrow. Select up arrow and select right icon below which will say click to accept answer. Please do it — DevelopmentIsMyPassion, Feb 10 '13 at 19:48

score 0 · Answer 1 · answered Feb 10 '13 at 18:24

As stated in the comments you should parameterize your SQL commands to avoid SQL injection attacks. They also generally eliminate errors caused through incorrect string concatenation. Your code should look like the following:

Dim UpdateComm As New OleDb.OleDbCommand
Dim dbAdap As New OleDb.OleDbDataAdapter

ConnectToDB()

Dim sqlUpdateUser As String = "UPDATE usersTable SET [Password] = @pwd, Username = @userName WHERE UserID = @currentUID;"

UpdateComm = New OleDb.OleDbCommand(sqlUpdateUser, dbConn)
UpdateComm.CommandType = CommandType.Text;
UpdateComm.Parameters.AddWithValue("@pwd", txtNewPass.Text)
UpdateComm.Parameters.AddWithValue("@userName", Username)
UpdateComm.Parameters.AddWithValue("@currentUID", CurrentUID)
UpdateComm.ExecuteNonQuery()

You don't seem to be adding the third CurrentUID to your command parameters. I have also removed the alias you were applying to the column names - they aren't required as ou are only dealing with 1 table.

score 0 · Answer 2 · answered Feb 10 '13 at 18:32

Try this

Dim connection As New SqlConnection(connectionInfo)
Dim sqlUpdateUser As String = "UPDATE usersTable SET [Password] = @pwd, Username=        @userName WHERE UserID = @currentUID;"
Dim UpdateComm  As SqlCommand(sqlUpdateUser , connection)

 With UpdateComm .Parameters
   .AddWithValue("@pwd", txtNewPass.Text)
   .AddWithValue("@userName", Username)
   .AddWithValue("@currentUID", CurrentUID)
   .ExecuteNonQuery()
 End With

(VB) SQL not updating my database

2 Answers2