Data type mismatch in criteria expression

Question

sushilsth 0 Light Poster

13 Years Ago

  For i = 0 To DataGridView1.Rows.Count - 1
            '  Dim row1 As Integer = DataGridView2.Rows(i).Cells(1).Value.ToString
            Dim row1 As Integer = Nothing
            Dim row2 As String = Nothing
            Dim row3 As String = Nothing
            Dim row4 As String = Nothing
            Dim row5 As String = Nothing
            Dim row6 As String = Nothing
            Dim row7 As String = Nothing
            Dim row8 As String = Nothing
            Dim row9 As String = Nothing
            Dim row10 As String = Nothing
            Dim row12 As String = Nothing
            Dim row11 As String = Nothing
            Dim row13 As String = Nothing
            Dim row14 As String = Nothing
            Dim row15 As String = Nothing
            Dim row16 As String = Nothing
            Dim row17 As String = Nothing
            IIf(IsNothing(DataGridView2.Rows(i).Cells(1).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(1).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(2).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(2).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(3).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(3).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(4).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(4).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(5).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(5).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(6).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(6).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(7).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(7).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(8).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(8).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(9).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(9).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(10).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(10).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(11).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(11).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(12).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(12).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(13).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(13).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(14).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(14).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(15).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(15).Value.ToString)
            IIf(IsNothing(DataGridView2.Rows(i).Cells(16).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(16).Value.ToString)
            ' IIf(IsNothing(DataGridView2.Rows(i).Cells(17).Value.ToString), String.Empty, DataGridView2.Rows(i).Cells(17).Value.ToString)
            Dim sqlquery As String = "INSERT INTO one(regno,rollno,nepali,com_english_w,com_english_o,com_math,social_w,social_o,science_w,science_o,hpe_w,hpe_o,creative,health,extra_math,extra_eng_w,extra_eng_o) values('" & row1 & "','" & row2 & "','" & row3 & "','" & row4 & "','" & row5 & "','" & row6 & "','" & row7 & "','" & row8 & "','" & row9 & "','" & row10 & "','" & row11 & "','" & row12 & "','" & row13 & "','" & row14 & "','" & row15 & "','" & row16 & "','" & row17 & "')"
            Dim sqlcommand As New OleDbCommand
            With sqlcommand
                .CommandText = sqlquery
                .Connection = conn
                .ExecuteNonQuery()      'Data type mismatch in criteria expression
            End With
        Next

i got error in this line.. plz help

.net:vb.net

2 Contributors
6 Replies
484 Views
3 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by sushilsth

Dani AI

Generated 9 Months Ago

Short diagnosis — the error comes from a mix of problems, not just quoting numbers. was right that numeric fields must not be wrapped in quotes, but the code shown also has these bugs: the IIf calls are never assigned to your row variables (so those vars stay empty/0), you call ToString() inside the null check (which will blow up if the cell is Nothing), the loop uses DataGridView1.Rows.Count but reads from DataGridView2.Rows(i), and building SQL by concatenation will silently place empty strings into numeric columns (causing Access to throw "Data type mismatch"). Fix each of those and the inserts will work.

Concrete fixes to apply

Iterate the correct grid and skip the new-row: use For Each r As DataGridViewRow In DataGridView2.Rows and If r.IsNewRow Then Continue For.
Extract cell values safely and assign them to variables; use If (short-circuiting) or explicit If/Else — do not call .ToString() until you know .Value is not Nothing.
Convert numeric cells with Integer.TryParse / Double.TryParse and pass DBNull.Value when the cell is empty.
Use a parameterized OleDb command (order matters for ? parameters) instead of concatenation; that avoids quoting/type mistakes and SQL syntax problems.

Example (safe, minimal pattern)

Dim sql As String = "INSERT INTO [Scores] (RegNo, ScoreA, Note) VALUES (?, ?, ?)"
Using cmd As New OleDb.OleDbCommand(sql, conn)
    Dim reg As Integer
    If Integer.TryParse(If(row.Cells(0).Value, "").ToString(), reg) Then
        cmd.Parameters.AddWithValue("?", reg)
    Else
        cmd.Parameters.AddWithValue("?", DBNull.Value)
    End If

    Dim sc As Double
    If Double.TryParse(If(row.Cells(1).Value, "").ToString(), sc) Then
        cmd.Parameters.AddWithValue("?", sc)
    Else
        cmd.Parameters.AddWithValue("?", DBNull.Value)
    End If

    cmd.Parameters.AddWithValue("?", If(row.Cells(2).Value, "").ToString())
    cmd.ExecuteNonQuery()
End Using

Final tips: print/log the final SQL/parameters while debugging, bracket table/field names, and double-check each Access column type. Once the loop, null checks, parsing and parameter order are correct the data-type mismatch will disappear.

All 6 Replies

Ancient Dragon 5,243 Achieved Level 70

13 Years Ago

my guess is that the data types in the database table are not the same as the types in your sqlstring. Are all the column data types in the table strings?

Ancient Dragon 5,243 Achieved Level 70

13 Years Ago

If they are numbers then don't put them in quotes

values(row1 & "," & row2...)

Edited 13 Years Ago by Ancient Dragon

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sushilsth 0 Light Poster · Answer 1 · 2013-07-26T13:42:53+00:00

no.. i m using ms access and its all data type is number.. plz make it correct ..
thanx for help.

sushilsth 0 Light Poster · Answer 2 · 2013-07-26T13:57:57+00:00

sir i coreected that and its solved now i got syntax error in
Dim sqlquery As String = "INSERT one(regno,rollno,nepali,com_english_w,com_english_o,com_math,social_w,social_o,science_w,science_o,hpe_w,hpe_o,creative,health,extra_math,extra_eng_w,extra_eng_o) values(" & row1 & "," & row2 & "," & row3 & "," & row4 & "," & row5 & "," & row6 & "," & row7 & "," & row8 & "," & row9 & "," & row10 & "," & row11 & "," & row12 & "," & row13 & "," & row14 & "," & row15 & "," & row16 & "," & row17 & ")"

       is there any syntax error. i cannot find any.. thanx for ur response.

Ancient Dragon 5,243 Achieved Level 70 Team Colleague Featured Poster · Answer 3 · 2013-07-26T14:02:31+00:00

        Dim sqlquery As String = "INSERT INTO one(regno,rollno,nepali,com_english_w,com_english_o,com_math,social_w,social_o," & _
           "science_w,science_o,hpe_w,hpe_o,creative,health,extra_math,extra_eng_w,extra_eng_o) values('" & row1 & "','" & _
            row2 & "','" & row3 & "','" & row4 & "','" & row5 & "','" & row6 & "','" & row7 & "','" & row8 & "','" _
             & row9 & "','" & row10 & "','" & row11 & "','" & row12 & "','" & row13 & "','" & row14 & "','" & row15 & "','" & row16 & "','" & row17 & "')"

I split the line up so that I could see all of it at the same time. This compiles ok with VB.NET 2012

sushilsth 0 Light Poster · Answer 4 · 2013-07-26T16:47:27+00:00

it gave me datatype mismatch error again. i wrote " & row1 & " instead of'" & row1 & "' but i didnt work..
n

Data type mismatch in criteria expression

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers