UPDATE statement in asp.net doesn't do anything

Question

I am making a settings page on my website where the user can change his email adress, name, etc. The problem is the update statement, it doesn't work, because nothing has changed in the database, but it doesn't give me an error, so I don't know what's up. When I check with breakpoints it shows that the parameters are getting the right values, and when i search on the internet I can't find anyone with the same problem.

I will paste the code of the update below:

Dim CmdUpdate As New OleDbCommand
        Dim Sqlstatement As String = "UPDATE tblUsers SET firstname = @firstname, lastname = @lastname, userPassword = @userPassword, email = @email, avatar = @avatar WHERE userID = @userID;"
        CmdUpdate.Connection = dbConn.cn
        CmdUpdate.CommandText = Sqlstatement

        CmdUpdate.Parameters.AddWithValue("firstname", txtFirstName.Text)
        CmdUpdate.Parameters.AddWithValue("lastname", txtLastName.Text)
        CmdUpdate.Parameters.AddWithValue("userID", Session("userID"))

        If txtPassword.Text = "" Then
            CmdUpdate.Parameters.AddWithValue("userPassword", Session("hashedpass"))
        Else
            CmdUpdate.Parameters.AddWithValue("userPassword", hash(txtPassword.Text))
        End If

        CmdUpdate.Parameters.AddWithValue("email", txtEmail.Text)
        CmdUpdate.Parameters.AddWithValue("avatar", strAvatar)

        dbConn.cn.Close()
        dbConn.cn.Open()
        CmdUpdate.ExecuteNonQuery()
        dbConn.cn.Close()

What is `dbConn.cn`? Hopefully not a static connections. Would be a bad idea in ASP.NET with enabled [connection-pool](http://msdn.microsoft.com/en-us/library/ms254502.aspx) because every request is a different thread. — Tim Schmelter, May 22 '13 at 16:20
it's a code file, if you are interested in it's content: Public Shared cn As New OleDbConnection(ConfigurationManager.ConnectionStrings.Item("AccessConnectionString").ConnectionString) the connection string is in the web.config — Joris_H, May 22 '13 at 16:25
I would avoid such "helper" classes, you should at least make it non-static and use the using statement. Have a look: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren — Tim Schmelter, May 22 '13 at 16:42

score 1 · Answer 1 · answered May 22 '13 at 16:17

1

You just have to include the "@" in your parameter add statements :

CmdUpdate.Parameters.AddWithValue("@firstname", txtFirstName.Text)

...and so on...

answered May 22 '13 at 16:17

Laurent S.

6,816
2
28
40

That didn't solve the issue, besides, I never use @'s in the addwithvalue, and my other commands all work. – Joris_H May 22 '13 at 16:27
ok, so the only explanation is that your where clause doesn't match any record. Try updating your code and replace the update by a select keeping the exact same where clause... I don't see any other good explanation if there's no error – Laurent S. May 22 '13 at 16:33

score 1 · Answer 2 · answered May 22 '13 at 16:18

1

You missed the '@'s in the sql-parameters:

 CmdUpdate.Parameters.AddWithValue("@firstname", txtFirstName.Text)

etc.

answered May 22 '13 at 16:18

Fabian Bigler

10,403
6
47
70

score 1 · Answer 3 · answered May 22 '13 at 16:18

1

Use

CmdUpdate.Parameters.AddWithValue("@firstname", txtFirstName.Text)

You were missing to add '@' in AddWithValue()

See Tutorial here

answered May 22 '13 at 16:18

Dhaval Marthak

17,246
6
46
68

score 1 · Accepted Answer · answered May 22 '13 at 16:30

1

Is it possible that the userid column just doesn't match, so your WHERE clause matches zero records and nothing gets updated? That could be caused by a number of things -- whitespace, character encoding, and so forth.

answered May 22 '13 at 16:30

Jim Dagg

2,044
22
29

I don't think so, because the session variable is loaded from the same database when the user loggs in. It also doen't work when I use the nickname in de WHERE clause. – Joris_H May 22 '13 at 16:35
@Joris: Can you please query the select 'manually' to doublecheck if it's really matching anything? – Fabian Bigler May 22 '13 at 16:38
1

ok, so I have replaced the userid parameter with 16, which is my userid, and now it works, so you were right about it having to do with the where clause, thanks! – Joris_H May 22 '13 at 16:42
1

i have found the problem, quite stupid actually, the parameters have to be in the right order, i placed the userid addwithvalue as last and now it works. – Joris_H May 22 '13 at 17:04

UPDATE statement in asp.net doesn't do anything

4 Answers4