Syntax Error In Insert Into Statement

Question

0.00/5 (No votes)

See more:

Hello,
I have a java application connected to microsoft access 2013 with JDBC.
If the user will add a student, he should fill the empty fields. But the problem is that if the user keeps one field empty like his city, an error is displaying saying that there is an error in the insert into statement. But if he fills all of them its working correct.

The statement is the following.

VB

String AddStudents="INSERT INTO STUDENT VALUES('"+IdentityField.getText()+"','"+FNameField.getText()
+"','"+LNameField.getText()
+"','"+DateField.getText()
+"',"+GPAField.getText()
+",'"+AreaField.getText()
+"','"+CityField.getText()
+"','"+BuildingField.getText()

+"',"+  AdvisorField.getText()+")";

Posted 26-May-13 10:48am

missak boyajian

Add a Solution

Comments

[no name] 26-May-13 16:50pm

So validate the input prior to attempting to construct your SQL statement.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-05-26T10:56:00

It means that City field does not accept NULL values ;)

Please, folow these links:
10 tricks for handling null values in Microsoft Access[^]
Common Errors with Null[^]
Create a table (MS Access)[^]

[EDIT #1]
Another solution is to define the order of fields:

SQL

INSERT INTO TableName (Field1, Field2, Field3,... FieldN)
VALUES(...)

Probably, you're trying to insert values which is not acceptable for this field.

Remember, date fields must be preceded and ended with # sign.

SQL

INSERT INTO TableName (StringField, DateField, IntField)
VALUES('myString', #2013/05/26#, 1)

[/EDIT]

Gianmaria Gregori · Answer 2 · 2013-05-26T11:47:00

Solution 2

I don't know Java, but in my opinion you should test for null.
Add a function like

Java

public static string testString(String str) {
   string temp = "";
   if (str != null) {
      temp = str;
   }
   return temp;
}

and test all your fields

Java

String AddStudents="INSERT INTO STUDENT VALUES(
'"+testString(IdentityField.getText())
+"','"+testString(FNameField.getText())
// so on..
+"',"+testString(AdvisorField.getText())+")";

Posted 26-May-13 11:47am

Gianmaria Gregori

Comments

missak boyajian 26-May-13 18:14pm

I already had tried that and still doesnt work :(

Gianmaria Gregori 27-May-13 11:55am

Try to output your AddStudents string and then use it as is directly in Access