problem with update statement
Dear everyone,
i am getting error message while updating...
i will show u the code
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:addpro");
// query statement
// SQLStatement = con.createStatement();
pStmt = con.prepareStatement("UPDATE project SET ename='"
+ ename +"', pro='" + pro +"', mod='" + mod +"', d='"
+ d +"', m='" + m +"', y='" + y
+"', fd='" + fd +"', fm='" + fm +"', fy='" + fy +"', lang='" + lang +"', plat='" + plat +"' WHERE eid=" + eid);
# 1
- What is the error message you get?- I would recommend you use prepared statements rather than building sql up like this. It saves you from sql injection attacks.Also sql code should be in a servlet/bean - not on a jsp page.
# 2
thank u very much for ur reply
i am getting error page...i will show u the code
<%
// edit_res.jsp
// form data
//int id = Integer.parseInt(eid);
String eid = request.getParameter("eid");
String ename = request.getParameter("ename");
String pro = request.getParameter("pro");
String mod = request.getParameter("mod");
String d = request.getParameter("d");
String m = request.getParameter("m");
String y = request.getParameter("y");
String fd = request.getParameter("fd");
String fm = request.getParameter("fm");
String fy = request.getParameter("fy");
String lang = request.getParameter("lang");
String plat = request.getParameter("plat");
int i=0;
Connection con=null;
PreparedStatement prep=null;
//Statement SQLStatement=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:addpro");
prep=con.prepareStatement("insert into project values(?,?,?,?,?,?,?,?,?,?,?,?) ");
prep.setString(1,eid);
prep.setString(2,ename);
prep.setString(3,pro);
prep.setString(4,mod);
prep.setString(5,d);
prep.setString(6,m);
prep.setString(7,y);
prep.setString(8,fd);
prep.setString(9,fm);
prep.setString(10,fy);
prep.setString(11,lang);
prep.setString(12,plat);
i=prep.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
}
if(i!= 0)
{
out.println("Entry successfully updated.");
}
else
{
out.println("Error! Please try again.");
}
// close connection
prep.close();
con.close();
# 3
What is the exact error message ?you have a e.printStackTrace(); in your code....this will print the error in the server console.Please let us know the exact error message for helping you.Thanks
# 4
What is the exact error message ?you have a e.printStackTrace(); in your code....this will print the error in the server console.Please let us know the exact error message for helping you.Thanks
# 5
sorry....i am getting message such that ...the else part MESSAGEif(i!= 0) { out.println("Entry successfully updated."); } else { out.println("Error! Please try again."); }
# 6
Ok no probs...
In your code whereever you have
catch(SQLException e)
{
e.printStackTrace();
}
modify above lines as following
catch(SQLException e)
{
out.println("The Exception "+e.getMessage());
}
Now let me know what it prints.... !
# 7
thanks a lot...the message that i receiced wasThe Exception [Microsoft][ODBC Microsoft Access Driver] You tried to assign the Null value to a variable that is not a Variant data type. Error! Please try again.
# 8
As the error suggest you are trying to insert null into a column which doesnot take null values...I need to look at the structure of the table project in which ur are inserting data.
# 9
i will explain u in a detailed manner.....
i am getting datas thru href link....after that i am updating the contents
the codes in the previous pages are
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body >
<center><h4><u> Employee Assessment Application</u></h4></center>
<%
String pro=null;
String mod=null;
String d=null;
String m=null;
String y=null;
String fd=null;
String fm=null;
String fy=null;
String lang=null;
String eid=null;
String ename=null;
String plat=null;
int i=0;
Connection con=null;
PreparedStatement pr=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:addpro");
pr=con.prepareStatement("select * from project");
ResultSet rs=pr.executeQuery();
%>
<table width="484" border="1" align="center">
<tr>
<td width="95">Programmer ID </td>
<td width="95">Programmer Name </td>
<td width="95">Project Name </td>
<td width="95">Module </td>
<td width="95">Start Date </td>
<td width="95">Finish Date </td>
<td width="95">Language</td>
<td width="95">Platform</td>
<td width="95">edit</td>
<td width="95">Delete</td>
</tr>
<%
while (rs.next ())
{
pro = rs.getString("pro");
mod = rs.getString("mod");
d = rs.getString("d");
m = rs.getString("m");
y = rs.getString("y");
fd = rs.getString("fd");
fm = rs.getString("fm");
fy = rs.getString("fy");
lang = rs.getString("lang");
plat = rs.getString("plat");
eid = rs.getString("eid");
ename = rs.getString("ename");
%>
<tr>
<td><%=eid%></td>
<td><%=ename%></td>
<td><%=pro%></td>
<td><%=mod%></td>
<td><%=d+"-"+m+"-"+y+""%></td>
<td><%=fd+"-"+fm+"-"+fy+""%></td>
<td><%=lang%></td>
<td><%=plat%></td>
<td><a href='trail5.jsp?id=<%=eid%>'>edit</a>
<td><a href='delp.jsp?id=<%=eid%>'>del</a>
</td>
</tr>
<%
}
%>
</table>
<div align="center"></div>
<%
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
pr.close();
con.close();
}
%>
<li><a href="hrloginform.jsp"><h4><u>Back</h4></a>
</body>
</html>
# 10
the structure of table is that every column have been in text format...
# 11
[Microsoft][ODBC Microsoft Access Driver] You tried to assign the Null value to a variable that is not a Variant data type. Error! Please try again.
Your update statement is failing because one of the values you are passing is null.
As a suggestion, print out all the values you are retrieving.
eg
eid = <%= eid %>
ename = <%= ename %>
One or more of them will probably be null.
Should they be null?
# 12
thanks a lot for every one who helped me...at last it worked out.....hats off to all of you friends....
