java.sql.SQLException: [Microsoft][ODBC Microsoft Access > Driver] Too few parameters. Expected 1

Question

I'm calling InserStudent.jsp file in action of the form from AddStudent.jsp from where I want to insert the data in database.

My database table's structure is below:

ID|Name|RollNumber|PhoneNumber|StudyProgram|Status

Below is the code for InserStudent.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>   
<body>

    <%
     String nam=request.getParameter("stuname");  
     String roll=request.getParameter("sturoll");  
     String phone=request.getParameter("stuphone");  
     String prog=request.getParameter("stuprogram");  
     String stats=request.getParameter("stustatus");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url =  "jdbc:odbc:stdProjectDataDSN";
Connection c = DriverManager.getConnection(url);
Statement statement = c.createStatement() ;
String sql = "insert into students (ID, NAME, RollNumber, PhoneNumber, StudyProgram, Status )";
sql += "values ( '"+ nam +"','"+ nam +"','"+ roll +"','"+ phone +"','"+ prog +"',"+ stats +" )";
statement.execute ( sql );
c.close();
response.sendRedirect("ManageAllStudent.jsp");
    %>
 </body>
</html>

This results in the following exception:

HTTP Status 500

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /web/InserStudent.jsp at line 27

24: Statement statement = c.createStatement() ;
25: String sql = "insert into students (ID, NAME, RollNumber, PhoneNumber, StudyProgram, Status )";
26: sql += "values ( '"+ nam +"','"+ nam +"','"+ roll +"','"+ phone +"','"+ prog +"',"+ stats +" )";

27: statement.execute ( sql );
28: c.close(); 29: 30: response.sendRedirect("ManageAllStudent.jsp");

Stacktrace: org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:521) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:412) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)

root cause

javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Microsoft Access > Driver] Too few parameters. Expected 1. note The full stack trace of the root cause is available in the Apache Tomcat/6.0.35 logs.javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

What am I missing or what am I doing wrong? In ShowAllStudent.jsp page it shows me all the data from database where I use the same script.

also edit the below code for **Class.forName** but it also does'not work `Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();` — mobi001, Jun 29 '12 at 01:08

score 1 · Answer 1 · edited May 23 '17 at 11:56

You shouldn't write Java code in JSP (Read SO FAQ - How to avoid Java Code in JSP). You must have to add Servlet to perform database operations.

Apart from this you have to learn how JDBC API works and as @BalusC commented that your code is victim of sql injection vulnerability. To avoid SQL injection, use the PrepreadStatement (parameterized query).

Connection cn=null;
PreparedStatement ps=null;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url =  "jdbc:odbc:stdProjectDataDSN";
Connection c = DriverManager.getConnection(url);

try{
  String sql="insert into students (ID,NAME,RollNumber,PhoneNumber,StudyProgram,Status) 
               Values (?,?,?,?,?,?)";
  ps=cn.prepareStatement(sql);
  ps.setInt(1,10);
  ps.setString(2,nam);
  ...
  ps.executeUpdate()
}catch(SQLException ex){

}finally{
  if(ps!=null){
     try { ps.close(); } catch(Exception ex) {}
  }
  if(cn!=null){
     try { cn.close(); } catch(Exception ex) {}
  }
}

PS: In case the column ID is autogenerate number then do not include in your column set.

 String sql="insert into students (NAME,RollNumber,PhoneNumber,StudyProgram,Status) 
                   Values (?,?,?,?,?)";

score 1 · Accepted Answer · answered Jul 02 '12 at 00:12

1

I got what iam missing i didnot configure my database in control panel->administrative tools...

So when i set it up it just work:)

answered Jul 02 '12 at 00:12

mobi001

517
1
8
19

java.sql.SQLException: [Microsoft][ODBC Microsoft Access > Driver] Too few parameters. Expected 1

2 Answers2