CODEWITHSUNDEEP
javamysqljdbc
Varshini
Varshini

Reputation: 199

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '98)' at line 1

My JDBC code is:

String name=req.getParameter("name");
String regno=req.getParameter("regno");
String m1=req.getParameter("m1");
String m2=req.getParameter("m2");
String m3=req.getParameter("m3");

Statement stmt=con.createStatement();
stmt.execute("create table students(sname varchar(20),regno varchar(10),mark1 int,mark2 int,mark3 int)");
stmt.execute("insert into students values('"+name+"','"+regno+"',"+m1+","+m2+","+","+m3+")");

I get the below error after I execute above code.

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '98)' at line 1

How is this caused and how can I solve it?

Upvotes: 2

Views: 4275

Answers (2)

vivek_ganesan
vivek_ganesan

Reputation: 698

You have an extra comma ","+","

This error is in the line with insert statement.

 stmt.execute("insert into students values('"+name+"','"+regno+"',"+m1+","+m2+","+","+m3+")");

Let's assume your values are as follows

 name = x
 regno = 2
 m1 = 33
 m2 = 45
 m3 = 48

Then, the SQL insert statement created by your program (string concatenation) will be

 insert into students values('x','2',33,45,,48);

The above statement is wrong and has an extra comma before 48

Remove the extra +"," block in the insert statement line and you can make this right.

Upvotes: 1

Jim Garrison
Jim Garrison

Reputation: 86774

You build the INSERT statement dynamically, and in that statement you have

m2+","+","+m3

So if m2==1 and m3==2 this generates

1,,2

Which makes the SQL syntax invalid. You need to be using a parameterized query with a prepared statement instead, as in

PreparedStatement ps = con.prepareStatement("insert into students values(?,?,?,?,?)");
ps.setString(1,name);
ps.setString(2,regno);
ps.setInt(3,m1);
ps.setInt(4,m1);
ps.setInt(5,m3);
int nupdate = ps.executeUpdate();

Currently the values of m1, m2 and m3 are Strings in the code but int in the database. You should convert their values to int for use with the PreparedStatement.

Upvotes: 3

Related Questions