Having problem with prepared statement

[vanan saravanan]

This is what i'm trying to acheive. Each result which appears in the table will have a delte link. When i click on it i need it to delete that particular record from the database. I get the following error. What might be wrong. Thank you for your help.

javax.servlet.ServletException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1



<%

while (rst2.next()){ %>
<tr>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Status") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Leave_Type") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Date_From") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Date_to") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Duration") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><a href="Deleterecord.jsp"?datedel=<%= rst2.getString("Date_From") %>>Delete</a></td>
</tr>
<tr> </tr>
<%}
%>



String query = "DELETE FROM tb_leavemc where Date_From =?";
java.sql.PreparedStatement statement = con.prepareStatement(query);
statement.setString(1,(request.getParameter("datedel")));
stm.executeUpdate(query);

[Shailesh Chandra]

Originally posted by vanan saravanan:

String query = "DELETE FROM tb_leavemc where Date_From =?";
java.sql.PreparedStatement statement = con.prepareStatement(query);
statement.setString(1,(request.getParameter("datedel")));
stm.executeUpdate(query);

stm.executeUpdate(query);
Change this line to

statement.executeUpdate();


Shailesh

[vanan saravanan]

Thank you the error has gone off. But the script does not delete and does nothing.


String query = "DELETE FROM tb_leavemc where Date_From =?";
java.sql.PreparedStatement statement = con.prepareStatement(query);
statement.setString(1,(request.getParameter("datedel")));
statement.executeUpdate();




<%

while (rst2.next()){ %>
<tr>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Status") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Leave_Type") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Date_From") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Date_to") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><%= rst2.getString("Duration") %></td>
<td rowspan="2" align=center bgcolor="#FFFFFF"><a href="Deleterecord.jsp"?datedel=<%= rst2.getString("Date_From") %>>Delete</a></td>
</tr>
<tr> </tr>
<%}
%>

[Naseem Khan]

After the change which you have made as pointed out by Shailesh

1. When you click the link, does it take you to the Deleterecord.jsp page?
Can you post here the url after clicking the link.?

2. If yes, check what request.getParameter("datedel") is returning. null or your required String?

3. After updating the database, how you come to your fisrt jsp from Deleterecored.jsp page?


Regards


Naseem
[ July 03, 2006: Message edited by: Naseem Khan ]

[vanan saravanan]

1) the page i am at is : http://localhost:8080/fch1/viewleave1.jsp

After clicking the link : http://localhost:8080/fch1/Deleterecord.jsp


2) How do i check?


3) i check the database to see if the changes have taken place.

[Naseem Khan]

After clicking the link : http://localhost:8080/fch1/Deleterecord.jsp


2) How do i check?

Oh...Values are not passed by query string...

Change your <a href> tag to this...

<%
  String value=rst2.getString("Date_From");
%>
<a href="Deleterecord.jsp?datedel=<%=value%>">Delete</a>

With this, your url will change to on clicking delete....

http://localhost:8080/fch1/Deleterecord.jsp?datedel=***somevalue***

where ***somevalue*** is the value of rst2.getString("Date_From")
----------------------------------------------------------------

Do one more thing, in Deleterecord.jsp page, check the value of datedel field by scriptlet like this...

<%
String value=request.getParameter("datedel");

out.println("Value coming from query String is: " + value); What value its printing, is it null?

%>

Naseem

[vanan saravanan]

THank you for your help. It does not delete but

<%
String value=request.getParameter("datedel");

out.println("Value coming from query String is: " + value); What value its printing, is it null?

%>

Value coming from query String is: 30-07-2006

http://localhost:8080/fch1/Deleterecord.jsp?datedel=30-07-2006

[vanan saravanan]

Sorry there was a typo mistake in the variable on my side. Thank you very much for your help and patience.