1.11M Members

Where is the problem in this SQL statement?

 
0
 

Hi,

I recieve the following error message:
"java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression'name '"

when I run this statement:

sql = "INSERT INTO empDetail VALUES(";
        sql += txtName.getText()+ ", ";
        sql += "'" + txtAddress.getText()+ "', ";
        sql += "'" + txtSuburb.getText()+ "', ";
        sql += "'" + txtPostCode.getText()+ ", ";
        sql += "'" + txtDOB.getText()+ "', ";
        sql += "'" + txtPhone.getText() + "', ";
        sql += "'" + txtWorkExt.getText()+ "', ";
        sql += "'" + txtMobile.getText()+ "', ";
        sql += "'" + txtEmail.getText()+"')";

I can't see the problem where is the missing operator?(and yes I have my glasses on :)
I have been going over and over it the last 2hrs but without any luck.
can you spot what's going on.

 
0
 

You're missing the ' before the comma in this line:

sql += "'" + txtPostCode.getText()+ ", ";

Sometimes it just needs a new pair of eyes:)

 
0
 

You're missing the ' before the comma in this line:

sql += "'" + txtPostCode.getText()+ ", ";

Sometimes it just needs a new pair of eyes:)

Thank you,
I have changed that but I am still receiving the same error

 
0
 

There could be any number of problems, but the most likely is that you have not listed the column names that you expect to populate with the data listed in the VALUES clause. Generally speaking, it is good procedure to explicitly list the column names like so (and these names are fictitious...just for example's sake):

sql = "INSERT INTO empDetail ";
sql += "(empName, empAddress, empSuburb, empPostCode, empDOB, empPhone, empWorkExt, empMobile, empEMail) ";
sql += "VALUES('" + txtName.getText() + "', ";
sql += "'" + txtAddress.getText() + "', ";
sql += "'" + txtSuburb.getText() + "', ";
sql += "'" + txtPostCode.getText() + ", ";
sql += "'" + txtDOB.getText() + "', ";
sql += "'" + txtPhone.getText() + "', ";
sql += "'" + txtWorkExt.getText() + "', ";
sql += "'" + txtMobile.getText() + "', ";
sql += "'" + txtEmail.getText() + "')";

Let's say for instance that the table has an identity column "empID". If you don't explicitly list the column names, the INSERT statement will assume you mean for your first value to go into that column.

There could also be mismatched datatypes, but we'll ignore that for now. Or, heaven forbid, you get your columns in a different order than your VALUEs.

As a final word of advice, whenever you get odd errors you may want to just print the value of your variable "sql" and paste in an SSMS query window and see if it actually works. Sometimes when you strip away all the dynamic construction code and see what's actually being executed, the error will jump out at you.

Hope this helps! Good luck!

Question Answered as of 2 Years Ago by hericles and BitBlt
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article