Honesty Rocks! truth rules.

Java And Sql: Data Mismatch

HOME      >>       Programming

xboxrulz1405241485

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:

init:

deps-jar:

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

compile:

run:

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

 

Thanks,

deltatux


wutske

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 ???


xboxrulz1405241485

What do you mean by accents?xboxrulz


darasen

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.


xboxrulz1405241485

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


wutske

What do you mean by accents?
xboxrulz


DELETE FROM CUSDATA WHERE ID = 'strRownum'

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


ps. maybe this is the answer ?
http://forums.xisto.com/no_longer_exists/

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


Answer:

Issue
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 ("").

Reason
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.

Solution
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.



xboxrulz1405241485

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