SQL Data type mismatch in criteria expression

Question

When running the expression below I get an error saying:

 Data type mismatch in criteria expression

The message boxes are used to identify where error happened, it gets to checkpoint 1 then it stops!

                    Dim table2 As New DataTable
                    Dim recordcount2 As Integer
                    Dim command2 As String = "SELECT * FROM [Results] where " & "[TestID] = " & " '" & CInt(LocationID) & "'"
                    Dim adapter2 As New OleDb.OleDbDataAdapter(command2, conn)
                    table2.Clear()
                    MsgBox("Checkpoint 1")
                    recordcount2 = adapter2.Fill(table2)
                    MsgBox("Checkpoint 2")

The code is in this section of my program:

    Try
        'Defining variables
        Dim table As New DataTable
        Dim command As String
        Dim recordCount As Integer
        Dim LocationID As Integer
        command = "SELECT * FROM [Test] where " & "[MachineID] = " & " '" & machineID & "'" 'SQL command to find if there is a usename stored with that is in username text box

        Dim adapter As New OleDb.OleDbDataAdapter(command, conn) 'adapter
        table.Clear() 'adding data to a table.
        recordCount = adapter.Fill(table)
        If recordCount <> 0 Then
            For i = 0 To recordCount
                Try
                    LocationID = CInt(table.Rows(i)(0))
                    Dim table2 As New DataTable
                    Dim recordcount2 As Integer
                    Dim command2 As String = "SELECT * FROM [Results] where " & "[TestID] = " & " '" & CInt(LocationID) & "'"
                    Dim adapter2 As New OleDb.OleDbDataAdapter(command2, conn)
                    table2.Clear()
                    MsgBox("Checkpoint 1")
                    recordcount2 = adapter2.Fill(table2)
                    MsgBox("Checkpoint 2")
                    If recordcount2 <> 0 Then
                        For x = 0 To recordcount2
                            MsgBox("yay1")
                            Dim TestID As String = table2.Rows(x)(1)
                            Dim Thickness As String = table2.Rows(x)(2)
                            Dim TargetFilter As String = table2.Rows(x)(9)
                            Dim SNR As String = table2.Rows(x)(3)
                            Dim STD As String = table2.Rows(x)(4)
                            MsgBox("yay2")
                            Dim M1 As String = table2.Rows(x)(5)
                            Dim M2 As String = table2.Rows(x)(6)
                            Dim kVp As String = table2.Rows(x)(7)
                            Dim mAs As String = table2.Rows(x)(8)
                            MsgBox("yay3")
                            Dim CNR As Short = (CLng(M1) - CLng(M2)) / 2
                            MsgBox("Further")
                            dgvViewData.Rows.Add(TestID, Thickness, CStr(SNR), CStr(STD), CStr(M1), CStr(M2), kVp, mAs, CStr(CNR))
                        Next
                    Else
                        MsgBox("RIP")
                    End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            Next
        Else
            MsgBox("There data for this machine.")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

Code on button

    Try
        Dim table As New DataTable
        Dim command As String
        Dim recordCount As Integer
        Dim TestNum As String = "1"
        command = "SELECT * FROM [Results] where " & "[TestID] = " & " '" & CStr(TestNum) & "'"
        Dim adapter As New OleDb.OleDbDataAdapter(command, conn)
        table.Clear()
        recordCount = adapter.Fill(table)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

Unknown Unknown · Accepted Answer · 2016-04-15T01:52:39

Your locationID parameter has to be a string to be concatenated into your select statement. I'm assuming its an integer which is causing your datatype mismatch.

SQL Data type mismatch in criteria expression

2 Answers