INSERT INTO problem

ceaton · Nov 16, 1999

I have an INSERT INTO query with several fields, one of which is a name field. The problem I'm having is if the person has a name like "O'brien", the query is then rejected due to the single apostrophe. Here is the bit of my query that I'm using. 
 
String query = "INSERT INTO caseInfo(caseName, evalDate, docketNum)" 
+"VALUES("+getAsSQLString(labelcaseName) 
+","+getAsSQLString(labelevalDate) 
+","+getAsSQLString(labelfileNum) 
+&quot

"; 
 
This the getAsSQLString, this checks my labels for null values. 
 
public String getAsSQLString(Label tf) { 
String s; 
 
s=tf.getText(); 
if ((s==null) ¦¦ (s.length()==0)) { s="null"; } 
else { s=" ' "+s+" ' "; } 
return s; 
} 
 
 
I tried changing the else stmt to else { s="\" "+s+"\" "; } 
and else { s="\' "+s+"\' "; } none of these seem to work. Here is the error I'm getting when I try to INSERT. 
 
SQLException: [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression ' 'O'brien'. 
 
As you can see by the error I'm using Access97 as my dB. 
Any help would be greatly appreciated. 
 
Thanks, Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

calahans · Nov 17, 1999

Courtney, 
 
Did you try and change your application to put "" around the fieldname, such as: 
 
String query = "INSERT INTO caseInfo(caseName, evalDate, docketNum)" 
+"VALUES(" + """ +getAsSQLString(labelcaseName) 
+""" +","+ 
 
I know you can do this in vb. 
 
C

ceaton · Nov 17, 1999

I tried that and it doesn't work out right, thanks for trying! 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

calahans · Nov 18, 1999

Courtney, 
 
Your problem is not an SQL one, SQL does not have an issue with strings with single quotes in them. Maybe you need to post a question in the C forum. If you can pass a string out to a database with the o'brien like "o'brein" then the database will have no trouble. 
 
Sorry I can't be of more help, but at least its a small help! 
 
C

ceaton · Nov 18, 1999

Thanks anyway, I have my question posted in the Java(sun) forum also. I've had Otto help me, but I've run into this little problem and thought someone else may be able to help. 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

MikeLacey · Nov 30, 1999

I'm not up to much with Access - but with Oracle you can work with embedded single quotes like this: 
 
'Mike''s' 
 
Put another single quote in front of the embedded quote. 
 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

ceaton · Nov 30, 1999

This is the snippet of code that goes through each of my strings, maybe there is something missing? 
 
public String getAsSQLString(Label tf) { 
String s; 
 
s=tf.getText(); 
if ((s==null) ¦¦ (s.length()==0)) { s="null"; } 
else { s=" ' "+s+" ' "; } 
return s; 
} 
 
 
I tried adding another single quote to this line... 
 
else { s=" ' ' "+s+" ' ' "; } 
 
and that doesn't work either. 
 
Does anyone have any ideas how to insert the name O'Neil? 
 
Thanks, 
Courtney Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

DarkMan · Dec 1, 1999

Just a thought... If the single quotes are giving you a problem, why not write code in your application to replace them with another character (probably an extended character) before you insert, then change that character back to the single quote after you select the data for display? 
 
I know this may be a clunky solution, but it appears that Access will not accept a single quote as part of a string value ..... Doug Trocino <a href=mailto:dtrocino@tecumsehgroup.com>dtrocino@tecumsehgroup.com</a> <a href=

http://www.tek-tips.com>Tek-Tips

Forums</a> Web Application Developer 
Tecumseh Group, Inc. 
Sponsors of Tek-Tips Forums

ceaton · Dec 1, 1999

I'm not trying to display the data, I'm trying to INSERT the data, Access accepts everything except for something like "O'Brian". Anything with an apostrophe in the word. 
 
Is there some kind of other way to insert apostrophe words? 
 
Thanks, 
 
Courtney Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

ceaton · Dec 1, 1999

Here is the code I'm trying to get to work properly. 
 
public String getAsSQLString(Label tf) { 
String s; 
 
s=tf.getText(); 
if (s.charAt(i)=='\ ' ') { s.insert(i, '\ ' '); } 
else { s=" ' "+s+" ' "; } 
return s; 
} 
 
The error that I'm getting is for the i and it reads: 
Undefined variable: i 
 
I know I must be missing something, I'm just not sure what. 
 
Any ideas? 
 
Thanks again, 
Courtney 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

carp · Dec 1, 1999

I don't program Access, so this may be a meaningless observation. However, since "i" is what's giving you the problem because it's not defined, then we need to define it. Looking at your code, I suspect you are trying to traverse a character string. If that is the case, shouldn't the traversal portion be in a loop? 
 
Something like 
 
FOR i IN 1..length(s) LOOP 
if (s.charAt(i)=='\ ' ') { s.insert(i, '\ ' '); } 
else { s=" ' "+s+" ' "; }; 
END LOOP; 
 
I think this would define i and traverse the string on a character-by-character basis - but like I said, I'm just guessing here (as is probably obvious since I doubt if this is valid Access syntax - but you get the idea!). 
 
When all else fails, fall back on biblical documentation: "If thine i offends thee, pluck it out!"

ceaton · Dec 2, 1999

I got it to work, thanks for your help. 
 
Courtney Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

carp · Dec 2, 1999

Courtney - 
 
I'm glad to hear you got it to work. Could you post the final solution so all of us could learn from your trials? It might save somebody else a lot of trouble in the future.

ceaton · Dec 2, 1999

This is the entire SQL that I needed to perform an INSERT. I had 96 total fields to INSERT so they needed to be checked for words like "O'Brian" and if they where 
null, that it is what the function getAsSQLString does. 
I shortened this code for posting purposes. I gathered my information from label fields which was used to create a display for the end user, otherwise it would still work for textfields, just change the funtions first line from Label to TextField. Otto helped me out tremendously in getting this done so he deserves a lot of credit. 
 
Courtney 
 
 
 
String url = "jdbc

dbc:JVR"; 
Connection con; 
Statement stmt; 
 
String query = "INSERT INTO caseInfo(caseName, evalDate, docketNum, " 
+"state, county, geoType, caseSum, plainName, plainAge, plainSex, plainRace)" 
+"VALUES("+getAsSQLString(labelcaseName) 
+","+getAsSQLString(labelevalDate) 
+","+getAsSQLString(labelfileNum) 
+","+getAsSQLString(labelstate) 
+","+getAsSQLString(labelcounty) 
+","+getAsSQLString(labeltypeofCounty) 
+","+getAsSQLString(labelcaseS) 
+","+getAsSQLString(labelpname) 
+","+getAsSQLString(labelpage) 
+","+getAsSQLString(labelpsex) 
+","+getAsSQLString(labelprace) 
+&quot

"; 
 
try 
{ 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot

; 
} 
catch ( ClassNotFoundException e) 
{ 
System.err.println("ClassNotFoundException: "+ 
e.getMessage()); 
} 
 
try 
{ 
con = DriverManager.getConnection (url,"","&quot

; 
stmt = con.createStatement(); 
ResultSet rs = stmt.executeQuery(query); 
 
stmt.close(); 
con.close(); 
 
}catch (SQLException ex){ 
System.err.println("SQLException: "+ 
ex.getMessage()); 
} 
 
 
public String getAsSQLString(Label tf) 
{ 
String s; 
StringBuffer sb; 
int i, l; 
 
s=tf.getText(); 
if ((s==null) ¦¦ (s.length()==0)) { s="null"; } 
else { 
sb=new StringBuffer(s); 
l=s.length(); 
for (i=l-1; i>=0; i--) { 
if (sb.charAt(i)==' \ ' ') { sb.insert(i, ' \ ' '); } 
} 
s=" ' "+sb.toString()+" ' "; 
} 
return s; 
} 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

INSERT INTO problem

ceaton

MIS

calahans

Programmer

ceaton

MIS

calahans

Programmer

ceaton

MIS

MikeLacey

MIS

ceaton

MIS

DarkMan

Programmer

ceaton

MIS

ceaton

MIS

carp

MIS

ceaton

MIS

carp

MIS

ceaton

MIS

Similar threads

Part and Inventory Search

Sponsor