Hi Everybody,
I've been trying to work out some SQL / .asp interactions, where I have a form page that is populated by products in a database. On this page, I can successfully connect to database, and SQL works to the point where I can print a list of the prods to a page. On the page, each prod has a checkbox next to it. If you check some boxes and then hit send, the items checked are passed to another .asp page that handles the data. This page creates an SQL script that looks like
sql = "DELETE FROM prod WHERE listnum IN ("
...a loop fills the IN list with the request.form checkbox entries.
I continually get errors no matter WHAT I've tried on this data handler page.
Here is the code from the intial form page:
<%@ Language=VBscript %>
<% Option Explicit%>
<% Response.Buffer = true %>
<%
dim prod(100)
dim listnum(100)
dim c
c=1
Dim Conn, Rs, sql
sql = "SELECT * FROM prod"
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("milinternational.mdb")
Rs.Open sql, Conn
while not Rs.eof
prod(c) = Rs("product")
listnum(c) = Rs("listnum")
rs.movenext
c = c+1
wend
%>
<html>
<head>
<title>Mil International - Administrator Only</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table cellpadding="3" cellspacing="3" border="1" align="center">
<tr>
<td colspan="3">Mil International Products List</td>
</tr>
<tr>
<td></td><td>List #</td><td>Product Name</td>
</tr>
<% dim i
c = c-1
%>
<form action="delhandle2.asp" method="post">
<%for i = 1 to c %>
<tr>
<td><INPUT TYPE=CHECKBOX NAME="listnum" value="<%=listnum(i)%>"></td><td></td><td><% response.write(prod(i)) %> </td>
</tr>
<% Next %>
<input type="submit" name="submit" value="Send">
</form>
</table>
<%
Rs.Close
Set Rs = Nothing
Set Conn = Nothing
%>
</body>
</html>
AND here is the code from the DATA handler page:
<%@ Language=VBscript %>
<% Option Explicit%>
<% Response.Buffer = true %>
<%
dim counter
counter=1
dim del(100)
dim x
dim del_list
dim add
del_list = ""
dim upper
upper=request.Form("listnum").count
Dim Conn, Rs, sql
sql = "DELETE FROM prod WHERE listnum IN ("
if upper >= 1 then
For counter = 1 To Request.Form("listnum").Count-1
del_list = del_list & Request.Form("listnum")(counter) & "," &""
Next
del_list=del_list+Request.Form("listnum")(counter)
end if
sql = sql&del_list&")"
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("milinternational.mdb")
Rs.Open sql, Conn
Rs.Close
Set Rs = Nothing
Set Conn = Nothing
response.Redirect("index.html")
%>
Why can I succesfully open the DB and print from it in the first page...but in the 2nd page, something crashes? This leads me to believe that my connection to the DB is not the issue. I think it is the SQL. What do you think?
I did a test to see exactly what the SQL script was by simply response.write it to the screen...to make sure the loops and the request.form was working. This is the result(when checking only boxes 1 and 2):
DELETE FROM prod WHERE listnum IN (1,2)
I'm struggling here and getting frustrated. ANY help would be greatly greatly appreciated. Thank you for your time! : )
-Brian
