Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: Invalid column index

 
Deependra Singh
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
viewLabour.jsp:
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="db.DBConnection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<script type="text/javascript">
function editRecord(){
window.location = 'editLabour.jsp';
}
function deleteRecord(){
window.location = 'deleteLabour.jsp';
}
</script>
</head>
<body>
<%
String idproofno = request.getParameter("idproofno");

String query = "SELECT * FROM LABOUR WHERE IDPROOFNO= '" + idproofno + "'";

DBConnection dbc = new DBConnection();
Connection con = dbc.getDBConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
%>
<form name="f" action="editLabour.jsp" method="post">
<table align="center" cellpadding="5" cellspacing="5" border="1">
<tr bgcolor="#A52A2A">
<td><b>Full Name</b></td><td><input type="text" name="fullname" value="<%=rs.getString("fullname")%>" disabled/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>Age</b></td><td><input type="text" name="age" value="<%=rs.getString("age")%>" disabled/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>Gender</b></td><td><input type="text" name="gender" value="<%=rs.getString("gender")%>" disabled/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>DOB</b></td><td><input type="text" name="dob" value="<%=rs.getString("dob")%>" disabled/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>Mobile No</b></td><td><input type="text" name="mobileno" value="<%=rs.getString("mobileno")%>"/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>ID Type</b></td><td><input type="text" name="idtype" value="<%=rs.getString("idtype")%>"/></td>
</tr>
<tr bgcolor="#A52A2A">
<td><b>ID Proof No</b></td><td><input type="text" name="idproofno" value="<%=rs.getString("idproofno")%>"/></td>
<tr bgcolor="#A52A2A">
<td><b>Address</b></td><td><input type="text" name="address" value="<%=rs.getString("address")%>"/></td>
</tr>
<tr bgcolor="#A52A2A">
<td colspan="2">
<center>
<a href="editLabour.jsp?mobileno=<%=rs.getString("mobileno")%>&idtype=<%=rs.getString("idtype")%>&idproofno=<%=rs.getString("idproofno")%>&address=<%=rs.getString("address")%>" onclick="return editRecord();"><b>Edit</b></a>
<a href="deleteLabour.jsp?idproofno=<%=rs.getString("idproofno")%>" onclick="return deleteRecord();"><b>Delete</b></a>
</center>
</td>
</tr>
</table>
</form>
<%
}
%>
</body>
</html>

editLabour.jsp
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="db.DBConnection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%

String mobileno = request.getParameter("mobileno");
String idtype = request.getParameter("idtype");
String idproofno = request.getParameter("idproofno");
String address = request.getParameter("address");

try {
DBConnection dbc = new DBConnection();
Connection con = dbc.getDBConnection();

String query = "UPDATE LABOUR SET mobileno =?, idtype =?, idproofno =?, address =?" + " WHERE idproofno = ?";

PreparedStatement st = con.prepareStatement(query);
st.setString(5, mobileno);
st.setString(6, idtype);
st.setString(7, idproofno);
st.setString(8, address);

int rowsEdited = st.executeUpdate();
if (rowsEdited > 0) {
getServletContext().getRequestDispatcher("/allLabour.jsp").include(request, response);
} else {
getServletContext().getRequestDispatcher("/Error.jsp").include(request, response);
}
} catch (SQLException e) {
out.println(e);
}

%>

Labour Tabledatatype of all columns varchar2)
fullname
age
gender
dob
mobileno
idtype
idproofno
address


Upon clicking Edit button, it shows error: java.sql.SQLException: Invalid column index
Please help me to find error!
 
Tayyeb Ahmad
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

String query = "UPDATE LABOUR SET mobileno =?, idtype =?, address =? WHERE idproofno = ?";

PreparedStatement st = con.prepareStatement(query);
st.setString(1, mobileno);
st.setString(2, idtype);
st.setString(3, address);
st.setString(4, idproofno);

int rowsEdited = st.executeUpdate();

 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For starters, don't do this in a JSP.
JSP's are for displaying data, not processing it or gathering it.
This would also allow you to log the stack trace for the exception as well, with a proper line number for the calling class rather than the generated JSP class.

But, I guess this is some homework thing, where they put silly demands on how to do it...


Your prepared statement has 5 placeholders.
You are trying to assign things to placeholders 6 through 8 (and nothing for the first 4), so that's where your error is coming from.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic