SQL syntax error in Update statement VB.net

Question

Hi, Im getting a strange syntax error when I run this in VB

        SQLString = "UPDATE Login SET Password = '" + PasswordTextBox.Text + "'"
        SQLString += " WHERE UserName = '" + UserNameTextBox.Text + "'"

The Username is checked before getting to this part and is definitly in the db. It gives an exception saying syntax error in update statement. Anyone have any ideas whats wrong?

Answer 1

I am pretty sure that Login is a reserved word, try changing Login to [Login]

Answer 2

LOGIN is a reserved word in SQL Server (used for login account management), so in order to use it in a query (i.e. a column name) you need to escape it with [], so use [LOGIN] as the field name.

You should never use string concatenation and pass that to your SQL database, as you are exposing yourself to SQL Injection attacks.

You should use the SqlCommand object and pass through parameters. See this article on how to do so.

SQLString = "UPDATE [Login] SET Password = @password "
SQLString += " WHERE UserName = @userName"

...

dbCommand.Parameters.Add("@password", SqlDbType.VarChar, 50)
dbCommand.Parameters["@password"].Value = PasswordTextBox.Text

dbCommand.Parameters.Add("@userName", SqlDbType.VarChar, 50)
dbCommand.Parameters["@userName"].Value = UserNameTextBox.Text

Answer 3

Instead of showing how you're building the statement, show us what's in SQLString when the statement is executed.

Also, try enclosing the column and table names in the identifier quote characters, which are [ and ] for Microsoft, and ` (on the tilde key) for many others databases.

Answer 4

Without knowing what you are using for your actual password and username, my guess is that some character in one (or both) of those are causing the sql statement to end prematurely. You should really use parameters when executing sql like this.

Take a look at this: http://msdn.microsoft.com/en-us/library/ms998271.aspx

Answer 5

Password is a reserved word so [Password] fixes it, my lecturer fixed to for me :)

Answer 6

I agree with some of the previous answers, about using parameters (I gave + 1 to @Oded) and using [ ] with tablenames and fieldnames (I gave +1 to SQLMenace).

In conclussion, I think this is the most correct way to launch your query:

using(SqlConnection connection = new SqlConnection("<your connection string>"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand();

        command.Connection = connection;

        command.CommandText = "UPDATE [Login] SET [Password] = @PasswordParameter WHERE [UserName] = @UserNameParameter";
        command.Parameters.AddWithValue("@PasswordParameter", PasswordTextBox.Text);
        command.Parameters.AddWithValue("@UserNameParameter", UserNameTextBox.Text);

        command.ExecuteNonQuery();
    }

Answer 7

I would recommend surrounding the words "login" and "password" with tick marks to let the handler know that they are not to be rendered as reserved words.

So:

Update 'login' SET 'password'

But rather than single-quotes, use the tick mark (upper-left key on the keyboard). I can't demonstrate it correctly as StackOverflow will treat it as a class if it's surrounded in tick marks.