none
Data type mismatch in criteria expression. RRS feed

  • 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
    


    Monday, July 16, 2018 5:45 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Monday, July 16, 2018 6:47 AM
  • Thanks Alex,

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

    Monday, July 16, 2018 7:53 AM
  • 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

    Monday, July 16, 2018 8:22 AM
  • 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 MSDNFSF@microsoft.com.

    Monday, July 16, 2018 8:42 AM
  • 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.


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

    Monday, July 16, 2018 9:49 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, July 16, 2018 11:05 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, July 16, 2018 11:13 AM
    Moderator
  •  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

    Monday, July 16, 2018 12:48 PM
  • 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

    Monday, July 16, 2018 12:52 PM
  • 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. 

    Monday, July 16, 2018 1:11 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, July 16, 2018 1:58 PM
    Moderator