DataType Mismatch Criteria - VB.Net to MS Access

VS 2005/ VB.Net

I am inserting to a Table.

All DataType and Query is perfectly correct, still i am getting these errors

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217913
  Message="Data type mismatch in criteria expression."
  Source="Microsoft JET Database Engine"
 
chokkaStudentAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Probably a good idea to post the SQL for the insert query :-)

mx
0
 
chokkaStudentAuthor Commented:


I am loading MS Access Database from VB.Net
Insert into tmpReport ([FirstName], [MiddleName],[LastName],[SSN],[Address],[City],[State],[Zip],[DOB],[IsMale],[HomePhone],[CellPhone],[WorkPhone],[ShippingAddress],[IsEmailShipment],[EmailAddress],[DCity],[DState],[DZip],[BestTimeToCall],[IsLeaveMsg],[IsPolicyHolder],[TypeofInsurance],[PolicyHolderName],[RelationshiptoPolicyHolder],[PolicyDOB],[PolicySSN],[Physician1],[Physician1PhnNumber],[Physician1FaxNumber],[Physician2],[Physician2PhnNumber],[Physician2FaxNumber],[Physician3],[Physician3PhnNumber],[Physician3FaxNumber],[CaseManager],[Organization],[CMPhoneNumber],[ReferredBy],[Relationship],[RelationPhoneNumber]) values ('" & txtFName.Text.Trim() & "','" & txtMIName.Text.Trim() & "','" & txtLName.Text.Trim() & "'," & iSSN & ",'" & strAddress & "','" & txtCity.Text.Trim() & "','" & strState & "'," & iZip & ",'" & txtDOB.Text.Trim() & "'," & isMale & "," & iHPn & "," & iCellPhone & "," & iWPhone & ",'" & strShipping & "'," & isEmailShipInfo & ",'" & txtEmail.Text.Trim() & "','" & txtDCity.Text.Trim() & "','" & strDState & "'," & txtDZipCode.Value.Trim() & ",'" & txtBestTimeToCall.Text.Trim() & "'," & isLeaveMsg & "," & isPolicyHolder & "," & iVariable & ",'" & txtPolicyHolderName.Text.Trim() & "','" & txtRelationshipPolicyHolder.Text.Trim() & "','" & txtPolicyHolderDOB.Text.Trim() & "'," & txtPolicySSN.Value.Trim() & ",'" & strPhy1 & "','" & txtP1Phn.Text.Trim() & "','" & txtP1Fax.Text.Trim() & "','" & strPhy2 & "','" & txtP2Phn.Text.Trim() & "','" & txtP2Fax.Text.Trim() & "','" & strPhy3 & "','" & txtP3Phn.Text.Trim() & "','" & txtP3Fax.Text.Trim() & "','" & strCM & "','" & txtOrganization.Text.Trim() & "'," & txtCPhoneNumber.Text & ",'" & txtReferredBy.Text.Trim() & "','" & txtRefRelationship.Text.Trim() & "','" & txtRefRelationshipPhoneNumber.Value.Trim() & "')

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I don't see a WHERE clause for criteria - per the error.

mx
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
chokkaStudentAuthor Commented:
Its just normal INTO Statement
0
 
Jini JoseSenior .Net DeveloperCommented:
debug the code, and get the output of the insert query and then try to execute the query in access database directly. then you will get the exact error.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok.  That error implies that you are tying to put ... for example ... a Text value in a Date/Time data type, Text value in a Numeric data type ... something to that effect.  Basically the wrong data type into a field with a different data type.

mx

0
 
chokkaStudentAuthor Commented:
i have kept all DateTime field as TEXT and passing as String
0
 
vbighamConnect With a Mentor Commented:
Where you are passing Values like iSSN, iZip, isMale, iHPn, iCellPhone, iWPhone, isEmailShipInfo, isLeaveMsg, isPolicyHolder, iVariable

I am going to assume that variables starting with i are integers, and is are booleans.

You will want to make sure that your access database has the respective types (ex. iSSN will want SSN field to be integer, boolean for isMale etc.)  If you want to store only strings, you will want iSSN.ToString(), isMale.ToString()

Otherwise, it seems like you have the fields/values in the right order (couldn't hurt to double check).

Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.