Link to home
Start Free TrialLog in
Avatar of chipsterva69
chipsterva69

asked on

submitting selected options in a checklist to a database

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>

Open in new window

Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

You are so modest, LOL

Try changing this line from:

iif(Request.Form("opt") <> "", "  and not option_id in (" & Request.Form("opt") & ")", "")

TO

iif(Request.Form("opt") <> "", ") and not option_id in (" & Request.Form("opt") & ")
Avatar of chipsterva69
chipsterva69

ASKER

Thanks for the input sammySeltzer - I adjusted the code as you suggested but got the following error:

Microsoft VBScript compilation error '800a0409'
Unterminated string constant
/Scripts/data/20110808codetest-v1b.asp, line 69
iif(Request.Form("opt") <> "", ") and not option_id in (" & Request.Form("opt") & ")
------------------------------------------------------------------------------------^
What type of datatype is option_id?
Please explain to me in simple terms what this code is doing:

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


I think it might be clearer to use CASE statement to handle what you are trying to do.
Good catch!  All this time staring at it and I had forgotten to update the option_id for the actual field name - itc_key.  It is a unique identifier key, so is a bit unwieldy.  I updated the code but still have the same error.

The code itself is courtesy of robert_schutt and if I am understanding it well, it should be deleting the records from the selected options table (co_interest) where the user previously had a selected option but they cleared the checkbox.  For instance, when the first accessed the page, they had a check for the box for Twinkies, but they cleared that check.  This step should be removing that option from the table.

When I was working on this before, I was able to adjust this particular step to remove all instances in the options table where the itc_key (user ID) field matched the variable user ID passed through the query.string.  Unfortunately, I then promptly ran into an identical error with the next step, the insert.

Does that make more sense?

The next step is to then insert into the options table those options that are checked as of when the page was submitted for update.

Thanks for looking this over!
Can you show me the updated portion of the code?


I just wanted  to ensure the following part is being wrapped up in single quotes like:

 ('" & Request.Form("opt") & "'"),
Also, when I asked you to explain in simple terms what those code snip I posted is supposed to be doing,

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

I was wondering if there is supposed to be an AND somewhere like:

WHERE itr_cst_key = ? AND" & _
                  iif(Request.Form("opt") <> "", " ) and not option_id in  ('" & Request.Form("opt") & "'")...
I currently have it as:

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

I tried your code snippet and it resulted with the following error:

  Microsoft VBScript compilation error '800a0401'
  Expected end of statement
  /Scripts/data/20110808codetest-v1b.asp, line 69
  iif(Request.Form("opt") <> "", " and not itc_key in ('" & Request.Form("opt") & "'"),
  ------------------------------------------------------------------------------------^
Okay - the code now looks like this:

            Set rst_cmd = Server.CreateObject("ADODB.Command")
            rst_cmd.ActiveConnection = conn
            rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND" & _
                  iif(Request.Form("opt") <> "", " and not itc_key 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


and I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/Scripts/data/20110808codetest-v1a.asp, line 67
My apologies, the actual error reads (I was looking at the wrong test page...)

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/Scripts/data/20110808codetest-v1b.asp, line 69
iif(Request.Form("opt") <> "", " and not itc_key in  ('" & Request.Form("opt") & "'"),
-------------------------------------------------------------------------------------^
SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for all of your help!  I dropped in the code you sent along and it generates the following error:

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/Scripts/data/20110808codetest-v1c.asp, line 69
"CASE WHEN Request.Form("opt") <> "" and itc_key NOT in ('" & Request.Form("opt") & "') THEN NULL END as opt"
-------------------------^
Ok, no problem.

Ok, pls explain again what you expect this line to do:

iif(Request.Form("opt") <> "", " and not option_id in (" & Request.Form("opt") & ")", "")
If I understand it correctly, it is comparing the values from the page check boxes, and if a box that was previously checked is now unchecked, then it should be deleting the file.

Here is a line from the page:

<tr><td><input type='checkbox' id='opt{1CF16C59-E430-488F-B85B-DF3D44E92606}' name='opt' checked value='{1CF16C59-E430-488F-B85B-DF3D44E92606}'></td><td><label for='opt{1CF16C59-E430-488F-B85B-DF3D44E92606}'>Consumer Products (HBC/HBW/OTC)</label></td></tr>

So the page code:

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

So the literal SQL code would be:

DELETE From co_interest WHERE itr_cst_key = 2320D292-035A-46DC-BF3E-1F9F67CA38EC AND " & _
                  iif(Request.Form("opt") <> "", " and not itc_key in  ('" & Request.Form("opt") & "'"),

How that line accomplishes that, I don't know - it is beyond my grasp of ASP and SQL.  My apologies - I wish I could give you a better answer.
Now that you ask it, I am wondering if it would be just simpler to just delete previous instances of records where itc_cst_key (user ID) matches the user ID variable passed through in the query string, then wouldn't it be simpler to just create an insert statement that would scroll through the rows and insert the the user ID (ist_cst_key) and the option code (ist_key)?  It seems to me that would be simpler then trying to compare the changed rows, remove the rows that have been changed, then insert the rows that are new.
so the resulting code would look something like this:

      
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:

	

<%@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 = ?"
		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

Open in new window

Wow... not sure what happened with the code window...

code snippet should look like this -

Case "update":
      
            Set rst_cmd = Server.CreateObject("ADODB.Command")
            rst_cmd.ActiveConnection = conn
            rst_cmd.CommandText = "DELETE From co_interest WHERE 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

            Dim opt1
            
            For Each opt1 In Split(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

I am logging off for the evening, it is almost midnight local time.  Thanks a million, sammySeltzer, for helping out.  I really appreciate it, above and beyond the 500 points.
My apologies in the delay in getting back to this.

I have reviewed the code already written and had to tweak a couple of more things and have finally gotten past the previous errors.  Current test page code is here:

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

The attached code below now renders the following error when trying to submit updates (checked off a total of 5 boxes...):



Microsoft OLE DB Provider for SQL Server error '80040e14'

The identifier that starts with ' and itc_key NOT in ('{0373E773-743F-4277-A4AB-DA20146D5BEE}, {59BE677F-5CD2-4150-9D18-FAC08E65F59F}, {042FE6C5-6F2B-48FC-85B2-C' is too long. Maximum length is 128.

/Scripts/data/20110808codetest-v1c.asp, line 74



Any further thoughts?
<%@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>

<!---  alternate select method from sammySeltzer --->

<%

Case "update":
	
		Set rst_cmd = Server.CreateObject("ADODB.Command")
		rst_cmd.ActiveConnection = conn
		rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & Request.Form("opt") & "') THEN NULL END as 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(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>

Open in new window

What is the datatype of itr_cst_key?

I think the error is suggesting that the value is much longer the length of the data type.

I would actually split the values like this:

stroptList = Request.Form("opt")
stroptList = Replace( stroptList , ", ", "','" ) ' see below!

Then this line:
rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & Request.Form("opt") & "') THEN NULL END as opt"
becomes:

rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & stroptList & "') THEN NULL END as opt"

This way, instead of values such as
'{0373E773-743F-4277-A4AB-DA20146D5BEE}, {59BE677F-5CD2-4150-9D18-FAC08E65F59F}

it looks more like this:

'{0373E773-743F-4277-A4AB-DA20146D5BEE}', '{59BE677F-5CD2-4150-9D18-FAC08E65F59F}', etc

Note the single quotes separating the values.

BTW, what line is line 74?

If the changes I recommended don't cover line 74, do same on line 74 and the error should at least go away.
Good day sir!

itr_cst_key and itr_itc_key are both unique identifiers, 36 characters long.  

Line 74 is the last line of code that sets up the SQL delete statement:

Set rst_cmd = Server.CreateObject("ADODB.Command")
            rst_cmd.ActiveConnection = conn
            rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & Request.Form("opt") & "') THEN NULL END as opt"
            rst_cmd.Prepared = true
            rst_cmd.Parameters.Append rst_cmd.CreateParameter("param1", 200, 1, 36, g_CurrentUserId) ' adVarChar
            Set rst = rst_cmd.Execute

I will adjust the code for your suggestions and let you know what happens!
I adjusted the code per your recommendation and it works better... sortof.  It accomplishes what you want it programatically to do, but still generates an error.

The current delete/insert code is attached - I hope I filled in the gaps correctly.

The page now renders this error message:

    Microsoft OLE DB Provider for SQL Server error '80040e14'

   The identifier that starts with ' and itc_key NOT in ('{042FE6C5-6F2B-48FC-85B2-C04160A3FEE9}','{6F1063E1-FFE1-4458-A828-D5ADDAA13D90}','{803DC27A-C7B7-4E7E-A920' is too long. Maximum length is 128.

   /Scripts/data/20110808codetest-v1c.asp, line 78

Page is located here:

http://sv_webtest/Scripts/data/20110808codetest-v1c.asp?nn=2320D292-035A-46DC-BF3E-1F9F67CA38EC
<%

Case "update":
	
		Dim stroptList
		stroptList = Request.Form("opt")
		stroptList = Replace( stroptList , ", ", "','" )
		
		Set rst_cmd = Server.CreateObject("ADODB.Command")
		rst_cmd.ActiveConnection = conn
		rst_cmd.CommandText = "DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & stroptList & "') THEN NULL END as 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(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

%>

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Somehere in your code, you are using double quotes when you should be using single quotes.

Try this:


<%

Case "update":
      
            Dim stroptList
            stroptList = Request.Form("opt")
            stroptList = Replace( stroptList , ", ", "','" )
            
            Set rst_cmd = Server.CreateObject("ADODB.Command")
            rst_cmd.ActiveConnection = conn
            rst_cmd.CommandText = "    SQL = "SET QUOTED_IDENTIFIER; DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & stroptList & "') THEN NULL END as 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(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

%>


I think the syntax is right.

please let me know if not.
Sorry, the code I posted is in response to the post before your most recent one.
sammySeltzer -

I updated the code for the following:

            Set rst_cmd = Server.CreateObject("ADODB.Command")
            rst_cmd.ActiveConnection = conn
            rst_cmd.CommandText = "SET QUOTED_IDENTIFIER OFF DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & stroptList & "') THEN NULL END as 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

and received the following error when I ran the page:

-----

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string ' and itc_key NOT in ('{0373E773-743F-4277-A4AB-DA20146D5BEE}','{59BE677F-5CD2-4150-9D18-FAC08E65F59F}','{042FE6C5-6F2B-48FC-85B2-C04160A3FEE9}','{6F1063E1-FFE1-4458-A828-D5ADDAA13D90}','{29A732EA-040F-4966-B773-90C5CBADA832}','{96F5E376-0544-4C1B-8343-BC30ABE1B468}','{8E681967-E34E-4552-ABD4-49070EDBAC6D}') THEN NULL END as opt'.

/Scripts/data/20110808codetest-v1c.asp, line 78

-----

I tried switching up the code in the query from

SET QUOTED_IDENTIFIER OFF DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> "" and itc_key NOT in ('" & stroptList & "') THEN NULL END as opt

to

SET QUOTED_IDENTIFIER OFF DELETE From co_interest WHERE itr_cst_key = ? AND CASE WHEN ('" & Request.Form("opt") & "') <> '' and itc_key NOT in ('" & stroptList & "') THEN NULL END as opt

and got the following error:

----

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'as'.

/Scripts/data/20110808codetest-v1c.asp, line 78

----
just to clarify, since the bold does not really stand out that well - I changed the double quotes that appear immediately after <> to single quotes.
No, please leave the quotes like you had it before = as double quotes.

That has nothing to do with the error you are getting.

Also, did you try semi-colon (;) after the OFF like I had it?

 rst_cmd.CommandText = "SET QUOTED_IDENTIFIER OFF; DELETE From co_interest...
I tried as you originally sent it, with the semi colon and double quotes.  I got the following error:

---

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string ' and itc_key NOT in ('{0373E773-743F-4277-A4AB-DA20146D5BEE}','{59BE677F-5CD2-4150-9D18-FAC08E65F59F}','{042FE6C5-6F2B-48FC-85B2-C04160A3FEE9}','{6F1063E1-FFE1-4458-A828-D5ADDAA13D90}','{29A732EA-040F-4966-B773-90C5CBADA832}','{96F5E376-0544-4C1B-8343-BC30ABE1B468}','{8E681967-E34E-4552-ABD4-49070EDBAC6D}') THEN NULL END as opt'.

/Scripts/data/20110808codetest-v1c.asp, line 78

--

I figured that meant converting the double quotes into singles - my bad!
In the end, I never did resolve the error that Sammy_Seltzers recommendations created, but without his help in working through the problem, I never would have solved it.  Thanks!