CODEWITHSUNDEEP
.netdatabasevb.nettypesmismatch
Harshal Pathak
Harshal Pathak

Reputation: 787

Data Type Mismatch in criteria expression in vb.net with access 2010

 Dim QRY As String = "SELECT t1.Observer_Name,t1.Company, t2.Safety, t2.T_Name, t2.Trainer_Name," _
                               + "t2.T_Location, t2.T_Start, t2.T_End,(DateDiff(""d"",T_Start, T_End)+1)*8 as [Man_Hours], t2.Remarks" _
                               + " FROM ObserverDetails t1,Training t2,TrainningAttendee t3" _
                               + " WHERE t2.T_ID=t3.T_ID AND t3.Ob_ID=t1.Observer_ID And  t2.T_Start >= @dt1 AND t2.T_Start <= @dt2 "
        cmdSelect.CommandText = QRY
        cmdSelect.Parameters.Add("@dt1", OleDb.OleDbType.Char).Value = DateTimePicker1.Text.Trim
        cmdSelect.Parameters.Add("@dt2", OleDb.OleDbType.Char).Value = DateTimePicker2.Text.Trim
        cmdSelect.CommandType = CommandType.Text
        cmdSelect.Connection = reportConnection4

        reader1 = cmdSelect.ExecuteReader
        If reader1.Read Then
            While reader1.Read
                Dim row As String() = New String() {" " & reader1.Item("Observer_Name") & " ", "" & reader1.Item("Company") & "", " " & reader1.Item("Safety") & " ", " " & reader1.Item("T_Name") & "", _
                                                   "" & reader1.Item("Trainer_Name") & " ", " " & reader1.Item("T_Location") & "", "" & reader1.Item("T_Start") & "", _
                                                   "" & reader1.Item("T_End") & " ", "" & reader1.Item("Man_Hours") & " ", "" & reader1.Item("Remarks") & " "}
                DataGridView1.Rows.Add(row)
            End While
        End If

giving me error at reader = cmdSelect.ExecuteReader error is Data type mismatch in criteria expression.

Upvotes: 0

Views: 3207

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112782

If your command is a System.Data.OleDb.OleDbCommand then your reader must be a System.Data.OleDb.OleDbDataReader or a base type of it (DbDataReader or IDataReader).

Dim reader1 As OleDbDataReader

Or implicitly

Dim reader1 = cmdSelect.ExecuteReader()

You can use AddWithValue in order to a a parameter with the right type auotmatically. If your database type is Date/Time then pass the date as Date not as string (as others have said in comments).

cmdSelect.Parameters.AddWithValue("@dt1", DateTimePicker1.Value)

The problem with strings is (in addition to the data type mismatch) that a textual representation of a date is always dependent on the culture used for formatting. If the DB assumes another culture, then things might go wrong US 5/7/2014 (m/d/yyyy) is not the same as British 5/7/2014 (d/m/yyyy)!

Upvotes: 1

Related Questions