Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Adding elements to a database!

 
Belloumi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there
I am trying to update a database using a form. My form seems to be working okay. But when I try updating the databse i get this error message:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Here is the actual jsp file where I am trying to update the database
<html>
<head>
<title>Database Update!</title>
</head>
<%@ page language="java"%>
<%@ page info="database handler"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.servlet.*"%>
<%@ page import="javax.servlet.http.*"%>
<body>
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String inam = request.getParameter("inpnam");
String iform = request.getParameter("inpform");
String ival = request.getParameter("inpval");
String icom = request.getParameter("inpcom");
String url="jdbc dbc:databasename";
Connection con=DriverManager.getConnection(url, "SA", "");
Statement stmt = con.createStatement();
int rowsAffected = stmt.executeUpdate("insert into test1(Name,Format,Value,Comment) values('" + inam + "','" + iform + "'," + ival + ",'" + icom +"')");
if (rowsAffected == 1){%>
<h4>Addition complete!!</h4>
<%}
else {%>
<h4>Sorry addition failed!!</h4>
<% }
stmt.close();
con.close();
}
catch (Exception e) {
out.println(e);
}
%>
</body>
</html>
Thanks in advance for any help

 
Linda Xu
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the problem is in your SQL query, to my guess your "name" is a string, so you need a ' to quote it, to write this in Java, you need a \ to escape, like"
query = "insert into tableName(name) values(\'userName\');";
Wish this to be helpful to you.
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Linda, you don't need to escape single quotes.

But do put a space between the table name and the opening bracket of the fields list, and a space between the values keyword and the opening bracket of the values list.

The last time I was debugging a SQL statement, I also ran into the problem of using a SQL keyword as a column name (which Access lets you do!). So as a comment, 'Value' is very close to 'values', a keyword. I would consider changing it.

As a last suggestion, I'd check the inam, iform, ival and icom strings for the null condition. If they are null, you should replace it with the actual string "null".
Hope that all helps.
 
Vikas Aggarwal
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think there is nothing wrong with the insert statement. The only thing I would like to suggest you is to look at the database schema once more.
1. Check whether you are trying to insert a diffrent data type in to the database field with another data type.
2. Check whether you are trying to insert special characters especially (' single quoate) in to a varchar/text field.
3. Check whether you have allowed the acceptance of null in the database schema.
4. The better of doing this kind of things is to use PreparedStatements which allow you to enter any kind of special characters and also keeps a check whether a valid datatype is being entered or not.
5. Just for trial use...
stmt.executeUpdate("insert into test1 values('" + inam + "','" + iform + "'," + ival + ",'" + icom +"')");

Originally posted by Mustapha Abella:
Hi there
I am trying to update a database using a form. My form seems to be working okay. But when I try updating the databse i get this error message:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Here is the actual jsp file where I am trying to update the database
<html>
<head>
<title>Database Update!</title>
</head>
<%@ page language="java"%>
<%@ page info="database handler"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.servlet.*"%>
<%@ page import="javax.servlet.http.*"%>
<body>
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String inam = request.getParameter("inpnam");
String iform = request.getParameter("inpform");
String ival = request.getParameter("inpval");
String icom = request.getParameter("inpcom");
String url="jdbc dbc:databasename";
Connection con=DriverManager.getConnection(url, "SA", "");
Statement stmt = con.createStatement();
int rowsAffected = stmt.executeUpdate("insert into test1(Name,Format,Value,Comment) values('" + inam + "','" + iform + "'," + ival + ",'" + icom +"')");
if (rowsAffected == 1){%>
<h4>Addition complete!!</h4>
<%}
else {%>
<h4>Sorry addition failed!!</h4>
<% }
stmt.close();
con.close();
}
catch (Exception e) {
out.println(e);
}
%>
</body>
</html>
Thanks in advance for any help


------------------
Vikas Aggarwal
Technology Associate
Shakun Global Networks
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic