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

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
redcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sforcierCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redcoderAuthor Commented:
You mean following order iin function or database? But in fact, the order in function is same as order in my table .
0
irps20001Commented:
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
0
sforcierCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

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.