We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

redcoder
redcoder asked
on
Medium Priority
2,981 Views
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
        dbCommand.Parameters.Add(dbParam_userID)
        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
        dbCommand.Parameters.Add(dbParam_first)
        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
        dbCommand.Parameters.Add(dbParam_surname)
        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
        dbCommand.Parameters.Add(dbParam_birthdate)
        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
        dbCommand.Parameters.Add(dbParam_address)
        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
        dbCommand.Parameters.Add(dbParam_postCode)
        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
        dbCommand.Parameters.Add(dbParam_phone)
        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
        dbCommand.Parameters.Add(dbParam_email)

        Dim rowsAffected As Integer = 0
        dbConnection.Open
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close
        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 & ",")
UpdateCustomerDetail(userid,fname,sname,bdate,add,pcode,phone,email)

End Sub
Comment
Watch Question

Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
You mean following order iin function or database? But in fact, the order in function is same as order in my table .
Hi
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.

HTHT
Rana
Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.