Syntax error in FROM clause in Visual Basic

Question

i'm having trouble fixing this problem "Syntax error in FROM Clause"

Here's my codes:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    myConnetion = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\iponz18\Documents\Visual Studio 2012\Projects\CanteenBillingSystem\CanteenBillingSystem\CanteenBillingSystem.accdb")
    Dim table As String = "SELECT UserID FROM User WHERE UserID = " + TextBox1.Text + ";"
    da = New OleDbDataAdapter(table, myConnetion)
    ds = New DataSet
    da.Fill(ds, "User")
    If (ds.Tables("User").Rows.Count > 0) Then
        Form2.Show()
        Me.Close()
    End If
End Sub

The error is on this line:

da.Fill(ds, "User")

please help me out...

Have you tried wrapping the `User` table name with [] - ie. `SELECT UserID from [User]` .. User is normally a reserved keyword. — theduck, May 20 '15 at 16:32
Try to use backquotes. _Maybe_ user is a reserved keyword. Allthough I would doubt that, but well, if it is then backquotes will tell it that you are talking about a table. — , May 20 '15 at 16:33

score 3 · Answer 1 · edited May 23 '17 at 11:43

User is a reserved keyword in MS-Access. You need to put it between square brackets

Dim table As String = "SELECT UserID FROM [User] WHERE UserID = ...

Said that, your query has other problems. A better approach is this one:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim table As String = "SELECT UserID FROM User WHERE UserID = @uid"
    Using conn = New OleDbConnection(.....)
    Using da = New OleDbDataAdapter(table, conn )
         da.SelectCommand.Parameters.Add("@uid", OleDbType.VarWChar).Value = textBox1.Text
         ds = New DataSet
         da.Fill(ds, "User")
         If (ds.Tables("User").Rows.Count > 0) Then
            Form2.Show()
         End If
    End Using
    End Using

End Sub

I have changed your string concatenated query text to a parameterized query. This approach is more safe because it avoids Sql Injection and remove the need to escape properly your strings.

Also another very important advice that I feel to give is to avoid at all costs global variables to keep objects like a connection or an adapter around. This will give you a lot of trouble when something unexpected happens and your connection is leaved in unpredictable state.

Finally, if you just need to check if you have a UserID in your table, then there is no need to build an OleDbDataAdapter, a DataSet and fill it. You could just use ExecuteScalar method of the OleDbCommand

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim table As String = "SELECT UserID FROM User WHERE UserID = @uid"
    Using conn = New OleDbConnection(.....)
    Using cmd = New OleDbCommand(table, conn )
         cmd.Parameters.Add("@uid", OleDbType.VarWChar).Value = textBox1.Text
         conn.Open()
         Dim result = cmd.ExecuteScalar()
         if result IsNot Nothing then 
            Form2.Show()
         End If
    End Using
    End Using

End Sub

Syntax error in FROM clause in Visual Basic

1 Answers1