aspose file tools*
The moose likes JDBC and the fly likes INSERT Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "INSERT Issue" Watch "INSERT Issue" New topic
Author

INSERT Issue

Tom Ben
Ranch Hand

Joined: Aug 17, 2001
Posts: 109
I am trying to insert some data into an Access DB. Here is my sql statement.

st is an object of Statement.
I get this error message [Microsoft][ODBC Microsoft Access Driver]To few parameters. Expected 3
I table only has three fields and I am only passing it three values in the order they are to go in. I tested the sql statement in Access and it worked. Please Help
[ Edited by Dave to format code ]
[ February 24, 2002: Message edited by: David O'Meara ]

Sun Certified Programmer on the Java 2 Platform
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

use single quotes around your strings instead of double quotes:
String sql = "INSERT INTO PhoneList VALUES('"+state+"','"+number.substring(1,4)+"','"+number.substring(5, number.length())+"')";
a way to verify that the SQL statement is correct is to System.out.println(sql); where 'sql' is the String that you used in your code. Cut and paste the output to your MS Access SQL editor and run it. The error message you get there will be much more informative than the one that JDBC-ODBC supplies.
I have seen that error message used out of context before by JDBC-ODBC. It also means that you have a spelling mistake on your column names or table names in a query.
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I have tried your code. Change the double quotes to single quotes and it will work. Must be an ODBC thing (which the JDBC-ODBC uses).
just a sidenote:
String number = "1234567890";
number.substring(1,4) equals "234"
number.substring(5,number.length()) equals "67890"
you lose the numbers "1" and "5" from the number string. If that is what is intended then disregard this sidenote.
Jamie
[ February 22, 2002: Message edited by: Jamie Robertson ]
Tom Ben
Ranch Hand

Joined: Aug 17, 2001
Posts: 109
Yes the single quotes worked I tried the Access SQL window and it worked fine with both single and double quotes. The substring usage is because the number object is a telephone number (444)111-2345
Thanks for the help everyone
Younes Essouabni
Ranch Hand

Joined: Jan 13, 2002
Posts: 479
I have the same problem and couldn't resolve it with your solution.
Could you explain???
Is it "' (double and single)or'"(single and double quote).
I don't see well on my screen.
Thx for your answers!!!


Younes
By constantly trying one ends up succeeding. Thus: the more one fails the more one has a chance to succeed.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Younes Essouabni:
I have the same problem and couldn't resolve it with your solution.
Could you explain???
Is it "' (double and single)or'"(single and double quote).
I don't see well on my screen.
Thx for your answers!!!

It depends on where in the string it occurs. In general, the single quote must be contained in the String. So this is how it should be:

This would translate into "SELECT * FROM emp where name = 'John Doe' and age = 23"
Hope this explains it well enough for you.
Jamie
[ February 28, 2002: Message edited by: Jamie Robertson ]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

and will break if I put my name in...
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by David O'Meara:
and will break if I put my name in...

He's not supposed to know about that until he posts in a few more weeks having that error. Come on Dave, this is how you keep them coming back! Besides, if we had to warn people about all the possible errors they may encounter on their first posting, the forum database would run out of room... fast! Especially with the volume of people using MS Access and the jdbc dbc bridge
Jamie
p.s.
I always thought it was deserving that the graemlin that appears when you type in jdbc: odbc is the embarassment graemlin!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: INSERT Issue
 
Similar Threads
jdbc:odbc insert error fails with code -3502
How to INSERT records into Excel File using JDBC
Simple mdb connection Error
insert to database...
problem in inserting data into ms access