CODEWITHSUNDEEP
sqlselectms-access-2010oledbvb.net-2010
Cory Short
Cory Short

Reputation: 1

"Syntax error in query. Incomplete query clause." when doing SELECT * FROM @Table WHERE @Field LIKE @Record

Dim TableName As String
Dim ParamField As String
Dim ParamRecord As String
TableName = SearchBoxTableCmb.Text.ToString    (the value for SearchBoxTableCmb.Text.ToString is "User")
ParamField = SearchBoxFieldCmb.Text.ToString   (the value for SearchBoxFieldCmb.Text.ToString is "Username")
ParamRecord = SearchBoxTxt.Text                (the value for SearchBoxTxt.Text is "Cshort")
Oledb.AddParam("@Table", TableName)
Oledb.AddParam("@Field", ParamField)
Oledb.AddParam("@Record", ParamRecord)

DataGrid.DataSource = Oledb.ExecQuery("SELECT * FROM @Table WHERE @Field LIKE @Record")

Below is the called Class

Imports System.Data.OleDb
Imports System.Data
Public Class OledbControl
Private OledbConn As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;" & _
                                         "Data Source = '" & Application.StartupPath & "\AS_Service_Database.accdb'")
Private OledbCmd As New OleDbCommand

Public OledbDataAdap As New OleDbDataAdapter
Public OledbDataSet As New DataSet
Public Params As New List(Of OleDbParameter)
Public RecordCount As Integer
Public Exception As String

Public Function ExecQuery(Query As String) As Object
    Try
        OledbConn.Open()

        OledbCmd = New OleDbCommand(Query, OledbConn)
        Params.ForEach(Sub(x) OledbCmd.Parameters.Add(x))
        Params.Clear()
        OledbDataSet = New DataSet
        OledbDataAdap = New OleDbDataAdapter(OledbCmd)
        OledbDataAdap.Fill(OledbDataSet, "TableName")
        Dim DataTable As DataTable = OledbDataSet.Tables("TableName")
        ExecQuery = DataTable

        OledbConn.Close()
    Catch ex As Exception
        Exception = ex.Message

    End Try
    If OledbConn.State = ConnectionState.Open Then OledbConn.Close()
End Function

Public Sub AddParam(Name As String, Value As Object)
    Dim NewParam As New OleDbParameter(Name, Value)
    Params.Add(NewParam)

End Sub

End Class

I am trying to use a text box and a pair of combo boxes to search within a database but when i Execute the Query it creates the exception message "Syntax error in query. Incomplete query clause." But i cannot seem to find why what is wrong with the Query

Upvotes: 0

Views: 2002

Answers (3)

Marcelo Ruiz
Marcelo Ruiz

Reputation: 93

I believe you should put between quotes the parameter you are using with the LIKE:

DataGrid.DataSource = Oledb.ExecQuery("SELECT * FROM @Table WHERE @Field LIKE '@Record'")

Upvotes: -2

cha
cha

Reputation: 10411

You can't bind the table names or column names to a parameter. Instead, use the dynamic query combined from the variables, like this:

First of all, I think you need to extend your Oledb class with a new method:

Public Sub AddParam(Name As String, Value As Object, type As OleDbType)
    Dim NewParam As New OleDbParameter(Name, type)
    NewParam.Value = Value
    NewParam.Direction = ParameterDirection.Input
    Params.Add(NewParam)

Then you need to call it passing the OleDbType.VarChar as type

Dim TableName As String
Dim ParamField As String
Dim ParamRecord As String
TableName = SearchBoxTableCmb.Text.ToString
ParamField = SearchBoxFieldCmb.Text.ToString
ParamRecord = SearchBoxTxt.Text
'you can bind to the value only
Oledb.AddParam("@Record", ParamRecord, OleDbType.VarChar) 

DataGrid.DataSource = Oledb.ExecQuery("SELECT * FROM [" & TableName & "] WHERE [" & ParamField & "] LIKE @Record")

Upvotes: 3

druidicwyrm
druidicwyrm

Reputation: 500

You will need to add the field and table values into your string as non-parameter values using string.Format() or something like that. Right now, your query output will be

SELECT * FROM 'TABLE_VALUE' WHERE 'FIELD_VALUE' LIKE 'RECORD_VALUE'

Make sure when applying those values that you are sanitizing your data.

Upvotes: 1

Related Questions