Asked by:
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
- Edited by Paul Ishak Monday, July 16, 2018 9:38 AM Move code to block
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 typehttps://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.
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 -
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
Monday, July 16, 2018 11:05 AM -
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
Monday, July 16, 2018 11:13 AM -
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
Monday, July 16, 2018 1:58 PM