Using Checkboxes to delete multiple records

[stevem2004]

Hi,

I have an access DB and currently use the following code to select records to delete


strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("database.mdb")
set conn = server.createobject("adodb.connection")
conn.open strconn
strSQL = "SELECT * FROM table WHERE status = 'I' ORDER BY id ASC;"
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 3, 3
%>

<table border="1" width="500" bordercolor="#809CC9" cellpadding="1" cellspacing="0">
<tr>
<th class="smallbold">ID</th>
<th class="smallbold">Date</th>
<th class="smallbold">On Call Person</th>
<th class="smallbold">Duty Person</th>
<th class="smallbold">Status</th>
</tr>
<% Do While Not rs.EOF %>
<tr>
<td class="small" align="center" valign="top"><a href=delete.asp?id=<%=rs("id")%>><%=rs("id")%></a></td>
<td class="small" align="center" valign="top"><%=rs("dbdate")%></td>
<td class="small" align="center" valign="top"><%=rs("callout")%></td>
<td class="small" align="center" valign="top"><%=rs("duty")%></td>
<td class="small" align="center" valign="top"><%=rs("status")%></td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>


Is it possible, that I could display the same information, but use Checkboxes to delete the records?

Thanks in Advance for any help, suggestions, code, tutorials
Steve

[buntine]

Yes, first, you will have to replace the anchor tags with input tags.

<form action="deleteRecords.asp" method="post" name="deletionForm">
<table border="1" width="500" bordercolor="#809CC9" cellpadding="1" cellspacing="0">
<tr>
<th class="smallbold">ID</th>
<th class="smallbold">Date</th>
<th class="smallbold">On Call Person</th>
<th class="smallbold">Duty Person</th>
<th class="smallbold">Status</th>
</tr>
<% Do While Not rs.EOF %>
<tr>
<td class="small" align="center" valign="top"><input type="checkbox" name="toDelete" value="<%= rs("id") %>" /> delete?</td>
<td class="small" align="center" valign="top"><%=rs("dbdate")%></td>
<td class="small" align="center" valign="top"><%=rs("callout")%></td>
<td class="small" align="center" valign="top"><%=rs("duty")%></td>
<td class="small" align="center" valign="top"><%=rs("status")%></td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>
<input type="submit" name="submit" value="Delete Selected Records" />
</form>

When submitted, a comma-delimited string will be passed to the server which contains the values of all the selected checkboxes.

So, you have to create an ASP page named "deleteRecords.asp" which will delete all the required records by splitting the string into tokens using the split() function. When split up, you will be left with an array that contains the id of each of the selected records.

Dim strSelection, arrSelection
strSelection = Request.Form("toDelete")
arrSelection = Split(strSelection, ",") '| This will retuen an array of sub-strings.

From there you will need to construct an SQL query by using a loop to add each of the ID's to the query.

If you need some more help, just ask.

Regards,
Andrew Buntine.

[stevem2004]

Thanks Andrew for speedy reply, sorry yes I need a little help on the SQL

[buntine]

Ok.

From there we have to use a loop to iterate through each member of the array we created.

Dim i
Dim strQuery
strQuery = "DELETE * FROM tableName WHERE "

For i = 0 To arrSelection.length - 1
If i > 0 Then
strQuery = strQuery & "AND id = " & arrSelection(i)
Else
strQuery = strQuery & "id = " & arrSelection(i)
End If
Next

Response.Write(strQuery) '| Do this first to make sure the query looks ok. It might not! When it looks ok, delete this line.

'|If that worked fine, uncomment thses lines to execute the query.
' Dim objConn, strConn
' Set objConn = Server.CreateObject("ADODB.Connection")
' strConn = "DBQ=" & Server.MapPath("your_database_path") & ";" & _
' "Driver={Microsoft Access Driver (*.mdb)}"
' objConn.Open(strConn)
'
' objConn.Execute(strQuery) '| Execute the query.

The preceding code will loop through the selected ID's and add them to the SQL query WHERE clause.

That should work, though, it has not been tested so an error should be expected.

Regards,
Andrew Buntine.

[stevem2004]

Hi Buntine,

I get the following error:-

Object required

/esol/duty/DeleteRecord.asp, line 20


Line 20 is

For i = 0 To arrSelection.length - 1


Help......:confused:

[buntine]

Oops, thats my fault. Im mixing languages again! The ASp syntax is as follows:

For i = 0 To UBound(arrSelection) - 1

Regards.

[stevem2004]

Hi Andrew,

Thanks it's starting to work now, however when I go throught the process of selecting the records to be deleted, the SQL that is written back is:-

DELETE * FROM table WHERE id = 126

I have selected multiple records and thus would have expected to see 126,127 or something that suggested there were more than just the one record? Or am I completely wrong?

[buntine]

Nope. My fault again ;)

Try romoving the -1 from the For - Next loop initiator. Like this:

For i = 0 To UBound(arrSelection)

Regards.

[stevem2004]

Andrew,

We're getting closer, thanks it sort of works, if I select a single record it deletes it no problem, but when I select multiple records it doesn't delete any of them...

the SQL looks like

DELETE * FROM roster WHERE id = 127AND id = 128


I don't understand as it looks right, doesn't it??

Thanks for your patience, I'm learning lots today...

[buntine]

Ahh, its very close. The reason the SQL query is not working is because there is no space between the number and the 'AND' operator. To fix the problem, just slightly alter the following line from this:

strQuery = strQuery & "AND id = " & arrSelection(i)

to this:

strQuery = strQuery & " AND id = " & arrSelection(i)

Fingers crossed. hehe
Regards.

[stevem2004]

Andrew,

Thanks for that the SQL looks fine now, but it's not actually deleting the records, when I select the records, click Delete, no errors displayed, but no records are deleted...

Below is all the code...now I'm confused....


<%
Dim strSelection, arrSelection
strSelection = Request.Form("toDelete")
arrSelection = Split(strSelection, ",") '| This will retuen an array of sub-strings.

Dim i
Dim strQuery
strQuery = "DELETE * FROM tablename WHERE "

For i = 0 To UBound(arrSelection)
If i > 0 Then
strQuery = strQuery & " AND id = " & arrSelection(i)
Else
strQuery = strQuery & "id = " & arrSelection(i)
End If
Next

Dim objConn, strConn
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "DBQ=" & Server.MapPath("data.mdb") & ";" & _
"Driver={Microsoft Access Driver (*.mdb)}"
objConn.Open(strConn)

objConn.Execute(strQuery) '| Execute the query.

%>

[buntine]

This type of error is normally hard to rectify. But I think I have got the answer.

The SQL query is saying delete all fields from the roster table where the value in the ID column is equal to 127 AND equal to 128. Obviously, this is not possible! So we need to replace that AND with an OR. ;)

Here is the code. Simply swap AND for OR.

strQuery = strQuery & " OR id = " & arrSelection(i)

Sorry about taking so long with this one.

Regards,
Andrew Buntine.

[stevem2004]

Andrew,

Yes it works, thank you very much for all your help & in a way it was good that it didn't work first time, as I have learnt and understood how this all worked & what the pitfalls are.

Once again many thanks

Steve

[buntine]

No worries, im glad to help. We learn best by helping others, you know. ;)