• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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"
 
0
chokka
Asked:
chokka
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Probably a good idea to post the SQL for the insert query :-)

mx
0
 
chokkaAuthor 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 MVP, Access and Data Platform)Commented:
I don't see a WHERE clause for criteria - per the error.

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
chokkaAuthor Commented:
Its just normal INTO Statement
0
 
Jini Jose.Net Team LeadCommented:
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 MVP, Access and Data Platform)Commented:
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
 
chokkaAuthor Commented:
i have kept all DateTime field as TEXT and passing as String
0
 
vbighamCommented:
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now