"Syntax error in FROM clause"

[Richard Alvarez]

good day guys, please help me with this code that i have. i'm having a "Syntax error in FROM clause" error. Here is my code, any help is much appreciated. Thank you in advance.

 Dim strSQL As String = "Select * from " & Trim(cboID.Text)
Dim rsData as ADODB.Recordset
 
If rsData.State = ConnectionState.Open Then rsData.Close()
rsData.Open(strSQL, DBConn, ADODB.CursorTypeEnum.adOpenKeyset, _
ADODB.LockTypeEnum.adLockPessimistic)

[Rabbit]

I have no idea what cboID.Text is. Did you output the string to make sure it's correct? If your table has spaces, you need to use the square bracket qualifiers.

[Richard Alvarez]

thank you so much for the quick reply sir, you've proven yourself a great help even on the first time that I ask question on this forum. cboID.text gives users the previledge to select what table he/she would use.

[Rabbit]

Did you output the query string to see if it's correct? What are these table names?

[Richard Alvarez]

yes i did and i did also try to assign a variable to hold the query string but still no luck.

[Rabbit]

What are these table names?

[Richard Alvarez]

table names are created by the user at runtime.

Dim sql As String = "CREATE TABLE " & txtTabelName.text & "
    (StudentId INTEGER CONSTRAINT PkeyMyId PRIMARY KEY,"
    + "Name CHAR(50), Address CHAR(255), Contact INTEGER)"
 
    cmd = New OdbcCommand(sql, conn)
 
    cmd.ExecuteNonQuery()

[Rabbit]

Read this and post the results: http://bytes.com/topic/access/insigh...bug-sql-string

Also, I noticed you have DBConn in your code but it's not defined anywhere.

[Richard Alvarez]

That was my mistake, the code that I posted was just a part of the private sub. Here's the whole code:

Private Sub cboID_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboID.Click
        Try
            Dim rsData As New ADODB.Recordset
            Dim ListUsers As New ListViewItem
 
            If rsData.State = ConnectionState.Open Then rsData.Close()
            rsData.Open("Select * from '" & cboID.Text & "'", DBConn, _
                        ADODB.CursorTypeEnum.adOpenKeyset, _
                        ADODB.LockTypeEnum.adLockPessimistic)
 
            lvExmView.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
            lvExmView.Items.Clear()
 
            Do Until rsData.EOF
                ListUsers = lvExmView.Items.Add(rsData.Fields("ID").Value)
                ListUsers.SubItems.Add(rsData.Fields("Question").Value)
                ListUsers.SubItems.Add(rsData.Fields("A").Value)
                ListUsers.SubItems.Add(rsData.Fields("B").Value)
                ListUsers.SubItems.Add(rsData.Fields("C").Value)
                ListUsers.SubItems.Add(rsData.Fields("D").Value)
                ListUsers.SubItems.Add(rsData.Fields("Ans").Value)
                rsData.MoveNext()
            Loop
            'lvExmView.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)
 
        Catch ex As Exception
            MsgBox(Err.Description & vbNewLine & vbNewLine & _
                Err.Number & " " & Err.Source, MsgBoxStyle.Critical, "Error")
            Err.Clear()
        End Try
    End Sub

[Richard Alvarez]

DBConn is a public declared variable (Public DBConn as ADODB.Recordset)

I same thing that I first use to debug my code is to use a messagebox to dispplay the real value of the string. I even tried this

Dim strSQL as string
 
strSQL= Chr(34) & "Select * from " & cboID.text & Chr(34)
msgbox(strSQL)

and got this output "Select * from TableName" with the quotes but still the same error goes in.

[Rabbit]

In your full code, there are single quotes around the table name, if anything, they should be an open square bracket and a close square bracket.

[Richard Alvarez]

You mean something like this,

 "Select * from " & [cboID.text], dbconn

[Rabbit]

Not quite. Your shortened code and your full code is not the same. In your full code, you have this

"Select * from '" & cboID.Text & "'"

Which means the end query is this

Select * from 'tableName'

That is incorrect. If you're using the single quotes because there are spaces in the table name, then it needs to be brackets and not single quotes.

"Select * from [" & cboID.Text & "]"

[Richard Alvarez]

I know that this is a dumb question but what is this "[]" for? (completely new to it :p)

[Rabbit]

To tell the engine that what's inside is an object. It's required only for those items that use reserved keywords or spaces in the name. If none of your table names have spaces in the name, then you don't need it. If my table is named Purchase Orders, I can't use select * from purchase orders. That will error out, I need to use select * from [purchase orders].

You may not even need it. But you definitely can't use the single quotes that you're using in your code. Single quotes are string demarcaters.

[Richard Alvarez]

Thank you so much, i really appreciate your help sir. I really do because this is the first time i learn about this bracket thing. Thank you again.

[Rabbit]

Not a problem, good luck.