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

Posted on 2006-04-02
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
    LVL 6

    Accepted Solution

    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

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

    Assisted Solution

    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.

    LVL 6

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now