prepared statements

[spider_pig]

I have a table with 9 fields the first being an autonumber data type. If I use a prepared statement to insert a row how do I get around the fact that I don't need to give a value for the first column? If I use a placeholder (?) then I need to give a value for it. Here is my code:

String query = "INSERT INTO orders VALUES (what do I put(?),?,?,?,?,?,?,?,?)";
java.sql.PreparedStatement statement = Conn.prepareStatement(query);

statement.setString(1,request.getParameter("firstname"));
statement.setString(2,request.getParameter("surname"));
statement.setString(3,request.getParameter("address"));
statement.setString(4,request.getParameter("city"));
statement.setString(5,request.getParameter("postcode"));
statement.setString(6,request.getParameter("card_number"));
statement.setString(7,request.getParameter("card_type"));
statement.setString(8,request.getParameter("totalvalue"));

statement.executeUpdate();

 

[gemma-the-husky]

not sure about the syntax you are using

if you use sql string, but you need to format the string exactly in the following way, with the brackets, to avoid a sysntax error. look at the sql for an existing query you have to see what it looks like

ie "insert into mytable (field1, field2, field3) values 1,2,3"

(i think the syntax is like this)

then access automatically adds an autonumber without you doing anything (in the same way that the autonumber appears automatically when you enter a new line manually into a table).

 

[spider_pig]

The whole point is that I want to use a prepared statement so that it can be repeated. In MySql you can pass a zero or a null value into an auto-increment column and the next number in the sequence is used. How do I do something similar in Access?

 

[ByteMyzer]

In Microsoft Access, you don't need to pass anything to the AutoNumber field. For example, if you have a table like the following:

MyTable
-------
Field1 | AutoNumber
Field2 | Text
Field3 | Text

Your SQL Statement should read:

INSERT INTO MyTable (Field2, Field3) VALUES (?, ?);

 

[Banana]

The whole point is that I want to use a prepared statement so that it can be repeated. In MySql you can pass a zero or a null value into an auto-increment column and the next number in the sequence is used. How do I do something similar in Access?

Access doesn't work quite like this.

In several RDBMS, the autonumber/autoincrement/identity/whatever are assigned after the insert statement is executed, while Access creates a AutoNumber as soon as you dirty the record. You can prove to yourself by creating a simple table with an autonumber and an extra column, and have a copy of that table linked in say, MySQL. Open both table. Dirty the text column, and you'll see a autonumber assigned as soon as you dirty the second column in Access's table, while with MySQL's table, it stays blank until you move away from that row or save the row.

So for purpose of writing queries, you usually wouldn't include autonumber columns; let Access deal with it. (Actually, this applies to most backends as well; it's usually not often that we need to muck around with autonumber column)

Also, to be clear, while Access supports parameter query, which is similar to MySQL's prepared statements, they are not exactly same. There is no such thing as prepared statements (e.g. a query where you can have optional parameters and can reuse it for fast execution), though parameter query does a good job as long you don't mind requiring all the parameters, or you can adopt raskew's VBA module for enabling optional parameters. Search the forum for raskew's code.

HTH.