SQL syntax error in VB.net update statement

Question

I get a strange syntax error when I run it in VB:

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

Username checked before getting into this part and is definitely located in the database. This gives an exception, saying that there is a syntax error in the update statement. Does anyone know what happened?

Answer 1

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

Answer 2

LOGIN is a reserved word in SQL Server (used to manage accounts), so to use it in a query (i.e. a column name) you need to avoid it with [] , so use [LOGIN] as the field name.

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

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

 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

I'm sure Login is a reserved word, try changing Login to [Login]

Answer 4

Instead of showing how you build the statement, show us what is in SQLString when the statement is executed.

Also, try to include column and table names in the identifier code characters, which [and] for Microsoft and `(on the tilde) for many other databases.

Answer 5

Not knowing what you are using for your actual password and username, I assume that some character in one (or both) of them makes the sql statement end early. 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 6

I agree with some of the previous answers about using parameters (I gave + 1 for @Oded) and using [] with table names and field names (I gave +1 SQLMenace).

In concession, I think this is the most correct way to run your request:

 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 the surrounding words "login" and "password" with label marks so that the handler knows that they should not be displayed as reserved words.

So:

Update 'login' SET 'password'

Instead of single quotes, use a label (upper left key on the keyboard). I cannot demonstrate this correctly, as StackOverflow will treat it as a class if it is surrounded by label marks.