Problem deleting multiple rows - Nearly there, PLEASE HELP

[jasone]

Hi all,

im nearly there with this one and im sure it shouldnt be hard to solve, i just cant seem to find the solution.

ive got records being displayed, the user can then tick what records to delete... click delete.. it shows deleted records then you can go back to the screen, It works if one record is selected but any more it doesnt delete any so im sure there is a problem with my SQL statement.

the code for the 2 pages have been shown below:

The code for the selection page
=============== ========

      <div align="center">
        <%
If rssearchcnx.BOF and rssearchcnx.EOF Then%> 
    We appolagise, but no flights were found matching your search
      </div>
      <p align="center"><a href="search.asp" target="mainFrame">please try again</a></p>
<%Else%>

<%If Not rssearchcnx.BOF Then%>
<%
	Do While Not rssearchcnx.EOF
	%>
<tr>
        <td ><%=rssearchcnx("flight_id")%></td>
        <td></td>
        <td>
        <form name="form" method="post" action="delete.asp">
        <input type="checkbox" name="flight_id" value="<%=(rssearchcnx.Fields.Item("flight_id").Value)%>"></td>

      </tr>
    <% rssearchcnx.MoveNext
	Loop
	%>

</table>

<%End If%>
<%End If%>
    <input type="submit" value="Delete Selected Records">
</form>
<%
rssearchcnx.Close
searchcnx.Close
%>

code for the delete action page
=============== =======

<%

Dim flight_id

flight_id = Request.Form("flight_id")


SQL="DELETE * FROM tbl_flight_details WHERE flight_id ='"+Request.Form("flight_id")+"'"

 Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\ddwassignment3\airlines.mdb"

Set MyRS=Server.CreateObject("ADODB.RecordSet")
MyRS.Open SQL,MyConn

%>

</p>
      <table width="37%" border="0" align="center">
        <tr>
<td align="center"><% Response.Write("<font face=Calibri>You have deleted flight number <B><font face=Calibri color=Red>" & flight_id)%>

any help will be MUCH appreciated

kind regards

jason

[jeffstl]

You have to execute the DELETE SQL in a loop (more then once) to delete multiple records.

You will need a check that gathers all the id's checked by the user into an array.

The you will need to loop through that array and run the SQL delete statement for EACH id in the array.

With this current set up I am not sure which direction to tell you. I would probably instead of letting them do check boxes and submitting a whole bunch to process, you could set it up where they can delete one at a time quicker by redirecting them to the page displaying the records.

Print this link out next to each record:

'FORM PAGE
response.write <a href=delete.asp?id=" & rssearchcnx.Fields.Item("flight_id").Value & ">Delete</a>"

'THEN ON YOUR DELETE.ASP
'take the id from the querystring instead
<%
 
Dim flight_id
 
flight_id = Request.Querystring("id")
 
 
SQL="DELETE * FROM tbl_flight_details WHERE flight_id ='"+Request.Form("flight_id")+"'"
 
 Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\ddwassignment3\airlines.  mdb"
 
'execute sql string!!
MyConn.execute SQL

'then just redirect back to your page listing all the records and delete links!
response.redirect("formpage.asp")

'dont really need to create a record set to execute a DELETE, only when ur 'SELECTING records I think
'Set MyRS=Server.CreateObject("ADODB.RecordSet")
'MyRS.Open SQL,MyConn
 
%>

[jasone]

Hi thanks for your response, a little unsure where to put this data, is it possible for you to insert the script into the script i have already posted, and then post it back on here?

many many thanks

jason

[jasone]

i forgot to say im using checkboxes.. so there is no need for the delete text next to each record

[jeffstl]

All I can say like stated before is that in order to use checkboxes and process many deletes at once, you will need to set up and pass an array of "id" numbers to loop through.

Im not sure how many records you are dealing with when you print out the area for them to select the checkboxes, so Im not sure what the best solution is, Its not simple to determine how many checkboxes are checked on a page when there could be 5 or there could be a 1000.

Just off hand on your delete.asp page you will need something like this where MyCheckBox is your array of checkboxes:

Again to avoid all this, you could use my example where there is a delete link next to each record and they click it and it goes right back to the page they were just viewing with the record deleted.

for FlightID=0 to MyRS.RecordCount
     if MyCheckBox(FlightID).Value = 1 then
          'execute delete SQL here using FlightID
     end if

Next

[jhardman]

Hey Jasone,

If Jeff's instructions were a jump for you, try a multi-step approach like this.

1- as it is you are opening up a new form for each row, only one form is going to be sent to the next page, so make sure the <form> tag is before the loop,

2- open up the data posted from your form to make sure it is in a good format:
[code=asp]for each x in request.form
response.write x & ": " & request.form(x) & "<br>" & vbNewLine
next[/code]If all of the flight_id numbers had the same input name (name="flight_i d") then you should get a list in response like this:

flight_id: 23, 75, 63, 192

Make sure these are all the records you want to delete before you go on.

3- Open the db and SELECT these records. Make sure you are getting the right records. I actually recommend you put up a confirmation dialogue before you delete, but regardless, I would open with a SELECT statement first.[code=asp]query = "SELECT * FROM tbl_flight_deta ils WHERE flight_id IN " & Request.Form("f light_id")[/code]Notice the "IN" keyword allows you to select values from a list, otherwise, you are saying you want the flight_id to equal 23,75,63,192 and there is no flight id that equals that. Look at the list this pulls up to make sure it is pullingup the right records (as I mentioned before, I usually leave this step in the final version as a confirmation "Are you sure you want to delete these recdords?") [code=asp]response.write "<table><tr >"
for each x in objRS.fields
response.write "<th>" & x.name & "</th>"
next
response.write "</tr>" & vbNewLine

do until objRS.eof
response.write "<tr>"

for each x in objRS.fields
response.write "<td>" & x.value & "</td>"
next

response.write "</tr>"
objRS.moveNext
loop

Response.write "</table>"
[/code]

4- if this looks good, then to delete you just need to change the word "SELECT" to "DELETE" and it will go.

Let me know if this helps.

Jared