none
Problem with conditional query RRS feed

  • Question

  • hi

    I got this error at the query

    data type mismatch in criteria expression

    quary

     Dim indx As Integer = DataGrid.SelectedRows(0).Index
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = '" & DataGrid(1, indx).Value.ToString() & "'", con)

    I want to create a query with the value of the AutoNumber field in the specified row

    But when you place the query in this way it works well

     Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = 1", con)


    Sunday, December 2, 2018 1:05 AM

Answers

  • hi

    I got this error at the query

    data type mismatch in criteria expression

    quary

     Dim indx As Integer = DataGrid.SelectedRows(0).Index
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = '" & DataGrid(1, indx).Value.ToString() & "'", con)

    I want to create a query with the value of the AutoNumber field in the specified row

    But when you place the query in this way it works well

     Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = 1", con)


    Hi , 

    If your problem in the first query you have to make it like this

    Dim indx As Integer = DataGrid.SelectedRows(0).Index
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = " & DataGrid(1, indx).Value.ToString() & " ", con)

    Remove single quote or use Karen's snippet

    hope it helps 


    Regards From Amr_Aly

    • Marked as answer by ahmeddc Sunday, December 2, 2018 3:42 PM
    Sunday, December 2, 2018 1:09 PM
  • Hello,

    First off string concatenation is one way to cause issues like this, instead use code such as presented below. I've never written a SQL statement with string concatenation other than to wrap to another line.

    Caveat (which doesn't matter here) is parameters are ordinal which is why each parameter in this syntax uses a question mark while SQL-Server we have named parameters which can be in any order.

    Dim dt As New DataTable
    Dim indx As Integer = DataGrid.SelectedRows(0).Index
    Dim selectStatement = "SELECT * FROM TB WHERE column_ID = ?"
    Using cn As New OleDbConnection(ConnectionString)
        Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = selectStatement}
            cmd.Parameters.AddWithValue("?", indx)
            cn.Open()
            dt.Load(cmd.ExecuteReader())
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Sunday, December 2, 2018 3:42 PM
    Sunday, December 2, 2018 10:52 AM
    Moderator

All replies

  • Hello,

    First off string concatenation is one way to cause issues like this, instead use code such as presented below. I've never written a SQL statement with string concatenation other than to wrap to another line.

    Caveat (which doesn't matter here) is parameters are ordinal which is why each parameter in this syntax uses a question mark while SQL-Server we have named parameters which can be in any order.

    Dim dt As New DataTable
    Dim indx As Integer = DataGrid.SelectedRows(0).Index
    Dim selectStatement = "SELECT * FROM TB WHERE column_ID = ?"
    Using cn As New OleDbConnection(ConnectionString)
        Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = selectStatement}
            cmd.Parameters.AddWithValue("?", indx)
            cn.Open()
            dt.Load(cmd.ExecuteReader())
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Sunday, December 2, 2018 3:42 PM
    Sunday, December 2, 2018 10:52 AM
    Moderator
  • hi

    I got this error at the query

    data type mismatch in criteria expression

    quary

     Dim indx As Integer = DataGrid.SelectedRows(0).Index
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = '" & DataGrid(1, indx).Value.ToString() & "'", con)

    I want to create a query with the value of the AutoNumber field in the specified row

    But when you place the query in this way it works well

     Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = 1", con)


    Hi , 

    If your problem in the first query you have to make it like this

    Dim indx As Integer = DataGrid.SelectedRows(0).Index
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TB WHERE column_ID = " & DataGrid(1, indx).Value.ToString() & " ", con)

    Remove single quote or use Karen's snippet

    hope it helps 


    Regards From Amr_Aly

    • Marked as answer by ahmeddc Sunday, December 2, 2018 3:42 PM
    Sunday, December 2, 2018 1:09 PM