aspose file tools*
The moose likes JDBC and the fly likes Having problem with prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Having problem with prepared statement" Watch "Having problem with prepared statement" New topic
Author

Having problem with prepared statement

vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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

Joined: Aug 13, 2004
Posts: 1081

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


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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

Joined: Apr 25, 2005
Posts: 809
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 ]

Asking Smart Questions FAQ - How To Put Your Code In Code Tags
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
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

Joined: Apr 25, 2005
Posts: 809

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

Joined: Jun 02, 2006
Posts: 95
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

Joined: Jun 02, 2006
Posts: 95
Sorry there was a typo mistake in the variable on my side. Thank you very much for your help and patience.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Having problem with prepared statement
 
Similar Threads
Alighment Problem in a Table
Html to database
Help me about html:select
jsp:include and c:import combination/nesting
inserting data to a table