 Java And Sql: Data Mismatch |
|
|
|
|
  |
 Jun 7 2008, 09:36 PM
Post
#1
|
|
|
Colonel Panic  Group: [MODERATOR] Posts: 2,789 Joined: 25-March 05 From: Toronto, Ontario, Canada Member No.: 3,233  |
Alright, I'm having some really funky issue with this. I know it's a mismatch (obviously) and I want to know if the Astahost community members can help me solve this issue. It's been annoying me a lot of late.
CODE 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: QUOTE 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:   Thanks, deltatux |
 |
 
|
|
Jun 8 2008, 09:26 AM
Post
#2
|
|
|
Way Out Of Control - You need a life :)  Group: [HOSTED] Posts: 1,048 Joined: 2-August 05 From: Kapellen (Antwerp, Belgium) Member No.: 7,585 |
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 ???
|
|
|
|
|
Jun 8 2008, 06:29 PM
Post
#3
|
|
|
Colonel Panic Group: [MODERATOR] Posts: 2,789 Joined: 25-March 05 From: Toronto, Ontario, Canada Member No.: 3,233 |
What do you mean by accents?
xboxrulz |
|
|
|
|
Jun 8 2008, 08:24 PM
Post
#4
|
|
|
Advanced Member Group: [HOSTED] Posts: 153 Joined: 3-April 08 From: Milling about Member No.: 29,596 |
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. |
|
|
|
|
Jun 9 2008, 03:10 AM
Post
#5
|
|
|
Colonel Panic Group: [MODERATOR] Posts: 2,789 Joined: 25-March 05 From: Toronto, Ontario, Canada Member No.: 3,233 |
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 |
|
|
|
|
Jun 9 2008, 07:39 AM
Post
#6
|
|
|
Way Out Of Control - You need a life :) Group: [HOSTED] Posts: 1,048 Joined: 2-August 05 From: Kapellen (Antwerp, Belgium) Member No.: 7,585 |
QUOTE(xboxrulz @ Jun 8 2008, 08:29 PM)  What do you mean by accents? xboxrulz CODE 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://www.xlinesoft.com/asprunnerpro/faq....atatypemismatch QUOTE 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. |
|
|
|
|
Jun 9 2008, 05:35 PM
Post
#7
|
|
|
Colonel Panic Group: [MODERATOR] Posts: 2,789 Joined: 25-March 05 From: Toronto, Ontario, Canada Member No.: 3,233 |
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 |
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 11th October 2008 - 06:10 PM |