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

ASPVB Script

Avatar of undefined
Last Comment
chipsterva69

8/22/2022 - Mon
sammySeltzer

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") & ")
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") & ")
------------------------------------------------------------------------------------^
sammySeltzer

What type of datatype is option_id?
Your help has saved me hundreds of hours of internet surfing.
fblack61
sammySeltzer

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.
chipsterva69

ASKER
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!
sammySeltzer

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") & "'"),
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sammySeltzer

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") & "'")...
chipsterva69

ASKER
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") & "'"),
  ------------------------------------------------------------------------------------^
chipsterva69

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
chipsterva69

ASKER
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
sammySeltzer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
chipsterva69

ASKER
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"
-------------------------^
sammySeltzer

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") & ")", "")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chipsterva69

ASKER
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.
chipsterva69

ASKER
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.
chipsterva69

ASKER
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
chipsterva69

ASKER
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.
chipsterva69

ASKER
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

sammySeltzer

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chipsterva69

ASKER
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!
chipsterva69

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sammySeltzer

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

%>


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
sammySeltzer

I think the syntax is right.

please let me know if not.
sammySeltzer

Sorry, the code I posted is in response to the post before your most recent one.
chipsterva69

ASKER
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

----
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chipsterva69

ASKER
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.
sammySeltzer

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...
chipsterva69

ASKER
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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
chipsterva69

ASKER
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!