i think problem lies with SQL statement, please view and assist

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

        <%
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><form name="update" method="post" action="flight_update.asp"><input type="hidden" value="<%=(rssearchcnx.Fields.Item("flight_id").Value)%>" name="flight_id" /><input type="submit" value="update" /></form></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)%></font></B></td>

any help will be MUCH appreciated

kind regards

jason

[amitpatel66]

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

        <%
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><form name="update" method="post" action="flight_update.asp"><input type="hidden" value="<%=(rssearchcnx.Fields.Item("flight_id").Value)%>" name="flight_id" /><input type="submit" value="update" /></form></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)%></font></B></td>

any help will be MUCH appreciated

kind regards

jason

Does the below line stores all the values selected from front end and in what format? either comma delimited or something else??
flight_id = Request.Form("f light_id")

[ronverdonk]

When I look at the logic of the script I see that, within each loop, a form with checkbox is created but only after the loop a SUBMIT is created. So hitting the SUBMIT will only submit one form with one checkbox.

Ronald

[jasone]

Does the below line stores all the values selected from front end and in what format? either comma delimited or something else??
flight_id = Request.Form("f light_id")

Hi, im unsure of this, is there anyway to check

[amitpatel66]

Hi, im unsure of this, is there anyway to check

Try printing your variable value in the front end (IE) using PHP after assigning a value to it.
And post back here for reference

[jasone]

Try printing your variable value in the front end (IE) using PHP after assigning a value to it.
And post back here for reference

Hey, i finally go the values selected to be printed out in the variable "flight_id" and it shows all the ones selected, seperated with a comma.

whats the next step to get this working? :-S

[jasone]

i have changed the form code slightly to the one shown below :

<form name="delete" method="GEt" action="delete.asp?flight_id=<%=(rssearchcnx.Fields.Item("flight_id").Value)%>" >

[jasone]

so im guessing i just need to write a loop now to delete each one... any ideas on how to wrote this... anyone?

[amitpatel66]

so im guessing i just need to write a loop now to delete each one... any ideas on how to wrote this... anyone?

No LOOP is not required. You can try something like this:

[code=sql]

SQL="DELETE * FROM tbl_flight_deta ils WHERE flight_id IN ("+Request.Form ("flight_id")+" )"

[/code]