This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Servlets and the fly likes Adding elements to a database! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Adding elements to a database!" Watch "Adding elements to a database!" New topic
Author

Adding elements to a database!

Belloumi
Greenhorn

Joined: May 28, 2001
Posts: 5
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

Joined: Feb 14, 2000
Posts: 51
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

Joined: Feb 20, 2001
Posts: 3695

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

Joined: Jun 22, 2001
Posts: 140
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


Vikas Aggarwal

Founder @
Leads and Deals Limited

www.LeadsAndDeals.com
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Adding elements to a database!