f



Trouble when submitting sql-query to MS Access Database

Hi,

I have some problems when trying to submit a simple insertion statement
to a MS Access Database. The reading is fine, but when I want to
execute an insertion statement using the following code:
	public void SQLCommand(String sql) throws SQLException {
		connection = DriverManager.getConnection(database);
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sql);
		connection.close();
	}

gives the following error:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
	at
org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45)
	at
org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15)

Could someone help me?

kito

0
kito
7/27/2006 8:46:55 PM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

9 Replies
2474 Views

Similar Articles

[PageSpeed] 47

Suggestion 1:
The error is caused because you're referring to a table that does not exist.
Try to run the query in a seperate query editor and verify that it does
work. Also check that the string you pass as argument is correct, e.g. not
concatenation that has destroyed the intended string.

Suggestion 2:
You probably need to add quotes in the executeQuery. Whenever you pass a
string to a database try to inclose the statement with single quotes, and
with ODBC in particular since its very strict with the syntax.

Suggestion 3:
You execute an update but not an query.



0
cp
7/27/2006 11:11:30 PM
To Suggestion 1:
I tried it out in the query editor of Access and then copied the
working query into my program. So the query is 100% ok, I tested it
several times!

To Suggestion 2:
I don't think that I have to add quotes, nevertheless I did it and then
I got this error:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
	at
org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45)
	at
org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15)

To Suggestion 3:
It is ok to use the executeUpdate(...) method, since my query doesn't
return any result and. It is simple an INSERT or DELETE query.


I really don't understand this error, since also on many internet pages
I found this approach, so...

0
kito
7/28/2006 6:38:25 AM
kito wrote:
> Hi,
> 
> I have some problems when trying to submit a simple insertion statement
> to a MS Access Database. The reading is fine, but when I want to
> execute an insertion statement using the following code:
> 	public void SQLCommand(String sql) throws SQLException {
> 		connection = DriverManager.getConnection(database);
> 		Statement stmt = connection.createStatement();
> 		stmt.executeUpdate(sql);
> 		connection.close();
> 	}
> 
> gives the following error:
> java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too
> few parameters. Expected 1.

<snip>

I'd be inclined to believe the error message and would insert
   System.out.println("SQLCommand: sql='"+sql+"'");
as the first statement of the SQLCommand() method.

If that shed no light on the problem, I'd construct and post a SSCE.
0
Ian
7/28/2006 9:27:02 AM
kito wrote:
> 'insert into Labels(label_name) values ("test");'

In ANSI SQL, shouldn't that be:

     insert into Labels(label_name) values ('test')

Double quotes should surround identifiers, not literal strings. OTOH, 
IIRC, Access does something non-standard with [ and ].

Tom Hawtin
0
Thomas
7/30/2006 9:31:35 AM
Well, then the output is the following:
SQL-Command:
'insert into Labels(label_name) values ("test");'
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
	at
org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:46)
	at
org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:12)

I also copied this query into Access at executed it from there and it
works perfectly...
Sorry, but what do you mean with SSCE??

Ian Wilson wrote:
> kito wrote:
> > Hi,
> >
> > I have some problems when trying to submit a simple insertion statement
> > to a MS Access Database. The reading is fine, but when I want to
> > execute an insertion statement using the following code:
> > 	public void SQLCommand(String sql) throws SQLException {
> > 		connection = DriverManager.getConnection(database);
> > 		Statement stmt = connection.createStatement();
> > 		stmt.executeUpdate(sql);
> > 		connection.close();
> > 	}
> >
> > gives the following error:
> > java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too
> > few parameters. Expected 1.
>
> <snip>
>
> I'd be inclined to believe the error message and would insert
>    System.out.println("SQLCommand: sql='"+sql+"'");
> as the first statement of the SQLCommand() method.
>
> If that shed no light on the problem, I'd construct and post a SSCE.

0
kito
7/30/2006 9:41:53 AM
On Sun, 30 Jul 2006 02:41:53 -0700, kito wrote:

> Well, then the output is the following:
> SQL-Command:
> 'insert into Labels(label_name) values ("test");'
> java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too
> few parameters. Expected 1.
> 	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
> 	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
> 	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
> 	at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
> 	at
> org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:46)
> 	at
> org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:12)
> 
> I also copied this query into Access at executed it from there and it
> works perfectly...
> Sorry, but what do you mean with SSCE??
> 

 I don't know about SSCE either, but the concern is the output of your
 query, like numbers of insert.. etc.. sounds like it expected 1, and
 there was none. So either look for the appropriate java function to use
 or change your query.

In hope it's gonna help.. 

friendly from a poor harassed man with no job because he is under heavy
harassment.
0
noname
7/30/2006 10:27:22 AM
Ohhh.... f***
Yes, for sure it should....When I wrote the query in the Access Editor
it allowed me to put quotes, but clearly I have to put the string
between ' '

Thanks a lot!!

Thomas Hawtin wrote:
> kito wrote:
> > 'insert into Labels(label_name) values ("test");'
>
> In ANSI SQL, shouldn't that be:
>
>      insert into Labels(label_name) values ('test')
>
> Double quotes should surround identifiers, not literal strings. OTOH,
> IIRC, Access does something non-standard with [ and ].
> 
> Tom Hawtin

0
kito
7/30/2006 3:28:31 PM
Ohhh.... f***
Yes, for sure it should....When I wrote the query in the Access Editor
it allowed me to put quotes, but clearly I have to put the string
between ' '
What a stupid mistake...

Thanks a lot!!

Thomas Hawtin wrote:
> kito wrote:
> > 'insert into Labels(label_name) values ("test");'
>
> In ANSI SQL, shouldn't that be:
>
>      insert into Labels(label_name) values ('test')
>
> Double quotes should surround identifiers, not literal strings. OTOH,
> IIRC, Access does something non-standard with [ and ].
> 
> Tom Hawtin

0
kito
7/30/2006 3:28:48 PM
kito wrote:
> Sorry, but what do you mean with SSCE??

I meant SSCCE but made a typing error.

http://mindprod.com/jgloss/sscce.html
0
Ian
8/3/2006 5:51:10 PM
Reply: