Delete Record from RecordSet 3

Slippenos · Jul 27, 2005

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]

Sheco · Jul 27, 2005

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

DotNetGnat · Jul 27, 2005

and you could send it using something like this:

"javascript:window.location='

http://<myweb>delete.asp?KeyField=keyvalue'"

and you can retrieve this using Query.String("KeyField")

-DNG

damber · Jul 27, 2005

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='

http://<myweb>delete.asp?id=123'">

Then using:

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

Exmaple:

http://www.w3schools.com/ado/ado_delete.asp

A smile is worth a thousand kind words. So smile, it's easy!

Sheco · Jul 27, 2005

wow, thats spooky!

damber · Jul 27, 2005

...I reckon Sheco and DNG copied my answer.... [thumbsup2]

A smile is worth a thousand kind words. So smile, it's easy!

Slippenos · Jul 27, 2005

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]

DotNetGnat · Jul 27, 2005

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

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!

Delete Record from RecordSet 3

Slippenos

MIS

Sheco

Programmer

DotNetGnat

Programmer

damber

Programmer

Sheco

Programmer

damber

Programmer

Slippenos

MIS

DotNetGnat

Programmer

Similar threads

Part and Inventory Search

Sponsor