Solved

Data type mismatch error when updating a record in a Access Database

Posted on 2006-07-23
5
309 Views
Last Modified: 2012-06-27
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.OleDbDataAdapter(SQL1, conn)
        da1.Fill(ds1)
        Dim cb1 As New Data.OleDb.OleDbCommandBuilder(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!
0
Comment
Question by:xfungalx
5 Comments
 
LVL 6

Expert Comment

by:Nandakumar Sakthivel
ID: 17165904

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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17166797
1) Does the data have single quotes in it?

2) What does the UpdateCommand and InsertCommand look like?

Bob
0
 
LVL 1

Expert Comment

by:awabid
ID: 17167540
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.
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 17167851
Hummm, We could try this....

Since you've got a strange column name (you rarely see a number as a column name) I'd bet the UpdateCommand and InsertCommands are throughly confused.  So I'd suggest that you set the QuotePrefix and QuoteSuffix properties like this:

cb1.QuotePrefix = "["
cb1.QuoteSuffix = "]"

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder_members.aspx

BTW:  Renaming the column would be my first choice
0
 

Author Comment

by:xfungalx
ID: 17168206
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!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Duplicate a row 2 46
ASP.NET Web Form Variables being reset 2 26
ISS migration from IIS 6 to IIS 8.5 rendering problems 4 50
Asp.net mvc foreach 3 35
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

23 Experts available now in Live!

Get 1:1 Help Now