xfungalx
asked on
Data type mismatch error when updating a record in a Access Database
Hello experts
I am creating a website using VWD and an Access database. I am having a problem when attempting to update a record in my database.
The error I am getting is:
"Data type mismatch in criteria expression."
The error is thrown at this line of code:
da1.Update(ds1)
I've added some message boxes to show the current dataset value and it is updating correctly, however when it trys to update the actual database it dies.
Here is the code.
Dim ds1 As New Data.DataSet()
Dim SQL1 As String = "SELECT Week1.[1], Week1.ID FROM Week1 WHERE (((Week1.Username)=" & """" & Username & """" & "));"
Dim da1 As New Data.OleDb.OleDbDataAdapte r(SQL1, conn)
da1.Fill(ds1)
Dim cb1 As New Data.OleDb.OleDbCommandBui lder(da1)
'For debugging purposes
MsgBox("The dataset is currently: " & ds1.Tables(0).Rows(0)("1") ) <---- Shows the dataset before making the change
ds1.Tables(0).Rows(0)("1") = Game1
'For debugging purposes
MsgBox("The dataset is now: " & ds1.Tables(0).Rows(0)("1") ) <---- the dataset updates correctly
da1.Update(ds1) <---- here is where the error is thrown
Ive spent four hours trying to figure this out! Please help!
I am creating a website using VWD and an Access database. I am having a problem when attempting to update a record in my database.
The error I am getting is:
"Data type mismatch in criteria expression."
The error is thrown at this line of code:
da1.Update(ds1)
I've added some message boxes to show the current dataset value and it is updating correctly, however when it trys to update the actual database it dies.
Here is the code.
Dim ds1 As New Data.DataSet()
Dim SQL1 As String = "SELECT Week1.[1], Week1.ID FROM Week1 WHERE (((Week1.Username)=" & """" & Username & """" & "));"
Dim da1 As New Data.OleDb.OleDbDataAdapte
da1.Fill(ds1)
Dim cb1 As New Data.OleDb.OleDbCommandBui
'For debugging purposes
MsgBox("The dataset is currently: " & ds1.Tables(0).Rows(0)("1")
ds1.Tables(0).Rows(0)("1")
'For debugging purposes
MsgBox("The dataset is now: " & ds1.Tables(0).Rows(0)("1")
da1.Update(ds1) <---- here is where the error is thrown
Ive spent four hours trying to figure this out! Please help!
1) Does the data have single quotes in it?
2) What does the UpdateCommand and InsertCommand look like?
Bob
2) What does the UpdateCommand and InsertCommand look like?
Bob
Hello
try changing data type of your required fields from the one u have selected to chararray or text or string or something to the effect available in dropdown list of the field type of the access database. you probably need to change all the fields which are likely to be affected. I will appriciate if you post the access table design view as well, so that it is more clear. but i'm pretty sure changing the data type to string or something to the effect in the database will surly resolve your problem. be careful, there might be data loss when attempting to change the fields. backup your table if you have data in it.
try changing data type of your required fields from the one u have selected to chararray or text or string or something to the effect available in dropdown list of the field type of the access database. you probably need to change all the fields which are likely to be affected. I will appriciate if you post the access table design view as well, so that it is more clear. but i'm pretty sure changing the data type to string or something to the effect in the database will surly resolve your problem. be careful, there might be data loss when attempting to change the fields. backup your table if you have data in it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Graye you nailed it, works great now! I renamed the column to "FirstGame" and updated my code as required and boom it worked.
Thanks to everyone who contributed!
Thanks to everyone who contributed!
I think there is a mismatch between the tables datatype and the value you are sending
for example
empty string value in the command text (SQL) cannot be stored in MS Access' "Date/Time"
Go thro' this link
http://www.xlinesoft.com/asprunner/docs/error_data_type_mismatch_in_criteria_expression.htm
http://support.microsoft.com/default.aspx/kb/175258
Thanks,
Nanda