Error when I run my program; Incorrect syntax near the keyword 'User'

Question

I am getting the following error when I run my code:

Incorrect syntax near the keyword 'User'.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

This is my code:

If k = 1 And b = 1 And c = 1 And d = 1 And f = 1 And g = 1 And h = 1 Then
    Dim connection As SqlConnection = New SqlConnection()
    connection.ConnectionString = "Data Source=DESKTOP-9G2397U;Initial Catalog=shopping;Integrated Security=True"
    connection.Open()
    Dim da As SqlDataAdapter = New SqlDataAdapter
    Dim ds As DataSet = New DataSet()
    Dim sql As String
    sql = ("select * from User where username='" & txt_username.Text & "'")
    da = New SqlDataAdapter(sql, connection)
    da.Fill(ds, "User")
    If ds.Tables("User").Rows.Count = 1 Then
        MessageBox.Show("This Username Already Exists, Re-enter A New One", "Username Already Exists")

What does this error mean and how can I fix it?

For your reference, here's the complete set of [SQL Server Reserved Words](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql). It's usually best to avoid using any of them for naming tables/columns/stored procedures, etc. (And usually easily avoided. Is your table designed to hold a single instance? If not, why not use a *plural* name for it?) — Damien_The_Unbeliever, May 11 '17 at 06:38
That's literally a [Bobby Tables](https://xkcd.com/327/). It's a good thing that `User` is a reserved keyword that prevents you from executing this query. Imagine what would happen if someone entered `FU'); DELETE FROM [User];--` as a UserName — Panagiotis Kanavos, May 11 '17 at 07:41

Bugs · Answer 1 · 2017-05-11T08:22:03.410

User is a reserved keyword and as such you need to place brackets around it like so; [User]. See Reserved Keywords - Transact-SQL for the full list.

I strongly recommend you use SQL parameters. This is to reduce syntax issues but more importantly stops SQL injection. See Bobby Tables for more details on this.

Also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

You also don't need to use a SqlDataAdapter. Instead as pointed out by Panagiotis Kanavos you can just check to see if ExecuteReader has any rows.

All together your code would look something like:

Using con As New SqlConnection("Data Source=DESKTOP-9G2397U;Initial Catalog=shopping;Integrated Security=True"),
      cmd As New SqlCommand("SELECT * FROM [User] WHERE username=@username", con)

    con.Open()

    cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = txt_username.Text

    If cmd.ExecuteReader().HasRows Then
        MessageBox.Show("This Username Already Exists, Re-enter A New One", "Username Already Exists")
    End If
End Using

score -1 · Accepted Answer · answered May 11 '17 at 06:14

-1

Note: Your query is possible for SQL Injection attack, use with parameterized.

Can you add square brackets [ ] around the User table, like

sql = ("select * from [User] where username='" & txt_username.Text & "'")

Here User is the reserved keyword.

answered May 11 '17 at 06:14

Arulkumar

12,966
14
47
68

it worked but i got a new error on : com.ExecuteNonQuery() saying: An unhandled exception of type 'System.FormatException' occurred in System.Data.dll Additional information: Failed to convert parameter value from a String to a Int32. thank you for your help – Myriam Harfouch May 11 '17 at 06:33
In your code, some where you are trying to store a string value into the integer variable, re-check the value assign part. – Arulkumar May 11 '17 at 06:39
@Arulkumar please don't post how to enable SQL Injection attacks, even if the OP asks for this. Post a *proper* answer like Bugs did. This is a dangerous answer – Panagiotis Kanavos May 11 '17 at 07:44

Error when I run my program; Incorrect syntax near the keyword 'User'

2 Answers2