Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Adding elements to a database!

 
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

 
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.
 
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.
 
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic