Delete Record

[ianripping]

I have this code: -

I want it to open the first record, then delete that record in the background while it is still being viewed by the user.

This dosnt work, why?

<%
Dim Conn
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")
sql= "SELECT name, comments FROM tblFeeds;"
Rs.Open sql, Conn
rs.movefirst
rs.delete
re.movefirst
Response.Write ("============================================="&"<br>")
Response.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
Response.Write ("<br>")
Response.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
Response.Write ("<br>")
Rs.Close
%>

[bloke]

Originally posted by ianripping

re.movefirst


..perhaps?

[ianripping]

What is this line doing wrong?

In my mind the code does this:-

Open database
Look at table
Look at 1st record in table
Delete record
Look at new 1st record (previously 2nd)
Display Fileds of record
Close Connection

[bloke]

Should it not be

rs.movefirst

or is this just an error in your post, not your code?

[ianripping]

sorry yeah, was an error, still causing problems

[buntine]

Ok, your code may work with a few changes. Though, i think it would be best to simply use SQL's DELETE statement.


'''Your connection string.
sql= "DELETE TOP 1 name, comments FROM tblFeeds"
conn.execute(sql)

sql = "SELECT * FROM tblFeeds"
set Rs = server.createObject("ADODB.recordSet")
Rs.open(sql), conn, 3

Rs.moveFirst
for i = 0 to Rs.recordCount
if not Rs.EOF

with response
.write("============================================="&"<br>")
.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
.Write ("<br>")
.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
.Write ("<br>")
end with

Rs.moveNext
End if
Next

set Rs = nothing
Rs.close


The preceding code will delete the first record from the database and then display the remaining records.

Regards,
Andrew Buntine.

[ianripping]

This is my new code: -

html>
<%
Dim Conn
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")

sql= "DELETE TOP 1 name, comments FROM tblFeeds"
conn.execute(sql)

sql = "SELECT * FROM tblFeeds"
set Rs = server.createObject("ADODB.recordSet")
Rs.open(sql), conn, 3

Rs.moveFirst
for i = 0 to Rs.recordCount
if not Rs.EOF

with response
.write("============================================="&"<br>")
.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
.Write ("<br>")
.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
.Write ("<br>")
end with

Rs.moveNext
End if
Next

set Rs = nothing
Rs.close

Rs.Close
%>

Next<a href = "view.asp">

Doesnt work. Could it be made so that it only looks at the next record, not the remaining?

[buntine]

Ok, what is the error your are getting? and what is the line number?

Also, do you have some sort of auto-incrementing field in your database table?

Regards.

[ianripping]

Yeah I think one field is set to auto number - theres no error, page just doesnt display on server

[buntine]

ok, change this line:

sql= "DELETE TOP 1 name, comments FROM tblFeeds"


to this:

sql= "DELETE TOP 1 name, comments FROM tblFeeds ORDER BY yourAutoNumberField DESC"


Just replace yourAutoNumberField with the name of your autonumber field.

Regards.

[buntine]

Just noticed two things in your code:

- You have created 2 recordSet objects. Just remove one of them

- You have closed the recordSet twice. Remove one of these also.

[ianripping]

Sorry can you tell me which lines I can remove?

[buntine]

Ok, the following line will ask the server to create an instance of an ADO recordSet object. ADO stands for ActiveX Data Objects, a Microsoft technology which is often used along side ASP.

Set Rs = Server.CreateObject("ADODB.Recordset")


You have set this object twice. Just remove one of those statements. Either one will do.

Once you have finished using the object, you should always close it to free up resources on the server. If you dont, the server will have a bunch of pending objects which ultimately will crash the server.
You have done the right thing and closed the object, but you did it twice.
You have the following statement two times in your application. Just remove one of them

Rs.close


Regards,
Andrew Buntine.

[ianripping]

New code: -

<html>
<%
Dim Conn
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")

sql= "DELETE TOP 1 name, comments FROM tblFeeds ORDER BY user_id DESC"
conn.execute(sql)

sql = "SELECT * FROM tblFeeds"
set Rs = server.createObject("ADODB.recordSet")
Rs.open(sql), conn, 3

Rs.moveFirst
for i = 0 to Rs.recordCount
if not Rs.EOF

with response
.write("============================================="&"<br>")
.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
.Write ("<br>")
.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
.Write ("<br>")
end with

Rs.moveNext
End if
Next

set Rs = nothing
Rs.close
%>

Next<a href = "view.asp">

Now I get

Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/ianripping/view.asp, line 18

if not Rs.EOF
-------------^

[buntine]

Ok, line 18 is:

if not Rs.EOF


I forgot the mandatory 'then'. Change it to this:

if not Rs.EOF then


Regards.

[ianripping]

Yeah did that, no error message now but still doesnt work!

[buntine]

Thats strange, your latest code is fine.. There is no errors i can see without testing it locally.

Is it just displaying a blank page? If so, take a look at the HTML source which is output.

Regards,
Andrew Buntine.

[ianripping]

Hi I get an Internal Server Error 500, could you try out the code on another server for me? I have tried on two now.

This is the code again: -

<%
Dim Conn
Dim Rs
Dim sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")

sql= "DELETE TOP 1 name, comments FROM tblFeeds ORDER BY user_id DESC"
conn.execute(sql)

sql = "SELECT * FROM tblFeeds"
set Rs = server.createObject("ADODB.recordSet")
Rs.open(sql), conn, 3

Rs.moveFirst
for i = 0 to Rs.recordCount
if not Rs.EOF then

with response
.write("============================================="&"<br>")
.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
.Write ("<br>")
.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
.Write ("<br>")
end with

Rs.moveNext
End if
Next

set Rs = nothing
Rs.close
%>

Would appreciate any help!

[buntine]

yes, i would be happy to.. Though i will need a copy of your database. Or an explanation of the fields and table names etc.

Regards.

[ianripping]

OK thanks, it can be found at

http://uk.geocities.com/moorblack@btinternet.com/FeedBack.mdb.

I have sent you a private message with my e-mail addy on.

Thanks again!

[buntine]

Ian, sorry for taking so long to look at your code.

I just had a play with it then and got it working. The actual problem was with the SQL query. You cant use the TOP stateent in a DELETE query. I wasnt aware of it, but now we both know.

Heres the code:

<%
Dim Conn, Rs, rs_temp
Dim sql
Dim i

Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb")

Set rs_temp = server.createObject("ADODB.recordSet")
sql = "SELECT TOP 1 * FROM tblFeeds ORDER BY user_id DESC"
rs_temp.open(sql), conn, 3
rs_temp.moveFirst

sql= "DELETE * FROM tblFeeds WHERE user_id = " & rs_temp("user_id")
conn.execute(sql)

rs_temp.close
set rs_temp = nothing

sql = "SELECT * FROM tblFeeds ORDER BY user_id ASC"
set Rs = server.createObject("ADODB.recordSet")
Rs.open(sql), conn, 3

Rs.moveFirst
for i = 0 to Rs.recordCount
if not Rs.EOF then

with response
.write ("============================================="&"<br>")
.Write ("Name: " & "<font color='red'>" & Rs("name") & "</font>")
.Write ("<br>")
.Write ("Comment: " & "<font color='red'>" & Rs("comments") & "</font>")
.Write ("<br>")
end with

Rs.moveNext
End if
Next

Rs.close
set Rs = nothing
%>


Regards,
Andrew Buntine.