Simple mdb connection Error

[Eric Belec]

For some reason, I can connect to but can't do the "INSERT INTO" statement on my database. The simple mdb file is here .. anyone, if there's an obvious reason why I can't insert data, and you could explain it to me...it would greatly be appreciated. It actually only has 3 entries in it... Its really weird because every other database, I can connect to. But I'm new to Databases so I might be overlooking something simple. Its just a Microsoft access Database .. nothing too complicated

Below the main part of code I use to connect to the database.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:ThePizza");
System.out.println("Connection Successful");
 
Statement st = con.createStatement();
String sql;

sql = "INSERT INTO PizzaOrder" + "(Order)"
   + " VALUES( " 
   + "'" + 5 + "')"; 
int rows = st.executeUpdate(sql);
st.close();

Any insight my problem would be appreciated.

[ November 29, 2007: Message edited by: Eric Belec ]
[ November 29, 2007: Message edited by: Eric Belec ]

[Rob Spoor]

Can you post the exception stack trace?

Also make sure the MDB file is not read-only.

[Eric Belec]

I get the following error

---------- Run Java ----------
GO
Connection Successful
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:288)
at TestDB.<init>(TestDB.java:29)
at TestDB.main(TestDB.java:47)
 
Output completed (0 sec consumed) - Normal Termination

WITH the following code

import javax.swing.*;
import java.sql.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.util.Random;
import java.io.*;
 
public class TestDB {
public TestDB()
{
    try
{ 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:ThePizza");
System.out.println("Connection Successful");
 
Statement st = con.createStatement();
String sql;

sql = "INSERT INTO PizzaOrder" + "(Order)"
   + " VALUES( " 
   + "'" + 5 + "')"; 
/*
sql = "INSERT INTO Worker" + "(theName)"
   + " VALUES( " 
   + "'" + 5 + "')"; */
int rows = st.executeUpdate(sql);
st.close();
con.close();
 
}
 (SQLException e)
{
System.out.println("Unable to connect");
System.out.println("SQL error: " + e);
}

catch (Exception ex)
{
ex.printStackTrace();
}
}
public static void main (String args[]){
System.out.println("GO");
TestDB eric = new TestDB();
}
 
}

[ November 30, 2007: Message edited by: Eric Belec ]
[ November 30, 2007: Message edited by: Eric Belec ]

[Herman Schelti]

hi Eric,

Your code just looks too complicated:

sql = "INSERT INTO PizzaOrder" + "(Order)"
   + " VALUES( " 
   + "'" + 5 + "')";     

try: just one String, and put a space after PizzaOrder

sql = "INSERT INTO PizzaOrder (Order) VALUES('5')";

Herman
PS if you use a preparedstatement, you won't even need the quotes anymore.

[Eric Belec]

I tried,

sql = "INSERT INTO PizzaOrder (Order) VALUES('5')" ;

and I'm still getting the "INSERT INTO" error.

just so you know PizzaOrder is a table and the primary key is "Order" which holds a string.

So doesn't this mean there's a problem with the database? As opposed to my code?

[Herman Schelti]

hi Eric,

Sorry I did not work out (yet).

I Googled some,
maybe Access just does not like the name of this column: "Order".


Herman

[Eric Belec]

Thank you, apparently it didn't like "Order" so I changed that to "Orders" in the database.

Thanks again.

[Rob Spoor]

Order is a reserved word in SQL, just like Select and From to name just a few.

You still can use these as names for tables and columns, if you surround them with the table/column name escape characters. In Access and SQL, that is [ and ], so it would be "INSERT INTO PizzaOrder ([Order]) VALUES('5')".
For some other database types it might be ` and `.

[Jeanne Boyarsky]

Originally posted by Rob Prime:
You still can use these as names for tables and columns, if you surround them with the table/column name escape characters.

Of course you want to avoid this if possible since it makes the code harder to maintain. I'm sure Rob is just mentioning it to be thorough, not as advice.

[Rob Spoor]

Correct. The only reserved word I use as column name myself is "name", and that one is only a reserved word in MS SQL Server (well, Query Analyzer marks it in blue ;)).

Unfortunately, sometimes other manufacturers are not as wise. I once had problems installing a package on a MS SQL Server 2005 server. The problem was that one of the column names was a reserved word. We had to enable using reserved words as columns and turn the database compliancy to SQL Server 2000 for it to work. Bloody morons.