Data type mismatch in criteria expression.

Question

Hi friends,

I am trying to add numerical value through parameter in access table, I receive aforesaid  message. what will be the solution, I have tried as follows:

  

Sub DataTrans()
        Dbconnection.Open()

        MasterReader = MasterCmd.ExecuteReader()

        Try

            While MasterReader.Read()

                IntArr(0) = (MasterReader("Sap_No"))
                IntArr(1) = (MasterReader("DesigIndex"))
                IntArr(2) = (MasterReader("CurrYear"))

                StrArr(0) = (MasterReader("CPF_No".ToString))
                StrArr(1) = (MasterReader("Name".ToString))
                StrArr(2) = (MasterReader("Designation".ToString))


                LvMasterCmd.CommandText = "INSERT INTO LeaveMaster  (CPF_No, Name, Designation,  MNT, YR, LeaveStatus,CurrYear,Sap_No,DesigIndex)" &
           "VALUES (@CPF_No, @Name, @Designation,  @MNT, @YR, @LeaveStatus, CurrYear, @Sap_No, @DesigIndex) ;  "


                Try

                     LvMasterCmd.Parameters.AddWithValue("@Sap_No", IntArr(0))
                    LvMasterCmd.Parameters.AddWithValue("@DesigIndex", IntArr(1))
                    LvMasterCmd.Parameters.AddWithValue("@CurrYear", IntArr(2))

                    LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0).ToString)
                    LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1).ToString)
                    LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2).ToString)

                    LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
                    LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
                    LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")


                    LvMasterCmd.ExecuteNonQuery()
                    LvMasterCmd.Parameters.Clear()
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try

            End While

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

        Dbconnection.Close()
    End Sub

Answer 1

Hi J_Mohan

Your code seems to be missing an @

Best Regards,

Alex

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Thanks Alex,

I have made required correction but still it is displaying similar error..... Please help

Answer 3

It shows error for :

                    LvMasterCmd.Parameters.AddWithValue("@Sap_No", IntArr(0))
                   

                      LvMasterCmd.Parameters.AddWithValue("@DesigIndex", IntArr(1))
                   

                         LvMasterCmd.Parameters.AddWithValue("@CurrYear", IntArr(2))

Please Help

Answer 4

Hi

This should be the value inserted in your SQL statement does not match the contents of the database.

Using AddWithValue means that the data type of the parameter value is determined by the value itself.
You need to check, if every parameter goes to update a compatible field type

https://stackoverflow.com/questions/23937942/data-type-mismatch-in-criteria-expression-error

Best Regards,

Alex

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi friends,

I am trying to add numerical value through parameter in access table, I receive aforesaid  message. what will be the solution, I have tried as follows:

   

If you are getting an error stating that you are having a data type mismatch, then perhaps you need to ensure that you are inserting objects into each column that have the correct datatype.

Since you haven't provided the schema for your "LeaveMaster" table, there isn't really enough information here to answer your question correctly.

Make sure that the datatype of the values you are adding to each column match the datatype of each column you are adding values to, and your error will go away.

---

Don't forget to vote for Helpful Posts and Mark Answers!
*This post does not reflect the opinion of Microsoft, or its employees.

Answer 6

Please finish one question before starting a new question. 

You have not finished up on the following question, the one you double posted on.

---

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

Answer 7

In regards to the issue here, set a break-point, when you hit the break-point, step thru the code, examine values, make sure they are the proper type and if they don't allow null for a value, make sure you are not passing null.

Another method, not a quick solution is to remove all parameters expect one, run the code, if it works add another, repeat until the problem value is found then fix it.

On a side note, you should not be clearing the parameter collection but instead create parameters beforehand then set their values as needed. Couple this with a transaction so if one record insert fails say out of five you can rollback the inserts.

---

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

Answer 8

 Thanks for reply sir,

Moved codes to block but same error occur, what to do though both tables have identical field in type and length also..... Please help

Answer 9

Thanks for reply sir,

Both tables are identical in type, length and name of fields also, even though "Data type mismatch" error occur.... Please help

Answer 10

Thanks for reply Karen Payne,

I scrupulously followed your all instructions i.e set break point and while step through values are examined and found all they are in proper types and not NULL.

Secondly, I tried to remove parameters one by one and run code, it is found that the fields and parameter values of type TEXT  executed correctly and records added, but -

when I enter parameter for NUMERIC field, compiler produces "Data type mismatch" error though the data reads from one table fields has correct type, length and Naming also.... Please guide. 

Answer 11

The following table in ms-access has a primary key and five numeric fields, each field name begins with the numeric type. Note the code represents how to add a single row, this can be expanded to be in a for/each or for/next where the parameters are created once prior to the loop. The idea is to show a bug in the code at the end of this reply.

First there is an exception class which the data class (second code block) inherits.

Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class BaseExceptionsHandler
    Protected mHasException As Boolean
    Public ReadOnly Property HasException() As Boolean
        Get
            Return mHasException
        End Get
    End Property
    Public ReadOnly Property HasSqlException() As Boolean
        Get
            Return mLastException Is GetType(SqlException)
        End Get
    End Property
    Public ReadOnly Property HasAccessException() As Boolean
        Get
            Return mLastException Is GetType(OleDbException)
        End Get
    End Property
    Protected mLastException As Exception
    Public ReadOnly Property LastException() As Exception
        Get
            Return mLastException
        End Get
    End Property
    Public ReadOnly Property LastExceptionMessage As String
        Get
            Return mLastException.Message
        End Get
    End Property

    Public ReadOnly Property IsSuccessFul As Boolean
        Get
            Return Not mHasException
        End Get
    End Property
End Class

In this data class there is one method "AddNewRow" where each parameter is strongly typed.

Imports System.Data.OleDb
Imports System.IO

Public Class AccessOperations
    Inherits BaseExceptionsHandler

    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0"
    }

    Public Sub New()
        Builder.DataSource = Path.Combine(
            AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
    End Sub
    Public Function AddNewRow(
        pInt As Integer,
        pLong As Long,
        pSingle As Single,
        pDouble As Double,
        pDecimal As Decimal,
        ByRef pIdentfier As Integer) As Boolean

        Try
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL> 
                            INSERT INTO VariousNumericFields  
                                ( 
                                    IntField, 
                                    LongField, 
                                    SingleField, 
                                    DoubleField, 
                                    DecinalField
                                )  
                            Values 
                                ( 
                                    @IntField, 
                                    @LongField, 
                                    @SingleField, 
                                    @DoubleField, 
                                    @DecinalField
                                ) 
                        </SQL>.Value

                    cmd.Parameters.AddWithValue("@IntField", pInt)
                    cmd.Parameters.AddWithValue("@LongField", pLong)
                    cmd.Parameters.AddWithValue("@SingleField", pSingle)
                    cmd.Parameters.AddWithValue("@DoubleField", pDouble)
                    cmd.Parameters.AddWithValue("@DecinalField", pDecimal)

                    cn.Open()

                    cmd.ExecuteNonQuery()

                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)

                End Using
            End Using

        Catch ex As Exception
            mHasException = True
            mLastException = ex
        End Try

        Return IsSuccessFul

    End Function
End Class

Implementing in a form.

Imports BaseClasses

Public Class Form3
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops As New AccessOperations
        Dim id As Integer = 0
        If ops.AddNewRow(1, 1, 1, 1.3D, 34.4D, id) Then
            MessageBox.Show($"id {id}")
        Else
            MessageBox.Show(ops.LastExceptionMessage)
        End If
    End Sub
End Class

Pressing the button a new row is inserted, the new primary key is returned.

Now if we introduce a bug into the AddNewRow. Now imagine the literal string being a variable, we would need to set a break point and debug as indicated before.

The result is, you guessed it.

What else can cause this? an old database that has used 16bit rather than 32bit, have seen this happen many years ago.

---

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