Using checkboxes to return result to SQL Server

Question

I have the following code in ASP Classic:

<%
dim prac_id
prac_id = Request.Form("Practice_ID")
dim surname_id
surname_id = Request.Form("clientsurname")

If prac_id <> "" And IsNumeric(prac_id) AND surname_id <>"" then
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "claims","username","password"
strSQL = "SELECT * FROM (Claim_Status INNER JOIN PI_Agents ON Claim_Status.Agent_ID =  PI_Agents.Agent_ID) INNER JOIN Statuses on Claim_Status.Status_ID = Statuses.Status_ID WHERE Practice_ID = '"&prac_id&"' AND Client_Surname LIKE '%" & surname_id & "%'"

set rs = Conn.Execute (strSQL)

%>
<html>
<head>
<title>PI Accepts - Results</title>
</head>

<body>
<a href="default.asp" title="Home Page">Home Page</a>
<form method="POST" action="result.asp" name="form1">
<td width="10">
<select name="Practice_ID" size="1" ID="Prac">
     <option value="0">Select Practice</option>
     <option value="1">HCL</option>
     <option value="2">Silverbeck</option>
     <option value="3">TPF</option>
 <option value="4">Express</option>
</select>

</td>
<p> Client Surname </p>
<input type="text" name="clientsurname" value="" />

<tr>
  <td>&nbsp;</td>
  <td colspan="2"><input type="submit" name="Submit" value="Submit"></td>
</tr> 
</form>

<table border="1">
<form>
<tr><th>Claim ID</th><th>Date</th><th>Agent</th><th>Client First Name</th><th>Client Surname</th><th>Client Number</th><th>Current Status</th><th>Accepted</th><th>Rejected</th></tr>
<% DO WHILE NOT rs.EOF %>
<tr><td><% Response.Write rs("ID_Ref") %></td><td><% Response.Write rs("Date_Passed") %></td><td><% Response.Write rs("Agent_Name") %></td><td><% Response.Write rs("Client_First_Name") %></td><td><% Response.Write rs("Client_Surname") %></td><td><% Response.Write rs("Main_Number") %></td><td><% Response.Write rs("Status") %></td>
<td><input type="checkbox" name="accepted" id="<%= rs("ID_Ref")%>" value="1"></td><td> <input type="checkbox" name="rejected" id="<%= rs("ID_Ref")%>" value="2"></td><td><input type="submit" name="editclaim" value="Submit"></td></tr>
</form>
<%
rs.MoveNext
Loop
%>
</table>
if request.form("accepted") <>""then
update Claim_Status SET Status_ID=1 WHERE ID_Ref=

<%
conn.Close
Set conn = Nothing
End IF
%>

</body>
</html>

What I'm trying to do is, using the two checkboxes (accepted and rejected), if one of these is selected and 'Submit' is pressed, then it returns the value (either 1 for accepted or 2 for rejected) to the column 'Status_ID' in the 'Claim_Status' table where the ID_Ref of the claim being selected matches the ID_Ref of the claim in the database (e.g, if Smith is searched for and it brings up for claims numbered 1,2,3,4 and someone picks the one numbered 3, then it'll only update that one on the database)

The page works fine and the checkboxes appear OK (they both need to be unticked when page first displays) but I cant quite figure out the last bit of coding to get the status to update based on the relevant checkbox being ticked.

Any help appreciated...Thanks!!

EEK!!! Look up sql injection. Your code is wide open!!! You need to use either stored procedures or parameterized queries. — Sean Lange, Commented Jul 31, 2014 at 16:25
Completely new to this so never heard of SQL Injection, will look into it properly but a quick google leads me to believe its a hack-type thing, the purpose of this site is an internal use by only 2 or 3 members of staff (one being me) so not sure how protected it would need to be? — davidn84, Commented Jul 31, 2014 at 16:30
Either way you should develop good habits now. Especially since you say you are new. You will also get better performance if you select only the columns you need instead of using select *. — Sean Lange, Commented Jul 31, 2014 at 16:31
I tried that but it kept telling me the column i was inner joining with was not a valid column (or something along them lines). When using Select * it worked so i went along with that. — davidn84, Commented Jul 31, 2014 at 16:34
The right way to fix that would be to fix the error instead of using select *. However...let's focus on your question and not diverge off topic. — Sean Lange, Commented Jul 31, 2014 at 16:36

davidn84 · Accepted Answer · 2014-08-12 13:46:37Z

There are a few issues here. You have the FORM open tag outside the loop but the FORM close tag inside the loop. Do you want the user to submit multiple rows at once or just one at a time. I presume as you have a submit button in each row you want the user to submit one row at a time.

If this is the case then you should move the FORM open tag inside the loop and add a hidden field to identify the row.

<% DO WHILE NOT rs.EOF %>
<form method="POST" action="result.asp">
<input name="idref" value="<%= rs("ID_Ref") %>">
<tr>
  <td><% Response.Write rs("ID_Ref") %></td>
  <td><% Response.Write rs("Date_Passed") %></td>
  <td><% Response.Write rs("Agent_Name") %></td>
  <td><% Response.Write rs("Client_First_Name") %></td>
  <td><% Response.Write rs("Client_Surname") %></td>
  <td><% Response.Write rs("Main_Number") %></td>
  <td><% Response.Write rs("Status") %></td>
  <td><input type="checkbox" name="accepted" id="<%= rs("ID_Ref")%>" value="1"></td>
  <td><input type="checkbox" name="rejected" id="<%= rs("ID_Ref")%>" value="2"></td>
  <td><input type="submit" name="editclaim" value="Submit"></td>
</tr>
</form>
<%
rs.MoveNext
Loop
%>

Then on the page the form posts to (results.asp?) you can retrieve the value of the refid field (Request.Form("refid")), check which checkbox was checked and do your database update.

There's probably a few issues with the above code with the FORM tags inside the TABLE structure this way but it should still work.

However, if you want the user to be able to submit mutliple rows at once then you would move the FORM tags outside of the TABLE and loop and name each checkbox differently so you can identify which one is clicked. You would also change to just one submit button. Then on the page you post to you can either open your recordset again and loop through and check the status of the checkboxes or loop through the Request.Form collection to find which checkboxes were checked.

<form method="POST" action="result.asp">
<table border="1">
<tr><th>Claim ID</th><th>Date</th><th>Agent</th><th>Client First Name</th><th>Client Surname</th><th>Client Number</th><th>Current Status</th><th>Accepted</th><th>Rejected</th>    
</tr>
<% DO WHILE NOT rs.EOF %>
<tr>
  <td><% Response.Write rs("ID_Ref") %></td>
  <td><% Response.Write rs("Date_Passed") %></td>
  <td><% Response.Write rs("Agent_Name") %></td>
  <td><% Response.Write rs("Client_First_Name") %></td>
  <td><% Response.Write rs("Client_Surname") %></td>
  <td><% Response.Write rs("Main_Number") %></td>
  <td><% Response.Write rs("Status") %></td>
  <td><input type="checkbox" name="accepted-<%= rs("ID_Ref")%>" id="<%= rs("ID_Ref")%>" value="1"></td>
  <td><input type="checkbox" name="rejected-<%= rs("ID_Ref")%>" id="<%= rs("ID_Ref")%>" value="2"></td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>
<input type="submit" name="editclaim" value="Submit">
</form>

And in the page you post to:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "claims","username","password"
strSQL = "SELECT * FROM (Claim_Status INNER JOIN PI_Agents ON Claim_Status.Agent_ID =  PI_Agents.Agent_ID) INNER JOIN Statuses on Claim_Status.Status_ID = Statuses.Status_ID WHERE Practice_ID = '"&prac_id&"' AND Client_Surname LIKE '%" & surname_id & "%'"
set rs = Conn.Execute (strSQL)
DO WHILE NOT rs.EOF
   intStatusID = 0
   If Request.Form("accepted-" & rs("ID_Ref")) = "1" Then
     'Accepted was checked
     intStatusID = 1
   End If
   If Request.Form("rejected-" & rs("ID_Ref")) = "2" Then
     'Accepted was checked
      intStatusID = 2
   End If
   'Your update query here
   strSQL = "UPDATE Claim_Status SET Status_ID = " & intStatusID & " WHERE Ref_Id = " & rs("ID_Ref")
   Conn.Execute strSQL
   rs.MoveNext
Loop

Or if you didn't want to loop through the database again you could use the method below (although the first method is safer as otherwise a hacker could potentially update the wrong rows by posting their own IDs).

For Each key in Request.Form
  If Left(key, 10) = "accepted-" Or Left(key, 10) = "rejected-" Then
     refid = Mid(key, 9) 'get the id

     If Left(key, 10) = "accepted-" Then
       If Request.Form(key) = "1" Then
         'accepted checkbox checked, do your update query
       End If
     Else
       If Request.Form(key) = "2" Then
         'accepted checkbox checked, do your update query
       End If
     End If
  End If
Next

Hopefully you get the idea from the above.

Hi, many thanks for this, one question now...on the Update page, how am I writing the update code - UPDATE Claim_Status SET Status_ID=(what goes here?) WHERE ID_Ref=(how do i tell it to update the ref_id for the check box that's been selected?) — davidn84, Commented Aug 1, 2014 at 11:28
I added an example of the update query in one of the examples above. If Status_ID should be 1 or 2 depending which checkbox is selected then you should consider using input type="radio" so both cannot be selected. — johna, Commented Aug 2, 2014 at 0:23

Collectives™ on Stack Overflow

Using checkboxes to return result to SQL Server

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related