java - odbc - insert into syntax error

Question

I am trying to update a access database via odbc driver through java.

Table name is form1

I am getting a syntax error when I execute this command:

updates="INSERT INTO form1 
(entrydate,name,gender,month,date,phno,emailid,facebookid,address,semester,
bloodgroup,slno,college,department,liveprojects,trainings); 

values("+abc+",'"+t3.getText()+"','"+t4.getText()+"',"+def+","+def1+","+zzz+",'"
      +t8.getText()+"','"+t9.getText()+"','"+t10.getText()+"',"+aaaa+",'"
      +t12.getText()+"',"+xyz+",'"+t13.getText()+"','"+dd+"','sa','da')";

Thank you.

What's that ; doing after the column list? (And please do read about sql injection and bind parameters.) — Mat
– Mat, Commented Aug 4, 2012 at 10:42
You shouldn't be using ODBC, use a real JDBC driver instead. — user330315
– user330315, Commented Aug 4, 2012 at 12:10

Sergey Kalinichenko · Accepted Answer · 2012-08-04 10:51:46Z

1

There are two problems with your statement - a real and a potential one:

Real: the semicolon before the values keyword needs to be removed
Potential: the statement needs to be converted for use with parameters, otherwise a single quote in the body of any of the string parameters will cause a syntax error.

Here is how you can switch to parameterized prepared statements:

String updates="INSERT INTO form1 (entrydate,name,gender,month,date,phno,emailid,facebookid,address,semester,bloodgroup,slno,college,department,liveprojects,trainings) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement psUpd = con.prepareStatement(updates);
psUpd.setInt(1, abc);
psUpd.setString(2, t3.getText());
psUpd.setString(3, t4.getText());
psUpd.setInt(4, def);
psUpd.setInt(5, def1); // The types of parameters need to match the type of setXYZ
... // Continue for the remaining parameters, then call
psUpd.executeUpdate();

edited Aug 4, 2012 at 10:51

answered Aug 4, 2012 at 10:43

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohammod Hossain · Accepted Answer · 2012-08-05 03:59:19Z

1

First problem:

updates="INSERT INTO form1 
(entrydate,name,gender,month,date,phno,emailid,facebookid,address,semester,
bloodgroup,slno,college,department,liveprojects,trainings);

*you are separating field and values with ; but it will be blank *

Secondly It is best to use prepareStatement

String sql ="INSERT INTO TableName  
(entrydate,name,gender,month,date,phno,emailid,facebookid,address,semester,
bloodgroup,slno,college,department,liveprojects,trainings) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?); 

    PreparedStatement ps = con.prepareStatement(sql );    
     int index = 1;
    ps.setInt(index++, abc);
    ps.setString(index++, t3.getText());
    ps.setString(index++, t4.getText());
    ps.setInt(index++, def);
    ps.setInt(index++, def1); // The types of parameters need to match the type of setXYZ
    ... // Continue for the remaining parameters, then call
    ps.executeUpdate();

edited Aug 5, 2012 at 3:59

answered Aug 4, 2012 at 11:27

Mohammod Hossain

4,1042 gold badges28 silver badges37 bronze badges

2 Comments

Sachin Sac Over a year ago

when PreparedStatement used i get message Driver does not support this function....why do i get this message????i use access 2003 and Odbc Driver

Sachin Sac Over a year ago

I used the following code and it worked..Thanks guys for the help....cheers Statement stmt=con.createStatement(); String insert="insert into Students (slno,entrydate,names,gender,dob,phno,emailid,facebookid,address,semester,bloodgroup,college,department,liveprojects,trainings) " + "values("+serialno+","+entdate+",'"+nameofs+"','"+Gender+"',"+bod+","+phone+",'"+emaid+"','"+femaid+"','"+addr+"',"+sem+",'"+bg+"','"+coll+"','"+depto+"','java','java')";

micha · Accepted Answer · 2012-08-04 10:43:42Z

0

Try to remove the ; before values.

answered Aug 4, 2012 at 10:43

micha

49.9k16 gold badges75 silver badges80 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

1.

Use INSERT INTO form1 values(value1,value2......); directly.

2. If you want to stick to your own format... then do the following changes.

- Remove the ; before the values.

- To insert the comma, its better to use "," instead of ','

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 4, 2012 at 10:47

Kumar Vivek Mitra

33.5k6 gold badges50 silver badges75 bronze badges

Comments

Durgadas Kamath · Accepted Answer · 2012-08-04 10:55:22Z

0

The semicolon (;) after the column names is the culprit. You are terminating the statement by doing that. just remove the ; . your code will work.

answered Aug 4, 2012 at 10:55

Durgadas Kamath

3902 silver badges12 bronze badges

1 Comment

Durgadas Kamath Over a year ago

Also make use of prepare statements. Its not good to inject directly the values in the string.

Nipun Jain · Accepted Answer · 2012-08-04 11:45:12Z

0

try this.....

updates="INSERT INTO form1(entrydate,name,gender,month,date,phno,emailid,facebookid,address,semester,bloodgroup,slno,college,department,liveprojects,trainings) 
values( '"+abc+"','"+t3.getText()+"','"+t4.getText()+"','"+def+"','"+def1+"','"+zzz+"','" +t8.getText()+"','"+t9.getText()+"','"+t10.getText()+"','"+aaaa+"','"
  +t12.getText()+"','"+xyz+"','"+t13.getText()+"','"+dd+"','sa','da')";

answered Aug 4, 2012 at 11:45

Nipun Jain

6014 silver badges6 bronze badges

Collectives™ on Stack Overflow

java - odbc - insert into syntax error

6 Answers 6

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related