File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Having problem with prepared statement

 
vanan saravanan
Ranch Hand
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 809
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 809
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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...



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
Ranch Hand
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry there was a typo mistake in the variable on my side. Thank you very much for your help and patience.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic