System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Posted on 2006-04-02
Medium Priority
Last Modified: 2008-01-09
Hwy this code just don't work : I keep getting error of ( System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.):

    Function UpdateCustomerDetail(ByVal userID As String, ByVal first As String, ByVal surname As String, ByVal birthdate As Date, ByVal address As String, ByVal postCode As String, ByVal phone As String, ByVal email As String) As Integer
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Documents an"& _
"d Settings\Administrator\My Documents\ECommerceAssignment\ASPCode\JEANS.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "UPDATE [customer] SET [First]=@First, [Surname]=@Surname, [Birthdate]=@Birthdate,"& _
" [Address]=@Address, [PostCode]=@PostCode, [Phone]=@Phone, [Email]=@Email WHERE "& _
"([customer].[userID] = @userID)"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_userID As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_userID.ParameterName = "@userID"
        dbParam_userID.Value = userID
        dbParam_userID.DbType = System.Data.DbType.String
        Dim dbParam_first As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_first.ParameterName = "@First"
        dbParam_first.Value = first
        dbParam_first.DbType = System.Data.DbType.String
        Dim dbParam_surname As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_surname.ParameterName = "@Surname"
        dbParam_surname.Value = surname
        dbParam_surname.DbType = System.Data.DbType.String
        Dim dbParam_birthdate As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_birthdate.ParameterName = "@Birthdate"
        dbParam_birthdate.Value = birthdate
        dbParam_birthdate.DbType = System.Data.DbType.DateTime
        Dim dbParam_address As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_address.ParameterName = "@Address"
        dbParam_address.Value = address
        dbParam_address.DbType = System.Data.DbType.String
        Dim dbParam_postCode As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_postCode.ParameterName = "@PostCode"
        dbParam_postCode.Value = postCode
        dbParam_postCode.DbType = System.Data.DbType.String
        Dim dbParam_phone As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_phone.ParameterName = "@Phone"
        dbParam_phone.Value = phone
        dbParam_phone.DbType = System.Data.DbType.String
        Dim dbParam_email As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_email.ParameterName = "@Email"
        dbParam_email.Value = email
        dbParam_email.DbType = System.Data.DbType.String

        Dim rowsAffected As Integer = 0
            rowsAffected = dbCommand.ExecuteNonQuery
        End Try

        Return rowsAffected
    End Function

Sub Button2_Click(sender As Object, e As EventArgs)
Dim fname as String,sname as String,add as String,pcode as String,phone as String,email as String
Dim bdate as Date
Dim userid as String

userid = Label2.text

fname = TextBox1.text
sname = TextBox2.text
bdate = TextBox3.text
add = TextBox4.text
pcode = TextBox5.text
phone = TextBox6.text
email = TextBox7.text
'response.write(userid & "," & fname & ",")

End Sub
Question by:redcoder
  • 2

Accepted Solution

sforcier earned 1050 total points
ID: 16357642
Hmm, I thought that OleDb was dumb with parameters (or perhaps just Access) and it just puts them in the query in the order they are added. That is, it doesn't bother matching the first parameter object, @userID, to the parameter in the WHERE clause, it uses it in the first SET assignment.

Try adding the parameters in the same order they appear in the query and see if that helps.

Author Comment

ID: 16357671
You mean following order iin function or database? But in fact, the order in function is same as order in my table .

Assisted Solution

irps20001 earned 450 total points
ID: 16358544
I guess the datatype is mismatching with Birthdate field which u r passing directly from Textbox.
Can u check by converting the TextBox3.text into DateTime. At the same time, pls verify the database columns datatype with the type of data ( parameters ) you are passing.
And also debug and find the type of data it is being passed and cross check with the table columns data type.


Assisted Solution

sforcier earned 1050 total points
ID: 16362718
My point is that if you have:

 "SELECT * FROM tblFoo WHERE a = @a AND b = @b"

And do (psuedo-code):

cmd.parameters.add(new parameter("@b", "bValue");
cmd.parameters.add(new parameter("@a", "aValue");

it may do the opposite of what you want. Since @a appears first in the query, but you add "bValue" first to the cmd.parameters collection.

It is entirely possible that I'm remembering this wrong, but it certainly won't hurt to add the parameters to the cmd.parameters collection in the order that they appear in the query just to see.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question