troubleshooting Question

submitting selected options in a checklist to a database

Avatar of chipsterva69
chipsterva69 asked on
ASPVB Script
30 Comments2 Solutions373 ViewsLast Modified:
This is the second part of a problem I have been working on for the last few days.  With a great amount of assistance from robert_schutt and mgfranz, I was able to resolve the first part of the problem, dynamically creating a list of checkboxes based on information from two tables to reflect a user's selections (solution).

Now comes the second part of the challenge - converting that checklist now into a submission to a database.  I have included the code I have to date below.  Mr. Schutt included some recommended code to accomplish the submit, but I can't seem to get the code to work (user error, I am positive).

Some of the particulars:

- page is coded in ASP VBScript (obviously, I know...)
- the fields itc_key, itr_itc_key and itr_cst_key are all unique identifier keys coming out of the SQL database, are 36 characters long, alphanumeric and for reasons beyond my understanding, render out in code with curly brackets on either end (example: {0373E773-743F-4277-A4AB-DA20146D5BEE})
- users are coming to this page from a link that includes a key value passed through the query.string, nn=value
- the data is coming from a SQL database that supports a proprietary AMS system, so changing the data structure is not an option in this project
- the basic data layout is thus
   co_interest_code table has a list of 40 business interest codes, with key, abbreviation,
   description and other values
   co_interest table has a list of users, each record being table key, user key, code key and
   other values
   users can and do have multiple business codes selected

I am probably throwing in too much information, my apologies.  I am learning that what I don't know is greatly exceeding what I do know.

When I run the script as is, I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/Scripts/data/20110808codetest-v1b.asp, line 72

This coincides with the line:

Set rst = rst_cmd.Execute

and refers back to the query:

rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ?" & _
                  iif(Request.Form("opt") <> "", " and not option_id in (" & Request.Form("opt") & ")", "")

I worked on this for a bit and tried removing the iif option and just delete each row in co_interest with a matching itr_cst_key value, which worked wonderfully.  However, the code threw the exact same error though when it got to the actual insert statement code:

rst_cmd.CommandText = "SELECT * From co_interest WHERE itr_itc_key = " & opt1 & _
                        " and itr_cst_key =  ?"

I have a rough version of the page up and running for your edification:

http://www.hdma.net/Scripts/data/20110808codetest-v1b.asp?nn=2320D292-035A-46DC-BF3E-1F9F67CA38EC

Again - props to robert_schutt and mgfranz for their help so far.  The failure is completely my own.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<%

Response.Buffer = True
Response.Expires = 0

Dim g_CurrentUserId
g_CurrentUserId = Request.QueryString("nn")

Dim conn, rst, rst_cmd, redir
redir = False

Set conn = Server.CreateObject("ADODB.Connection")

conn.open "connection string information"

Select Case Request.QueryString("mode")

Case "": ' show options
	
Set rst_cmd = Server.CreateObject("ADODB.Command")
rst_cmd.ActiveConnection = conn
rst_cmd.CommandText = "SELECT itc_key, itc_code, itc_description, CASE WHEN EXISTS (SELECT * from co_interest where itr_cst_key = ? and itr_itc_key = itc_key) THEN 1 ELSE 0 END as checked from co_interest_code ORDER by itc_description ASC"
rst_cmd.Prepared = true
rst_cmd.Parameters.Append rst_cmd.CreateParameter("param1", 200, 1, 36, g_CurrentUserId) ' adVarChar
Set rst = rst_cmd.Execute
rst_numRows = 0
		
%>

<html>
<head>
</head>
<body>
<p>Now = <%= Now() %></p>
<form name="frm" action="<%= Request.ServerVariables("URL") %>?nn=<%= Request.QueryString("nn") %>&mode=update" method="post">

<table border=1 cellpadding=2 cellspacing=1>
<%

		
		While Not rst.eof
			Response.Write "<tr>" & _
				"<td><input type='checkbox' id='" & rst.Fields("itc_key").Value & _
				"' name='opt'" & iif(rst.Fields("checked").Value, " checked", "") & _
				" value='" & rst.Fields("itc_key").Value & "'></td>" & _
				"<td><label for='opt" & rst.Fields("itc_code").Value & "'>" & _
				rst.Fields("itc_description").Value & "</label></td></tr>" & vbCrLf
			rst.MoveNext
		Wend
		rst.close
		Set rst = Nothing
		Response.Write "<tr><td colspan=3 align='right'>" & _
			"<input type='submit' value='Update'></td></tr>" & vbCrLf
%>
</table>
</form>

<%

Case "update":
	
		Set rst_cmd = Server.CreateObject("ADODB.Command")
		rst_cmd.ActiveConnection = conn
		rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ?" & _
			iif(Request.Form("opt") <> "", " and not option_id in (" & Request.Form("opt") & ")", "")
		rst_cmd.Prepared = true
		rst_cmd.Parameters.Append rst_cmd.CreateParameter("param1", 200, 1, 36, g_CurrentUserId) ' adVarChar
		Set rst = rst_cmd.Execute
		rst_numRows = 0

		Dim opt1
		
		For Each opt1 In Split(Trim(Request.Form("opt")),",")
		
			Set rst_cmd = Server.CreateObject("ADODB.Command")
			rst_cmd.ActiveConnection = conn
			rst_cmd.CommandText = "SELECT * From co_interest WHERE itr_itc_key = " & opt1 & _
				" and itr_cst_key =  ?"
			rst_cmd.Prepared = true
			rst_cmd.Parameters.Append rst_cmd.CreateParameter("param1", 200, 1, 36, g_CurrentUserId) ' adVarChar
			Set rst = rst_cmd.Execute
			rst_numRows = 0
			
			If rst.eof Then
				rst.AddNew
				rst.Fields("itr_cst_key").Value = g_CurrentUserId
				rst.Fields("itr_itc_key").Value = opt1
				rst.Update
			End If
			rst.Close
		Next
		Set rst = Nothing
		redir = true

End Select

If redir Then Response.Redirect Request.ServerVariables("URL") '' back to 'show' after 'update'

Function iif(b, v1, v2)
	If b Then
		iif = v1
	Else
		iif = v2
	End If
End Function

%>










<body>
</body>
</html>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 30 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 30 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros