CODEWITHSUNDEEP
sqlvb.netoledbstring-concatenationoledbcommand
Ahmed Faizan
Ahmed Faizan

Reputation: 456

Syntax error in query. Incomplete query clause in Max statement inside a function

I am trying to simply connect to a database. But the problem arises in the concatenation part of the Sql query.

When I do:

 cmd = New OleDbCommand("Select max(ID) from Table1", con)

There is no error but when I do 

 cmd = New OleDbCommand("Select max(ID) from'" & tablename & "'", con)

The vb.net error comes: Syntax error in query. Incomplete query clause.

Here is the full code

Function Get_Max(ByVal Get_Table_Name As String)
    Dim tablename As String = Get_Table_Name
    Dim found_max As Integer
    Call connect()
    con.Open()
    cmd = New OleDbCommand("Select max(ID) from'" & tablename & "'", con)
    dr = cmd.ExecuteReader
    While dr.Read
        found_max = dr(0)
    End While
    con.Close()
    MsgBox(found_max)

    Return found_max

End Function

Upvotes: 1

Views: 10111

Answers (3)

Steve
Steve

Reputation: 216293

Do not put single quotes around the variable tablename

cmd = New OleDbCommand("Select max(ID) from " & tablename, con)

Otherwise the tablename variable becomes a literal string and, from the database point of view you are requesting the MAX(ID) of the string 'Table1'.

A part from this you should be absolutely sure about the value of the variable tablename.

Do not allow the end user to directly type this value (at least let him choose between a list of predefined names).
This kind of code is very weak and open to Sql Injections.

And, as other answers have already outlined, a couple of Square Brackets around the table name are required if one or more of your tables contains a space or have the same name as a reserved keyword

cmd = New OleDbCommand("Select max(ID) from [" & tablename & "]", con)

Upvotes: 3

Adrian Wragg
Adrian Wragg

Reputation: 7401

You are adding in additional quote characters. Your initial string

cmd = New OleDbCommand("Select max(ID) from'" & tablename & "'", con)

is evaluating to

cmd = New OleDbCommand("Select max(ID) from'Table1'", con)

Instead, you should probably be using:

cmd = New OleDbCommand("Select max(ID) from [" & tablename & "]", con)

The [ and ] give you additional protection against reserved words matching table names, although not SQL query injection, to which this code may still be vulnerable.

Upvotes: 2

Codemunkeee
Codemunkeee

Reputation: 1613

change this line

cmd = New OleDbCommand("Select max(ID) from " & tablename & "", con)

to this

cmd = New OleDbCommand("Select max(ID) from " & tablename, con)

you just missed a space and remove the "'"

Upvotes: 2

Related Questions