Help with sql insert

[jmorris]

I am trying to insert data into my database, but I get the "data type mismatch in criteria expression" error. Everything in the database is stored as text EXCEPT the autonumber field id. It is the first field in the database.

This is my insert:

String query = "INSERT INTO Customer VALUES ('','" + fname + "','"+ lname + "','" + address + "','"+ city + "','"+ state + "','"+ zipcode + "','"+ phone + "')";

I tried to insert an empty string into the id field and that's what gives me the error.
I just need to know how to deal with the autonumber field...somebody pls help...and thx :)

[serjant]

Most elegant to insert into sql tables is in this way:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

[jmorris]

Ok so now my insert is as follows:

INSERT INTO Customer (fName, lName, address, city, state, zipcode, phone)
VALUES ('" + fname + "','"+ lname + "','" + address + "','"+ city + "','"+ state + "','"+ zipcode + "','"+ phone + "')

but I still need to know how to deal with the autonumber field...

[masijade]

Are your table columns actually named "fName" and so? I doubt it.

Also use a PreparedStatement, cobbling together an SQL Statement like this is error-prone and just begging for problems (both of an innocent nature and as SQL injection attacks).

To handle an automatically generated value field, just simply leave that field off the column and value list (i.e. don't include it).

[gustio]

Autonumber field is generated by DBMS.
If you need only for that field, try with less field:
INSERT INTO Customer (fName)
VALUES ('" + fname + "')
It will insert a row on your db, only id and fName have value, others null

[masijade]

Autonumber field is generated by DBMS.
If you need only for that field, try with less field:
INSERT INTO Customer (fName)
VALUES ('" + fname + "')

Like I said, cobbling together an SQL String this way is not the way anyone should want to do this. It is error prone (and outright dangerous).

It will insert a row on your db, only id and fName have value, others null

Only if those columns are defined to allow null values.

[jmorris]

ok I think I got it now. Thanks for the help. I redid my query using prepared statements.