Trouble when submitting sql-query to MS Access Database

Thread view:

kito - 27 Jul 2006 21:46 GMT

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

Reply to this Message

cp - 28 Jul 2006 00:11 GMT

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.

Reply to this Message

kito - 28 Jul 2006 07:38 GMT

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

Reply to this Message

Ian Wilson - 28 Jul 2006 10:27 GMT

> Hi,
>
[quoted text clipped - 11 lines]
> 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.

Reply to this Message

kito - 30 Jul 2006 10:41 GMT

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

> > Hi,
> >
[quoted text clipped - 19 lines]
>
> If that shed no light on the problem, I'd construct and post a SSCE.

Reply to this Message

Thomas Hawtin - 30 Jul 2006 10:31 GMT

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

Reply to this Message

kito - 30 Jul 2006 16:28 GMT

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

> > 'insert into Labels(label_name) values ("test");'
>
[quoted text clipped - 6 lines]
>
> Tom Hawtin

Reply to this Message

kito - 30 Jul 2006 16:28 GMT

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

> > 'insert into Labels(label_name) values ("test");'
>
[quoted text clipped - 6 lines]
>
> Tom Hawtin

Reply to this Message

noname - 30 Jul 2006 11:25 GMT

> Well, then the output is the following:
> SQL-Command:
[quoted text clipped - 13 lines]
> 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.

Reply to this Message

Ian Wilson - 03 Aug 2006 18:51 GMT

> Sorry, but what do you mean with SSCE??

I meant SSCCE but made a typing error.

http://mindprod.com/jgloss/sscce.html

Reply to this Message

Trouble when submitting sql-query to MS Access Database

Free Magazines