Click here to Skip to main content
15,121,678 members
Please Sign up or sign in to vote.
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
Comments
[no name] 26-May-13 16:50pm
   
So validate the input prior to attempting to construct your SQL statement.

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]
   
v2
Comments
missak boyajian 26-May-13 17:08pm
   
No the field accepts Null values. For example when i open access and create a statement like this: INSERT INTO STUDENT
VALUES (450, '', '', '', '', '', '', '', '');

Its working, but executing from java application giving error
Maciej Los 26-May-13 17:28pm
   
See my answer now ;)
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())+")";
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900