dynamicly adjusted

[onressy]

Hi currently i have a sub with that has a few of these lines:

strChiropractor = Trim(Request.Form("Chiropractor"))
strDentist = Trim(Request.Form("Dentist"))

//////it also has a few of these in the same sub right after:

'Chiropractor
strSql = "UPDATE NotValidatedMessage" _
& " SET Message = '" & Replace(strChiropractor, "'", "''") & "'" _
& " WHERE ProfessionID = 23"
gobjConn.Execute(strSql)

' Dentist
strSql = "UPDATE NotValidatedMessage" _
& " SET Message = '" & Replace(strDentist, "'", "''") & "'" _
& " WHERE ProfessionID = 14"
gobjConn.Execute(strSql)

End Sub

I was hoping to have fun with this and make it more dynamic, this is what i have so far, assistance is need to understand further, any suggestions?

Dim thing

For Each thing In Request.Form 

Response.Write "str & thing & " = " & "Trim(Request" & thing & "")) & vbCrLf
strSql =" &" UPDATE NotValidatedMessage" _
& " SET Message = '" & Replace(str"'&thing&'", "'", "''") & "'" _
& " WHERE ProfessionID = " & NewVar  'NewVar is the profession ID number
	
gobjConn.Execute(strSql)

Next

 

[onressy]

this is the error:

& " SET Message = '" & Replace(str""" & thing &, """'", "''") & "'" _
----------------------------------^

Dim thing

For Each thing In Request.Form

Response.Write "str" & thing & " = " & "Trim(Request" & thing & "))" & vbCrLf


strSql = " UPDATE NotValidatedMessage" _
& " SET Message = '" & Replace(str""" & thing &, """'", "''") & "'" _
& " WHERE ProfessionID = " & Session("newVar")

response.write " SQL: " & strSql

gobjConn.Execute(strSql)

Next

 

[tsuji]

If I understand correctly what you have in mind, there is a safe move and there is a dangerous move. If you have in mind actually making out the two variables, then it is a dangerous move. The option [1] can do the same thing and is relatively safer.
[tt]
'[1] this move is safer, do not make actually the variables named strChiropractor and strDenist
for each thing in request.form
strSql = "UPDATE NotValidatedMessage" _
& " SET Message = '" & Replace(trim(request.form(thing)), "'", "''") & "'" _
& " WHERE ProfessionID = 14"
response.write strSql & "<br />"
'gobjConn.Execute strSql
next

'[2] Alernative with dangerous move: actually making variables named strChiropractor and strDenist
'Do not use this move in any case---just for demo with your idea and with a restricted format of request.form(thing) actually postback, complying with the vbs name convention.
'Danger: involves the use of execute() and eval()
for each thing in request.form
execute "str" & thing & "=""" & replace(trim(request.form(thing)),"'","''") & """"
next

for each thing in request.form
strSql = "UPDATE NotValidatedMessage" _
& " SET Message = '" & eval("str" & thing) & "'" _
& " WHERE ProfessionID = 14"
response.write strSql & "<br />"
'gobjConn.Execute strSql
next
[/tt]

 

[BDC2]

I think that the reason your sub routine isn't dynamic at the moment is because the ProfessionID is hard coded.

You need to either pass the professionID along with the form element OR you need to look up the professionID for every form element when you update the database OR you need to use the form element name "Chiropractor" as the unique identified instead of the professionID.

You could change your form element names to be the professionID instead of the profession name, for example:

For each item in request.form()
SQLstring = "UPDATE NotValidatedMessage SET Message='" & trim(replace(request.form(item),"'","''")) & " WHERE ProfessionID = " & item
Next

In the case of the Chiropractor form element, it would be changed to <input name="23" type="text">

BDC.

 

[tsuji]

Just a quick note: the name of input element cannot/shouldn't be starting with a number in case it is useful to know.

 

[BDC2]

Yes, thanks tsuji, this came straight out of my head and is untested. You could always add a letter before the number and strip it out when processing

BDC.

 

[tsuji]

BDC2, I think the op's form has chiropractor and dentist as its input elements, plus some others, and the input's value constitutes the message contents for each discipline. The professionid is not intended to be an input. That's how I understand between the lines. I may be missing the whole picture though.

 

[tsuji]

Looking at op's 2nd code box, professionid indeed looks like as you said being set in some variable after all! (But I still doubt it is drawn from input element or request.form as such.)

 

[BDC2]

I read the question differently maybe, you're probably right though. I posted before I saw your reply.

Well I think onressy has a lot of food for thought, maybe he can claify.

BDC.

 

[onressy]

does this help:

'***************************************************************************************************
' Purpose : Save messages to the database.
' Params  : 
'***************************************************************************************************
Sub SaveMessages()
	Dim strSql
	Dim strNurse
	Dim strPatientConsumer
	Dim strPharmacist
	Dim strPhysician
	Dim strPhysicianAssistant
	Dim strPodiatrist
	
	' Get messages from Form post
	strNurse              = Trim(Request.Form("Nurse"))
	strPatientConsumer    = Trim(Request.Form("PatientConsumer"))
	strPharmacist         = Trim(Request.Form("Pharmacist"))
	strPhysician          = Trim(Request.Form("Physician"))
	strPhysicianAssistant = Trim(Request.Form("PhysicianAssistant"))
	strPodiatrist         = Trim(Request.Form("Podiatrist"))
	
	' Nurse
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strNurse, "'", "''") & "'" _
				& " WHERE ProfessionID = 1"
	gobjConn.Execute(strSql)
	
	' Patient / Consumer
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strPatientConsumer, "'", "''") & "'" _
				& " WHERE ProfessionID = 2"
	gobjConn.Execute(strSql)
	
	' Pharmacist
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strPharmacist, "'", "''") & "'" _
				& " WHERE ProfessionID = 3"
	gobjConn.Execute(strSql)
	
	' Physician
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strPhysician, "'", "''") & "'" _
				& " WHERE ProfessionID = 4"
	gobjConn.Execute(strSql)
	
	' Physician Assistant
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strPhysicianAssistant, "'", "''") & "'" _
				& " WHERE ProfessionID = 5"
	gobjConn.Execute(strSql)
	
	' Podiatrist
	strSql = "UPDATE NotValidatedMessage" _
				& " SET Message = '" & Replace(strPodiatrist, "'", "''") & "'" _
				& " WHERE ProfessionID = 6"
	gobjConn.Execute(strSql)
End Sub
%>

 

[onressy]

OPk i see your points, this line:
& " WHERE ProfessionID = " & Session("newVar")

can't happen cause Session is not dynamic, but i do have a profession table where there is a column called ID and another called Name, these hold the profession IDs and names

I guess then i would have something like this (any suggestions):

Dim thing

For Each thing In Request.Form

Response.Write "str" & thing & " = " & "Trim(Request" & thing & "))" & vbCrLf


strSql = " UPDATE NotValidatedMessage SET Message = '" & Replace(str""" & thing & """, "'", "''") & "' WHERE ProfessionID = " [Select ID from Profession where ID = & thing]

response.write " SQL: " & strSql

gobjConn.Execute(strSql)

Next

 

[BDC2]

I would say:

For Each thing In Request.Form

Response.Write "str" & thing & " = " & Trim(Request.form(thing)) & "<BR>"
strSql = "UPDATE NotValidatedMessage SET Message = '" & Replace(request.form(thing),"'","''") & "' WHERE ProfessionID = (Select ID from Profession where name = '" & thing & "')"

response.write "SQL: " & strSql & "<BR>"

gobjConn.Execute(strSql)

Next

SQL Syntax would depend on your database. What say you tsuji?

BDC.

 

[tsuji]

BDC2, I wouldn't have anything radically different to add really along the line you outlined. I think we have given sufficient focus on how the asked scripting pattern (using "for each thing in request.form") can be done to achieve the "dynamic". The rest is have the op to adapt it to their concrete page detail and backend data storage design.