problems with structure of code - i think?

mind_grapes · #1 (**permalink**) 11-19-09, 11:54

Hi all, i really need some help, hope someone can help me.

I had to write some vbscript that executed a stored procedure which then went to update a database - this was finally sorted but, for some reason it has caused an problem else where and i cant work out where and why this is?

The screen doesnt display an error (for example: Microsoft OLE DB Provider for SQL Server (0x80040E10) on line XXX) but the error is more to do with when a user selects and fills out a form, and then hits the submit button.

When the submit button is pressed the user is presented with a message, ("please wait while form is processed") this form then fires of to the relevant people. This message appears for all the forms, however, only the form I have been working on is processed and sent to the people that need it. For the other forms the message displays, and then freezes there, the message doesnt change, and i cant work out why?

Im guessing that the code loops or something, or I've added in my new line of code in the wrong place. I just dont know what to write and where to write it?

I'll post code but its more than the limit, so i'll have to post two additonal threads for the complete code.

pleeeease pleeease help someone. Im trying to work it out to, and will contiue to work on it.

Regards
MG

mind_grapes · #2 (**permalink**) 11-19-09, 11:58

Hi all,

So here is the code i added (in red) and the original one too:

part 1.

Code:

user    = clean(Request.Cookies("user"))			' clean function minimises risk of SQL Injection
repname = clean(Request.Cookies("name"))			' clean is defined in rc4.asp
manager = clean(Request.Cookies("manager"))
typeo   = clean(Request.Cookies("type"))



IF user <>  "" THEN									' When logged in user has a value
	IF LEN(user) > 0 THEN							' Unexplainable ASP bug means <> "" can lie, must check length too
	
		Response.cookies("user").Expires    = DateAdd("n", 40, NOw())		' Reset cookies to 40 minutes from now. 
		Response.cookies("name").Expires    = DateAdd("n", 40, NOw())		' Maintains 40 minute timeout for user so long as they keep using/loading web pages.
		Response.cookies("type").Expires    = DateAdd("n", 40, NOw())
		Response.cookies("manager").Expires = DateAdd("n", 40, NOw())
	
		IF typeo = "S" THEN 						' User is from a Store
			
			str = "SELECT top 1 Store_Name, store_code FROM stores INNER JOIN pgm_repcodes ON rep_store_code = store_code WHERE rep_code = '" & user & "' ORDER BY rep_id DESC "
			SET RS =  con.execute(str)
			
			' REP can have multiple records in pgm_repcodes so query gets the latest one added to the DB
			' multiples created because REP can change stores, loose a card, get promoted etc.
			
			If NOT RS.EOF THEN
				sentby = RS("Store_code") & ": " & RS("Store_Name")
				storename = RS("Store_Name")
				messageStr = "<div class=""ok"">Logged in as: <b>" & repname & "</b> (" & storename & ") <a href=""" & request.ServerVariables("SCRIPT_NAME") & "?action=logout"">Log Out</a></div>" 
			ELSE
				messageStr = "<div class=""confirm"">Your store could not be identified by your rep code. Please visit the Action section under AIR on the Knowledgebase, click an action point and swipe your rep card. This will set you up on AIR and stop this message appearing again.</div>" 
			END IF
			
			RS.Close
			SET RS = Nothing
		
		ELSE
	
			str = "SELECT top 1 UserGroup FROM SOHelpdeskUsers WHERE ID = '" & user & "' "
			SET RS =  con.execute(str)
			
			If NOT RS.EOF THEN 
				storename = RS("UserGroup")
				sentby = storename
				messageStr = "<div class=""ok"">Logged in as: <b>" & repname & "</b> (" & storename & ") <a href=""" & request.ServerVariables("SCRIPT_NAME") & "?action=logout"">Log Out</a></div>" 
	
			END IF
			
			RS.Close
			SET RS = Nothing
		
		END IF
	
		'response.write("@" & formid & "@")
		str = "SELECT wf_id, wf_name, wfc_name FROM pgm_webforms INNER JOIN pgm_webform_categories ON wfc_id=wf_category " 
		    IF manager = "True" OR manager = "true" Then
		        str = str & "WHERE wf_manager_only IN ( 0, 1 ) "
		    ELSE
		        str = str & "WHERE wf_manager_only IN ( 0 ) "
		    END IF
		str = str & "AND wf_status=1 "
		str = str & "AND (('H'='" & Request.Cookies("type") & "' AND wfc_id IN (4)) OR wfc_id NOT IN (4) ) "  ' Fudge to turn off Head Office only Forms
		str = str & "ORDER BY wfc_sequence, wfc_name, wf_sequence, wf_name"
		
		'response.write(str)
		SET RS = con.execute(str)
		
		'DROP DOWN MENU
		'This gets all the menus for the drop down list in Forms Manager. Cycles through a RecordSet in the database and presents them to the user.
		If NOt RS.EOF THEN
			
			nav = "<form name=""form_nav"" method=""post"" action=""" & Request.ServerVariables("SCRIPT_NAME") & """>" & vbcrlf &_
				  "<select name=""formid"" class=""chooser"" onchange=""chopt(this);"">" & vbcrlf 
			
			lastcat = RS("wfc_name")

			nav = nav & "<option style='border-bottom: 1px solid #ccc' value='' class='nowt'>Select a Form...</option>" 
			nav = nav & "<option value=''>" & RS("wfc_name") & "</option>"
			
			WHILE NOT RS.EOF
				IF lastcat <> RS("wfc_name") THEN
				    nav = nav & "<option value=''>" & RS("wfc_name") & "</option>"
				END IF
				    nav = nav & "<option class='ind' value=""" & RS("wf_id") & """ " 
				If isNumeric(formid) THEN
					If formid <> "" THEN
						If cInt(formid) = RS("wf_id") THEN
							nav = nav & " SELECTED "
						END IF
					END IF
				END IF
				    nav = nav & ">" & RS("wf_name")
				    nav = nav & "</option>" & vbcrlf
		
				lastcat = RS("wfc_name")
			RS.MoveNext
			WEND
	
			nav = nav & "</select>" & vbcrlf & "<input type=""hidden"" name=""action"" value=""doform"" />&nbsp;<input type=""submit"" value=""Go"" />"
			nav = nav & vbcrlf & "</form>" & vbcrlf 
		
		ELSE
		
			nav = "<div class=""nothingfound"">There are no forms available for you at this time. <p>You will get this message if you do not have supervisory access in your Pronto swipe card. Contact Pronto Rep Card Support (0116 232 6336) for a new card if you believe you should have supervisory access.</p></div>"	
	
		END IF
		
		RS.Close
		SET RS = Nothing

mind_grapes · #3 (**permalink**) 11-19-09, 12:00

part 2:

Code:

	IF formid <> "" AND confidentity = "" AND (action = "doform" OR action = "processform") Then
			If isNumeric(formid) THEN
			
				str = "SELECT wf_id, wf_form_file FROM pgm_webforms WHERE wf_id = '" & formid & "' "
				SET RS = con.Execute(str)
			
				If NOT RS.EOF THEN
			
					Set fso = Server.CreateObject("Scripting.FileSystemObject")
					path = Server.MapPath(RS("wf_form_file"))
					
					'response.write(path)
					
					IF fso.fileExists(path) Then
					
						set objFile = fso.opentextfile(path, 1, TRUE)
						filecontent = objFile.ReadAll
						
						objFile.close
						set objFile = nothing
						set fso = nothing
						
						objRegExp.Pattern = "<(!D|/?head|/?html|/?title|meta)[^>]*>"
						filecontent = objRegExp.Replace(filecontent,"") 						
						
						objRegExp.Pattern = "<(script|link)[^>]*form(manager|validator)[0-9]{0,}\.(js|css)[^>]*(/?>|></(script|link)>)"
						filecontent = objRegExp.Replace(filecontent,"") 						
						
						
						objRegExp.Pattern = "<form[^>]*>"
						filecontent = objRegExp.Replace(filecontent, "<form name=""form1"" method=""post"" id=""form"" action=" & Request.ServerVariables("SCRIPT_NAME") & " onsubmit=""return validate(this);"">")
						
						objRegExp.Pattern = "(<[^>]*role=""?[^/>]*)(/?>)"
						filecontent = objRegExp.Replace(filecontent, "$1 disabled=""disabled"" class=""disabled"" $2")
						
						if action = "processform" THEN 
						
							objRegExp.Pattern = "<input[^>]*type=""?submit""?[^>]*>"
							filecontent = objRegExp.Replace(filecontent,"&nbsp;")
							
							str = "INSERT INTO pgm_webformresponses (wfr_user_id, wfr_form_id, wfr_ip, wfr_key ) " &_
								  "VALUES ('" & user & "','" & formid & "', '" & Request.ServerVariables("REMOTE_HOST") & "', '" & password & "');  "
							con.execute(str)
								    
								        
							'newstarter                                
					        'str = "Exec spNewStarter '" & formName & "','" & formSurname & "','" & formStore & "'," & formHours & ",'" & formUniform & "','" & formSex & "','" & formNI & "'"
                            'CON.Execute(str)
                     
							
							str = "SELECT TOP 1 wfr_id AS ide FROM pgm_webformresponses WHERE wfr_key = '" & password & "' AND wfr_form_id = " & formid & " AND wfr_user_id = '" & user & "' ORDER BY wfr_id DESC "
							SET id = con.execute(str)
							IF NOT id.EOF THEN identity = id("ide")
							
							id.close
							SET id = Nothing
							
							str = "INSERT INTO pgm_webform_response_updated_by (wru_response_id, wru_role_id, wru_su_id, wru_field_name, wru_field_value, wru_status) " &_ 
								  "SELECT " & identity & " AS wru_response_id, war_role_id AS wru_role_id, 0 AS wru_su_id, null AS wru_field_name, null AS wru_field_value, null As wru_status " &_ 
								  "FROM pgm_webform_admin_roles WHERE (war_form_id = " & formid & " )"
							con.execute(str)
							
							str = "INSERT INTO pgm_webform_archive (wa_response_id, wa_role_id, wa_archived_by_su_id, wa_form_id, wa_emailed, wa_status) " &_ 
								  "SELECT " & identity & " AS wa_response_id, war_role_id AS wa_role_id, 0 As wa_archived_by_su_id, " & formid & " AS wa_form_id, 0 AS wa_emailed, 0 AS wa_status " &_
								  "FROM pgm_webform_admin_roles WHERE (war_form_id = " & formid & " )"
							con.Execute(str)
							
							'if action = "process form" Then
                            'str = "EXEC spNewStarter '" & formName & "','" & formSurname & "','" & formStore & "'," & formHours & ",'" & formUniform & "','" & formSex & "','" & formNI & "'"
                            'CON.Execute(str)
                            'End IF
            							
							For each item in Request.Form 
							
								objRegExp.Pattern = "(<input[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*type=""radio""[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*name=""(" & cStr(item) & ")""([a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>))|(<input[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*name=""" & cStr(item) & """([a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*type=""radio""[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>))"
								SET matches = objRegExp.Execute(filecontent)
								
								FOR each match in matches
									objRegExp.Pattern = "value=""" & Request.form(item) & """"
									found = objRegExp.test(match)
									if found Then
										filecontent = Replace(filecontent,match,"<img src=""/images/kb/button_tick.gif"" />")
									ELSE
										filecontent = Replace(filecontent,match,"&nbsp;")
									END IF
								NEXT
								
								SET matches = nothing
								
								objRegExp.Pattern = "(<input[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*type=""checkbox""[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*name=""(" & cStr(item) & ")""([a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>))|(<input[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*name=""" & cStr(item) & """([a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*type=""checkbox""[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>))"
								filecontent = objRegExp.replace(filecontent,"<img src=""/images/kb/button_tick.gif"" />")
								
								objRegExp.Pattern = "<(input|textarea)[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*name=""" & cStr(item) & """([a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*</textarea>|[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>)"
								filecontent = objRegExp.replace(filecontent,"<b class=""userdata"">" & Request.form(item) & "&nbsp;</b>")
								
								objRegExp.Pattern = "<option[^>]*>[^<]*</option>"
								filecontent = objRegExp.replace(filecontent,"")
								
								objRegExp.Pattern = "<select[^>]*name=""" & cStr(item) & """[^>]*>[^<]*</select>"
								filecontent = objRegExp.replace(filecontent, "<b class=""userdata"">" & Request.form(item) & "&nbsp;</b>")
			
								IF Trim(Request.form(item)) <>  "" THEN
									str = "INSERT INTO pgm_webformdata (wfd_field_name, wfd_field_value, wfd_response_id) " &_ 
										  "VALUES ('" & Base64Encode(endecrypt(cStr(item),password)) & "', '" & Base64Encode(endecrypt(clean(Request.form(item)),password)) & "', '" & identity & "') "
									Con.Execute(str)
								END IF
								
							Next
					
							objRegExp.Pattern = "(<input[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*type=""(checkbox|radio)""[a-z0-9\?\(\)&%! \s\n/\.\\""':;=_\-]*>)"
							filecontent = objRegExp.replace(filecontent,"&nbsp;")
						
							objRegExp.Pattern = "(<body[a-z0-9\?\(\)&%! \s\n/\.\\#""':;=_\-]*>)"
							filecontent = objRegExp.replace(filecontent,"<form name=""conf"" id=""conf"" onsubmit=""startprocess();"" action=""" & request.ServerVariables("SCRIPT_NAME") & """ method=""post""> <div class=""confirm""><h4>The form is NOT submitted yet.</h4>I confirm that the details I have provided below are correct and understand that it is my responsibility should there be any inaccuracies.</div><div class=""confirm2""><input style=""font-size: 16px;""  type=""submit"" value=""I AGREE ( SUBMIT FORM )"">" & processStr & "</div><input type=""hidden"" name=""action"" value=""confirmsubmit""><input type=""hidden"" value=""" & identity & """ name=""confidentity""><input type=""hidden"" name=""posthtml"" value="""" /></form><form action=""" & Request.ServerVariables("SCRIPT_NAME") & """ method=""post"">" & variables & "<div class=""confirm3""><input type=""button"" value=""NO, THERE ARE ERRORS (Go back to fix them)"" onclick=""startprocess(); amendform();""></div></form>")
						
							
						END IF
					
						objRegExp.Pattern = "</form>"
						
						IF action = "processform" THEN
						
							filecontent = objRegExp.replace(filecontent,"<input type=""hidden"" value=""" & formid & """ name=""formid"" />" & vbcrlf & "<input type=""hidden"" name=""identity"" value=""" & identity & """ />" & vbcrlf & "</form>")
						ELSE
							filecontent = objRegExp.replace(filecontent,"<input type=""hidden"" value=""" & formid & """ name=""formid"" />" & vbcrlf & "<input type=""hidden"" name=""action"" value=""processform"" />" & vbcrlf & "</form>")
						END IF
						
						
					    
						'objRegExp.Pattern = "(</?(body|html|meta|head))([^>]*)>|(<title>[^>]*</title>)"
						'filecontent = objRegExp.replace(filecontent,"")
						
						objRegExp.Pattern = "(</?body[^>]*>)"
						filecontent = objRegExp.replace(filecontent,"")
						
						Set objRegExp = Nothing
					
					ELSE
					    
						response.write("<!-- Form Not found -->")
						
					END IF
				END IF
				
				RS.Close
				SET RS = Nothing
			END IF

mind_grapes · #4 (**permalink**) 11-19-09, 12:02

part 3 0f 3

Code:

ELSE
			IF action = "confirmsubmit" THEN
				IF isNumeric(confidentity) THEN
				 
					postedhtml=request.Form("posthtml")
				
					str = "UPDATE pgm_webformresponses SET wfr_status = 1, wfr_html_response='" & Base64Encode(endecrypt(postedhtml,password)) & "' WHERE wfr_id = '" & confidentity & "' "
					CON.Execute(str)
					
					str = "SELECT DISTINCT we_email_address, wf_name " &_ 
						  "FROM pgm_webform_email_recipients " &_ 
						  "INNER JOIN pgm_webforms ON wf_id=we_form_id " &_ 
						  "INNER JOIN superusers ON su_email_address=we_email_address " &_ 
						  "INNER JOIN pgm_webform_superuser_admin_roles ON wsar_su_id=su_id " &_
						  "INNER JOIN pgm_webform_admin_roles ON war_role_id=wsar_role_id " &_ 
						  "WHERE we_form_id= '" & formid & "' AND we_status=1 AND wsar_status=1 "
						  
    						  
					SET RS = con.Execute(str)
					
					IF NOT RS.EOF THEN
					
						tostr = RS("we_email_address")
						thesubject = RS("wf_name") & ": " & sentby & " (" & FormatdateTime(NOW(),2) & ") "
						
						WHILE NOT RS.EOF
						
							tostr = tostr & "," & RS("we_email_address")
							
						RS.MoveNext
						WEND
						
						bodyHTML = "<html><head><link rel=""stylesheet"" type=""text/css"" href=""http://kbserver/aform/v3/formmanager.css""></link></head><body>" &_ 
								   "<p>From: <b>" & sentby & "</b><br><a href=""http://kbserver/aform/V3/admin.asp"" style=""text-decoration:underline;"">Click Here for full details</a></p> " &_ 
								   postedhtml &_
								   "</body></html>"
						
						Set objMessage = CreateObject("CDO.Message") 
						objMessage.Subject = thesubject  
						objMessage.From = "robot@kbserver" 
						objMessage.To = tostr ' "dsmith@work.com" "pmartin@work.com" '
						'objMessage.TextBody = postedhtml 
						objMessage.HTMLBody = Replace(bodyHTML,"/images/","http://kbserver/images/")
						objMessage.Send
						SET objMessage = Nothing
					
					END IF
					
					messageStr = "<div class=""ok""><h1 style=""font-size: 4em;"">Done</h1><b>Thanks your form has been submitted.</b></div>" & vbcrlf		
					str = "SELECT wf_printable AS pr FROM pgm_webforms WHERE wf_id = '" & formid & "' "
					
					SET RS=con.execute(str)
					IF NOT RS.EOF THEN isPrintable = RS("pr")
					RS.CLOSE
					SET RS = Nothing
					
				END IF
			END IF
		End IF
		
		
	END IF
	
	
ELSE ' Not Logged In
	If Trim(Request("in")) = "1" THEN 
		messageStr = "<div class=""confirm"">You have been logged out. This is a security feature after 12 minutes of inactivity.</div>" & vbcrlf
	END IF 
	
	nav = "<form name=""login"" action=""" & Request.ServerVariables("SCRIPT_NAME") & """ method=""post"">" & vbcrlf
	nav = nav & "	<table class=""adminform"" width=""100%"" cellspacing=""0"">" & vbcrlf
	nav = nav & "		<tr class=""h"">" & vbcrlf
	nav = nav & "				<td colspan=""2""><b>Stores</b> - Swipe your card: </td>" & vbcrlf
	nav = nav & "			</tr>" & vbcrlf
	nav = nav & "			<tr class=""hr"">" & vbcrlf
	nav = nav & "				<td colspan=""2""><input type=""password"" name=""swipe"" style=""width: 60%"" />" & vbcrlf
	nav = nav & "				<input name=""submit"" type=""submit"" value=""Go"" /></td>" & vbcrlf
	nav = nav & "			</tr>" & vbcrlf
	nav = nav & "		</table>" & vbcrlf
	nav = nav & "		<table width=""100%"" class=""adminform"" cellspacing=""0"" style=""margin-top: 20px;"">" & vbcrlf
	nav = nav & "			<tr class=""h"">" & vbcrlf
	nav = nav & "				<td colspan=""2"" ><b>Head Office</b> - Login </td>" & vbcrlf
	nav = nav & "				</tr>" & vbcrlf
	nav = nav & "			<tr class=""hr"">" & vbcrlf
	nav = nav & "				<td width=""200"">Username:</td>" & vbcrlf
	nav = nav & "				<td><input name=""username"" type=""text"" style=""width: 25%;"" /></td>" & vbcrlf
	nav = nav & "			</tr>" & vbcrlf
	nav = nav & "			<tr class=""hr"">" & vbcrlf
	nav = nav & "				<td width=""200"">Password</td>" & vbcrlf
	nav = nav & "				<td><input type=""password"" name=""pass"" style=""width: 25%"" /> <input type=""submit"" value=""submit"" /></td>" & vbcrlf
	nav = nav & "			</tr>" & vbcrlf
	nav = nav & "		</table>" & vbcrlf
	nav = nav & "		<input type=""hidden"" name=""action"" value=""login"" />" & vbcrlf
	nav = nav & "	</form>" & vbcrlf


END IF

con.close
SET con = nothing

This I believe is everything, anything else please say. really need the help guys,

Kind regards
MG

Pat Phelan · #5 (**permalink**) 12-07-09, 07:46

My solution for localization has always been to use Resources. These amount to snippets of text (words, phrases, paragraphs) that are identified by a number, keyword, or GUID). A table contains one or more rows (for each language) with a Unicode representation for the resources in a specific language. This allows the developer to code the program using one or many languages, then allows a linguistic team to provide appropriate translations at a later point in time. You can even add new languages long after the code had been deployed, with only trivial effort.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech