Honesty Rocks! truth rules.

Java And Sql: Data Mismatch

HOME      >>       Programming


Alright, I'm having some really funky issue with this. I know it's a mismatch (obviously) and I want to know if the Xisto community members can help me solve this issue. It's been annoying me a lot of late.


try{ libSQL myAddNewData = new libSQL(); String strRownum =(String.valueOf(jComboBoxLName.getSelectedItem())); if (intChoice == 1){ myAddNewData.AddNewData("INSERT INTO CUSDATA (FIRSTNAME, LASTNAME, PHONE, SIN) VALUES ('"+ strUserData[intCount][0] +"','" + strUserData[intCount][1] + "','" + strUserData[intCount][5] + "','" + strUserData[intCount][8] + "')"); myAddNewData.AddNewData("INSERT INTO CUSLOCATION (ADDRESS, CITY, [POSTAL CODE], COUNTRY, PROVINCE) VALUES ('" + strUserData[intCount][2] + "','" + strUserData[intCount][3] + "','" + strUserData[intCount][4] + "','" +strUserData[intCount][6] + "','" + strUserData[intCount][7] + "')"); } if (intChoice == 2){ myAddNewData.AddNewData("UPDATE CUSDATA SET FIRSTNAME = '" + jTextFieldFName.getText() + "', PHONE = '" + jTextFieldPh.getText() + "', SIN = '" + jTextFieldSIN.getText() + "' WHERE ID = '" + strRownum + "'"); myAddNewData.AddNewData("UPDATE CUSLOCATION SET ADDRESS='" + jTextFieldAddr.getText() + "', CITY = '" + jTextFieldCity.getText() +"', [POSTAL CODE] = '" + jTextFieldPC.getText() + "' WHERE ID = '" + strRownum + "'"); } if (intChoice == 3){ myAddNewData.AddNewData("DELETE FROM CUSDATA WHERE ID = '" + strRownum + "'"); myAddNewData.AddNewData("DELETE CUSLOCATION WHERE ID = '" + strRownum + "'"); } }catch(Exception e){ jTextArea1.append("SQLException: " + e.getMessage()); }
The error I get when I initiate the UPDATE command:



Compiling 1 source file to D:\devel\java\netbeans\Grade 12\SIN\build\classes



Error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

BUILD SUCCESSFUL (total time: 36 seconds)

Here's a screenshot of my databases:

Posted Image

Posted Image





I don't know how access deal with it, but if a value is a number then maybe it shouldn't be surrounded by accents ???


What do you mean by accents?xboxrulz


I have not looked at this deeply just yet nor am I a Java expert. That said it looks as if the program is trying to query the DB using the ID as the criteria. The problem seems to be that the program is passing the ID paramter as a string when the ID field in the DB is not a string. Thus strRownum needs to be an integer. Hope that helps.


I tried getting it as an integer before. Failed doing that, I was looking at my classmates' notes, he got it in String, so I guess that's not the true issue.xboxrulz


What do you mean by accents?


the ID is between ' ' , that's what I mean with accents, probably not the correct english word ?

ps. maybe this is the answer ?

5. Question:I receive the following error message when trying to save changes I made to database:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/tablename_edit.asp, line xxx


The SQL error 80040E07 occurs when attempting to use the "Insert Record" or the "Update Record" server behavior to set the value of a column in Microsoft Access with an empty string ("").

MS Access is characterized by strong data typing; it imposes a rigorous set of rules on given column values. The empty string value in the command text (SQL) cannot be stored in MS Access' "Date/Time" data type specified by the associated column.

Try to avoid inserting and/or updating columns of "Date/Time" data types in Access with empty strings, ("") or with any other value that does not correspond to the range of values specified for the data type.


Alright, I'll try that and see. Thanks for the info.As for accents, I have a US English keyboard so accents are out of the question. That's just my variable name and wasn't supposed to be a dictionary word.xboxrulz