Syntax error in INSERT INTO statement

Get Started
1

Hi folks,

Can anyone see what’s wrong in the Insert query statement? Categorylist is a listbox value (not dynamic). Publ is a checkbox value and the others are text boxes. I’m using JSP, trying to update the Access 2000 database from a form.

char publ;

   String ename =(request.getParameter("eventname"));
   String loc =(request.getParameter("locat"));
   String sdt =(request.getParameter("sdate"));
   String edt =(request.getParameter("edate"));
   String stm =(request.getParameter("stime"));
   String etm =(request.getParameter("etime"));
   String des =(request.getParameter("desc"));
   String comm4 =(request.getParameter("comment4"));
   String cat =(request.getParameter("categorylist"));

  if (request.getParameter("publ") != null) {
    publ = 'Y';
  } else {
    publ = 'N';
  }

  try {

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con = DriverManager.getConnection("jdbc:odbc:GENESIS","","");

    Statement stmt = con.createStatement();

     stmt.executeUpdate("insert into tblEvent (event_name,start_date,end_date,start_time,(end time),event_desc,event_category,publish,comments,event_location) values('"+ename+"','"+sdt+"','"+edt+"','"+stm+"','"+etm+"','"+des+"','"+cat+"','"+publ+"','"+comm4+"','"+loc+"')");

   }
  catch (Exception e) {
  out.println("<h1>ERROR!</h1>");
  out.println("Error = " + e.getMessage() + "<br><pre>");
  e.printStackTrace(new java.io.PrintWriter(out));
  out.println("</pre>");
  return;
}
finally{
  try{
    if(con != null) con.close();
  }
  catch(SQLException sqle){}
}
   response.sendRedirect("addevent.jsp");

I’m getting his error:

Error = [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
etc...
etc...
etc....
2

try prining
String query = “insert into tblEvent (event_name,start_date,end_date,start_time,(end time),event_desc,event_category,publish,comments,event_location) values('”+ename+“‘,’”+sdt+“‘,’”+edt+“‘,’”+stm+“‘,’”+etm+“‘,’”+des+“‘,’”+cat+“‘,’”+publ+“‘,’”+comm4+“‘,’”+loc+“')”;
out.println(query);

copy this output on the screen and put it in the access directly you will get exact problem.

3

Thanks. Will try that when I get home later. I hope that works.

4

microsoft access wants hash marks around its date strings, and it wants no quotes around its numeric literals

so, like this –

insert
  into tblEvent
     ( event_name
     , start_date
     , end_date
     , start_time
     , end time
     , event_desc
     , event_category
     , publish
     , comments
     , event_location )
values
     ( 'weekend boozer'
     , #2005-04-15#
     , #2005-04-18#
     , '5:30 p.m.'
     , '9 a.m.'
     , 'what''s up, doc'
     , 937
     , 0
     , 'B there or B square'
     , 'my house' )

note that access date/time values always include a time component, so best practice is not to have separate date and time fields

5

Actually, those date and time fields are stored as Text fields (less stress) .

I don’t I changed the code, now I get another type of error msg!

Error = [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)

etc....
etc.....

Hmmmmm I’m now wondering just what that “char publ” is doing there!!!

<body>
  <%

   Connection con = null;
   char publ;

   String ename =(request.getParameter("eventname"));
   String loc =(request.getParameter("locat"));
   String sdt =(request.getParameter("sdate"));
   String edt =(request.getParameter("edate"));
   String stm =(request.getParameter("stime"));
   String etm =(request.getParameter("etime"));
   String des =(request.getParameter("desc"));
   String comm4 =(request.getParameter("comment4"));
   String cat =(request.getParameter("categorylist"));

  if (request.getParameter("publ") != null) {
    publ = 'Y';
  } else {
    publ = 'N';
  }

  try {

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con = DriverManager.getConnection("jdbc:odbc:GENESIS","",""); // connection to GENESIS database

    Statement stmt = con.createStatement();
     stmt.executeUpdate("insert into tblEvent (event_name,start_date,end_date,start_time,end_time,event_desc,event_category,publish,comments,event_location) values('"+ename+"','"+sdt+"','"+edt+"','"+stm+"','"+etm+"','"+des+"','"+cat+"','"+publ+"','"+comm4+"','"+loc+"')");

     }
catch (Exception e) {
  out.println("<h1>ERROR!</h1>");
  out.println("Error = " + e.getMessage() + "<br><pre>");
  e.printStackTrace(new java.io.PrintWriter(out));
  out.println("</pre>");
  return;
}
finally{
  try{
    if(con != null) con.close();
  }
  catch(SQLException sqle){}
}
   response.sendRedirect("addevent.jsp");


  %>
 </body>

I’m wondering if part of the problem is trying to grab the selected value from the option list (categorylist) and also that check box too. Just what is the corect code for doing those two( selecting one value from the list box…and also the value from thecheckbox)! I’d be a happier camper if anyone can solve this. I’m losing sleep and getting crabbier. LOL.
Please don’t confuse me with too much technical talk…lol…I’m sleep depraved! All the fields are TEXT except for the check box…it’s Yes/No in the Access 2000 db.

Thanx