Hello, I'm trying to insert values from a form to a table (employee) on my database using prepared statements. From what I can tell the connections are fine, am able to query the database (as shown in the code to check the user type of the current logged in user). My code follows all the tutorials ive read on many sites including posts on here. Yet I keep getting this error - "The method setInt(int, String) is undefined for the type Statement", also error occurs for setString. Any help with this would be much appreciated.
JSP Connection Code
<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();
Connection con=null;
Connection con1=null;
Connection con2=null;
ResultSet rst=null;
ResultSet rst1=null;
Statement stmt=null;
Statement stmt1=null;
Statement stmt2=null;
try{
String url="jdbc:mysql://localhost:3306/hr_data";
String user_id = user.getUsername();
session.putValue("user_id",user_id);
String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
String uname = request.getParameter("username");
String pword = request.getParameter("password");
String uemail = request.getParameter("email");
String qual = request.getParameter("qual");
String dob = request.getParameter("dob");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String tax = request.getParameter("tax");
String tel = request.getParameter("tel");
String addr = request.getParameter("address");
String posc = request.getParameter("post");
String trvl = request.getParameter("trvl");
String sal = request.getParameter("sal");
String ins = request.getParameter("ins");
String utype = request.getParameter("utype");
String dep = request.getParameter("dep");
String add_emp= "insert into employee (employee_id, username, password, first_name, last_name, email, qualification, dob, age, sex, insurance, travel_method, salary, tax, user_type, phone_number, address, postcode, department_department_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
int updateQuery=0;
con=DriverManager.getConnection(url, "tahirs2", "samir");
con1=DriverManager.getConnection(url, "tahirs2", "samir");
stmt=con.createStatement();
stmt1=con1.createStatement();
rst1=stmt1.executeQuery("select employee.user_type from employee where username='"+user_id+"'");
rst=stmt.executeQuery("select * from employee where employee.employee_id = (select max(employee_id) from employee)");
if (rst1.next()&(rst1.getString(1).equals("admin"))){
while(rst.next()){
int num_id= Integer.parseInt((rst.getString(1))) + 1;
String new_id=Integer.toString(num_id);
if(fname!=null && lname!=null && uname!=null&& pword!=null&& uemail!=null&& qual!=null&& dob!=null&& age!=null&& sex!=null&& tax!=null&& tel!=null&& addr!=null&& posc!=null&& trvl!=null&& sal!=null&& ins!=null&& utype!=null&& dep!=null){
// check if the text box having only blank spaces
if(fname!="" && lname!="" && uname!=""&& pword!=""&& uemail!=""&& qual!=""&& dob!=""&& age!=""&& sex!=""&& tax!=""&& tel!=""&& addr!=""&& posc!=""&& trvl!=""&& sal!=""&& ins!=""&& utype!=""&& dep!=""){
con2=DriverManager.getConnection(url, "tahirs2", "samir");
stmt2=con2.prepareStatement(add_emp);
stmt2.setInt(1, new_id);
stmt2.setString(2, uname);
stmt2.setString(3, pword);
stmt2.setString(4, fname);
stmt2.setString(5, lname);
stmt2.setString(6, uemail);
stmt2.setString(7, qual);
stmt2.setString(8, dob);
stmt2.setString(9, age);
stmt2.setString(10, sex);
stmt2.setString(11, ins);
stmt2.setString(12, trvl);
stmt2.setString(13, sal);
stmt2.setString(14, tax);
stmt2.setString(15, utype);
stmt2.setString(16, tel);
stmt2.setString(17, addr);
stmt2.setString(18, posc);
stmt2.setString(19, dep);
updateQuery = stmt2.executeUpdate();
if (updateQuery != 0) {
%>
<br>
<table style="background-color: #E3E4FA;" width="30%" border="1">
<tr><th>Data is inserted successfully in database.</th></tr>
</table>
<%
}}}
else{
out.println("Make sure all fields are filled in correctly");
}
else
{
out.println("Make sure all fields are not empty");
}
%>
<tr>
<td bgColor="#CCFF99" vAlign="top" width="100" align="center" height="19"><%=rst.getString(1)%>.</td>
</tr>
<%
}}
else
{
out.println("Invalid user privileges, must be administrator");
}
rst.close();
rst1.close();
stmt.close();
stmt1.close();
stmt2.close();
con.close();
con1.close();
con2.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>
Full Code:
<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="java.io.*" %>
<jsp:useBean id="user" class="user.UserData" scope="session"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/2002/REC-xhtml1-20020801/DTD/xhtml1-transitional.dtd"> -->
<!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/2002/REC-xhtml1-20020801/DTD/xhtml1-strict.dtd"> -->
<!-- <!doctype html public "-//W3C//DTD HTML 4.01//EN"> -->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="generator" content="HTML Tidy for Linux (vers 1 September 2005), see www.w3.org" />
<title>HRMS: Add Employee</title>
<link rel="stylesheet" type="text/css" media="screen" href="style.css" />
<style type="text/css">
/*<![CDATA[*/
span.c1 {color:#000;}
/*]]>*/
</style>
</head>
<body>
<div id="container">
<div id="header">
<div id="userbox">
</div>
<h1><strong>Human Resource Management System<span class="c1">.</span></strong></h1>
<ul id="nav">
<li><a href="index.html" title="Home" class="on">Add</a></li>
<li><a href="index2.htm" title="Page 2">Edit</a></li>
<li><a href="#" title="Contact">Delete</a></li>
</ul>
</div>
<div id="sidebar">
<div>
<h3 title="Sidebar">Reports</h3>
<ul>
<li<a href="http://localhost:8080/admin_details_hrms.jsp">
Employee details report</a></li>
<li<a href="http://localhost:8080/admin_training_hrms.jsp">
Training report<a /></li>
<li<a href="http://localhost:8080/admin_skill_hrms.jsp">
Skill report<a /></li>
<li<a href="http://localhost:8080/admin_proj_hrms.jsp">
Project report<a /></li>
<li<a href="http://localhost:8080/admin_job_hrms.jsp">
Job report<a /></li>
<li<a href="http://localhost:8080/admin_dep_hrms.jsp">
Placement report</a></li>
<li<a href="http://localhost:8080/admin_leave_hrms.jsp">
Leave report</a></li>
<li<a href="http://localhost:8080/admin_appraisal_hrms.jsp">
Appraisal report</a></li>
</ul>
<h3 title="Lorem Ipsum">Admin</h3>
<ul>
<li><a href="http://localhost:8080/admin_add_hrms.jsp">Account tools</a></li>
<li<a href="http://localhost:8080/style_logout_hrms.jsp">
Logout</a></li>
</ul>
</div>
</div>
<div id="content">
<h2>Add employee</h2>
<p>Enter the new employee's details in the fields specified below, note all entries must not exceed the limits given.</p>
<p>The username field must be unique, i.e the last name followed by the initial of their first name followed by a number, for instance, "tahirs1".</p>
<div>
</div>
<form>
<table>
<tr>
<td>First name:</td>
<td><input type="text" size= "15" name="fname" /></td>
</tr>
<tr>
<td>Last name:</td>
<td><input type="text" size= "15" name="lname" />
</td>
</tr>
<tr>
<td>Username:</td>
<td><input type="text" size= "15" name="username" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" size= "15" name="password" /></td>
</tr>
<tr>
<td>Email:</td>
<td><input type="text" size= "15" name="email" /></td>
</tr>
<tr>
<td>Qulification:</td>
<td><input type="text" size= "15" name="qual" /></td>
</tr>
<tr>
<td>Date of birth:</td>
<td><input type="text" size= "15" name="dob" /></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" size= "15" name="age" /></td>
</tr>
<tr>
<td>Sex:</td>
<td><input type="text" size= "15" name="sex" /></td>
</tr>
<tr>
<td>Insurance:</td>
<td><input type="text" size= "15" name="ins" /></td>
</tr>
<tr>
<td>Salary:</td>
<td><input type="text" size= "15" name="sal" /></td>
</tr>
<tr>
<td>Travel method:</td>
<td><input type="text" size= "15" name="trvl" /></td>
</tr>
<tr>
<td>User type</td>
<td><select name= utype>
<option value="user">User</option>
<option value="admin">Admin</option>
</select></td>
</tr>
<tr>
<td>Tax code:</td>
<td><input type="text" size= "15" name="tax" /></td>
</tr>
<tr>
<td>Telephone:</td>
<td><input type="text" size= "15" name="tel" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" size= "15" name="address" /></td>
</tr>
<tr>
<td>Post code:</td>
<td><input type="text" size= "15" name="post" /></td>
</tr>
<tr>
<td>Department:</td>
<td><select name= dep>
<option value="0">Requirements</option>
<option value="1">Analysis</option>
<option value="2">Design</option>
<option value="3">Implementation</option>
<option value="4">Human resources</option>
</select></td>
<td><input type="submit" value="Create" /></td>
</tr>
</table>
</form>
<div>
</div>
<table border="1" borderColor="#ffe9bf" cellPadding="0" cellSpacing="0" width="800" height="63">
<tbody>
<td bgColor="#006699" width="100" align="center" height="19"><font color="#ffffff"><b>Target</b></font></td>
<td bgColor="#006699" width="47" height="19"><font color="#ffffff"><b>Start date</b></font></td>
<td bgColor="#006699" width="270" height="19"><font color="#ffffff"><b>Review</b></font></td>
<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();
Connection con=null;
Connection con1=null;
Connection con2=null;
ResultSet rst=null;
ResultSet rst1=null;
Statement stmt=null;
Statement stmt1=null;
Statement stmt2=null;
try{
String url="jdbc:mysql://localhost:3306/hr_data";
String user_id = user.getUsername();
session.putValue("user_id",user_id);
String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
String uname = request.getParameter("username");
String pword = request.getParameter("password");
String uemail = request.getParameter("email");
String qual = request.getParameter("qual");
String dob = request.getParameter("dob");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String tax = request.getParameter("tax");
String tel = request.getParameter("tel");
String addr = request.getParameter("address");
String posc = request.getParameter("post");
String trvl = request.getParameter("trvl");
String sal = request.getParameter("sal");
String ins = request.getParameter("ins");
String utype = request.getParameter("utype");
String dep = request.getParameter("dep");
String add_emp= "insert into employee (employee_id, username, password, first_name, last_name, email, qualification, dob, age, sex, insurance, travel_method, salary, tax, user_type, phone_number, address, postcode, department_department_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
int updateQuery=0;
con=DriverManager.getConnection(url, "tahirs2", "samir");
con1=DriverManager.getConnection(url, "tahirs2", "samir");
stmt=con.createStatement();
stmt1=con1.createStatement();
rst1=stmt1.executeQuery("select employee.user_type from employee where username='"+user_id+"'");
rst=stmt.executeQuery("select * from employee where employee.employee_id = (select max(employee_id) from employee)");
if (rst1.next()&(rst1.getString(1).equals("admin"))){
while(rst.next()){
int num_id= Integer.parseInt((rst.getString(1))) + 1;
String new_id=Integer.toString(num_id);
if(fname!=null && lname!=null && uname!=null&& pword!=null&& uemail!=null&& qual!=null&& dob!=null&& age!=null&& sex!=null&& tax!=null&& tel!=null&& addr!=null&& posc!=null&& trvl!=null&& sal!=null&& ins!=null&& utype!=null&& dep!=null){
// check if the text box having only blank spaces
if(fname!="" && lname!="" && uname!=""&& pword!=""&& uemail!=""&& qual!=""&& dob!=""&& age!=""&& sex!=""&& tax!=""&& tel!=""&& addr!=""&& posc!=""&& trvl!=""&& sal!=""&& ins!=""&& utype!=""&& dep!=""){
con2=DriverManager.getConnection(url, "tahirs2", "samir");
stmt2=con2.prepareStatement(add_emp);
stmt2.setInt(1, new_id);
stmt2.setString(2, uname);
stmt2.setString(3, pword);
stmt2.setString(4, fname);
stmt2.setString(5, lname);
stmt2.setString(6, uemail);
stmt2.setString(7, qual);
stmt2.setString(8, dob);
stmt2.setString(9, age);
stmt2.setString(10, sex);
stmt2.setString(11, ins);
stmt2.setString(12, trvl);
stmt2.setString(13, sal);
stmt2.setString(14, tax);
stmt2.setString(15, utype);
stmt2.setString(16, tel);
stmt2.setString(17, addr);
stmt2.setString(18, posc);
stmt2.setString(19, dep);
updateQuery = stmt2.executeUpdate();
if (updateQuery != 0) {
%>
<br>
<table style="background-color: #E3E4FA;" width="30%" border="1">
<tr><th>Data is inserted successfully in database.</th></tr>
</table>
<%
}}}
else{
out.println("Make sure all fields are filled in correctly");
}
else
{
out.println("Make sure all fields are not empty");
}
%>
<tr>
<td bgColor="#CCFF99" vAlign="top" width="100" align="center" height="19"><%=rst.getString(1)%>.</td>
</tr>
<%
}}
else
{
out.println("Invalid user privileges, must be administrator");
}
rst.close();
rst1.close();
stmt.close();
stmt1.close();
stmt2.close();
con.close();
con1.close();
con2.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>
</tbody>
</table>
</div>
<div id="footer">
Copyright © All Rights Reserved.
</div>
</body>
</html>