Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Record from RecordSet 3

Status
Not open for further replies.

Slippenos

MIS
Apr 22, 2005
333
US
I'm trying to delete a record ..

delete.asp:
Code:
<% Response.Buffer = True %>

<!-- #include virtual="/<mynet>adovbs.inc" -->
<!-- #include virtual="/<mynet>db.asp" -->

<%

	Set Conn = Server.CreateObject( "ADODB.Connection" )
	Conn.Open "MyDSN"

	MySQL = "SELECT * FROM MyTable"

	Set Rs = Server.CreateObject( "ADODB.Recordset" )
	Rs.Open MySQL, Conn, adOpenStatic, adLockOptimistic

	Rs.Delete([adAffectCurrent])
	
	Rs.Close
	Set Rs = Nothing

%>

Here is my html:
Code:
<html>
<head>
<meta http-equiv = "refresh" content="3.0; URL = [URL unfurl="true"]http://<myweb>/edit.asp">[/URL]
<title>Inventory Record Change Success</title>
</head>
<body>

<%
	
Response.Write "<h3><b>" & "The current record has just been removed from the RecordSet" & "</b></h3>"
	
%>

</body>
</html>
I get this message telling me it was successfully deleted. But when I check my db .. the record is still there. I trigger this page with a button:
Code:
<input type = "button" name = "delete" value = "DELETE THIS RECORD" onclick = "javascript:window.location='[URL unfurl="true"]http://<myweb>delete.asp'">[/URL]

I think the problem is my code doesn't know which Rs to delete. I may add a filter, but I dont know how to pass a value along with a button click

Any thoughts?



[red]Tools | Internet Options | Clear History[/red]
 
Maybe you could change it up so that the key field is submitted to the delete.asp page.

Then instead of selecting the entire table into a recordset you could just execute something like:
DELETE FROM MyTable WHERE KeyField = SubmittedValue
 
Use the DELETE SQL Command instead of SELECT - and use the WHERE clause to identify which record to delete....

sSQL = "DELETE FROM MyTable WHERE MyUniqueIDField = " & myUniqueRecordID

oRS.Execute(sSQL)

You can get the myUniqueRecordID by passing it in the querystring..
(from your code:)
<input type = "button" name = "delete" value = "DELETE THIS RECORD" onclick = "javascript:window.location='
Then using:

myUniqueRecordID = replace(trim(request.querystring("id")),"'","'')


Exmaple:


A smile is worth a thousand kind words. So smile, it's easy! :)
 
...I reckon Sheco and DNG copied my answer.... [thumbsup2]

A smile is worth a thousand kind words. So smile, it's easy! :)
 
Good news it worked ..

To break it down:

[red]details.asp:[/red]
Code:
<input type = "button" name = "delete" value = "DELETE THIS RECORD" onclick = "javascript:window.location='[URL unfurl="true"]http://<myweb>delete.asp?id=<%=objRS("SubmitId")%>'">[/URL]
Code:
<input type = "hidden" name="key" value="<%= Request("ID")%>">
</form>

[red]delete.asp:[/red]
Code:
<%

	Dim objRS, strQ
	strQ = "SELECT * FROM MyTable WHERE SubmitId = " & Request("ID")

	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open strQ, Conn, 3, 1
	
%>
Code:
<form method = "post" action = "confirm.asp">
...
<input type = "submit" value = "Confirm">
Code:
<%


	objRS.Close
	Conn.Close
	Set objRS = Nothing
	Set Conn = nothing

%>
<input type = "hidden" name="key" value="<%= Request("ID")%>">
</form>

[red]confirm.asp:[/red]
Code:
<% Response.Buffer = True %>

<!-- #include virtual="<myNet>/db.asp" -->
<!-- #include virtual="<myNet>/adovbs.inc" -->

<%

    Set Conn = Server.CreateObject( "ADODB.Connection" )
    Conn.Open "MyDSN"

    MySQL = "DELETE FROM MyTable WHERE SubmitId = " & Request("key")  

    Set Rs = Server.CreateObject( "ADODB.Recordset" )
    Rs.Open MySQL, Conn

%>

... then misc html

My only qualm is that I am so accustomed to closing my Rs and setting it equal to Nothing. I received errors when this was in my code and the error went away when I took them out.

Is that right?

Thanks all for the help.

[red]Tools | Internet Options | Clear History[/red]
 
I am so accustomed to closing my Rs and setting it equal to Nothing

thats a very good practice, so dont stop doing it...but just put these lines in the correct place..you dont want to close the recordset while you are processing it..do you?

glad everything worked for you [thumbsup2]

-DNG
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top