Requesting lots of variables

[Tom]

Hi everyone,

I don't know if anyone can help me,

I've got roughly 25 forms in a site, with between 10 and 70 fields on
each form. Now, these fields need to be inserted into a DB, each form
has it's own table and each column is named the same as it's
corresponding form field. Make sense?

My question is, is there a quicker way of constructng my SQL UPDATE
query string for all this lot? Is it possible to request all the form
fields from a single page and put them into and array that I can loop
through and construct the SQL query on the fly? Or am I going to have
to do it the hard way and and do a request.form for every form field
and construct each one manually?

I've had a quick google round but can't really find anything that
answers my question,

Maybe I'm just being lazy, but any tips would be really appreciated!

Thanks guys,

Tom

[Slim]

for each el in Request.Form
Session("forms" &el) = Request.Form(el )
next

sql = "UPDATE myTab set "

dim coma

for each el in Session.Content s
if mid(el,1,5) = "forms" then
sql = sql & coma & mid(el,6) &" = "& Session(el) coma = ","
end if
next

Response.Write sql

"Tom" <sp**@tom-jordan.co.uk> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi everyone,

I don't know if anyone can help me,

I've got roughly 25 forms in a site, with between 10 and 70 fields on
each form. Now, these fields need to be inserted into a DB, each form
has it's own table and each column is named the same as it's
corresponding form field. Make sense?

My question is, is there a quicker way of constructng my SQL UPDATE
query string for all this lot? Is it possible to request all the form
fields from a single page and put them into and array that I can loop
through and construct the SQL query on the fly? Or am I going to have
to do it the hard way and and do a request.form for every form field
and construct each one manually?

I've had a quick google round but can't really find anything that
answers my question,

Maybe I'm just being lazy, but any tips would be really appreciated!

Thanks guys,

Tom

[Tom]

Brilliant - thx for the quick response too

[Slim]

"Tom" <sp**@tom-jordan.co.uk> wrote in message
news:11******** ************@o1 3g2000cwo.googl egroups.com...

Brilliant - thx for the quick response too

no worries I guess you noticed the that
coma = ","
should of been on its own line

for each el in Request.Form
Session("forms" &el) = Request.Form(el )
next

sql = "UPDATE myTab set "

dim coma

for each el in Session.Content s
if mid(el,1,5) = "forms" then
sql = sql & coma & mid(el,6) &" = "& Session(el) coma = ","
end if
next

[Bob Barrows [MVP]]

Perhaps I'm missing something :-)
Could you explain why you go to the extra step of transferring the Form
collection to Session?
Also, how will the need for delimiters be handled? I would assume that not
all of these fields are numeric ...

Bob Barrows

Slim wrote:

for each el in Request.Form
Session("forms" &el) = Request.Form(el )
next

sql = "UPDATE myTab set "

dim coma

for each el in Session.Content s
if mid(el,1,5) = "forms" then
sql = sql & coma & mid(el,6) &" = "& Session(el) coma = ","
end if
next

Response.Write sql

"Tom" <sp**@tom-jordan.co.uk> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi everyone,

I don't know if anyone can help me,

I've got roughly 25 forms in a site, with between 10 and 70 fields on
each form. Now, these fields need to be inserted into a DB, each form
has it's own table and each column is named the same as it's
corresponding form field. Make sense?

My question is, is there a quicker way of constructng my SQL UPDATE
query string for all this lot? Is it possible to request all the form
fields from a single page and put them into and array that I can loop
through and construct the SQL query on the fly? Or am I going to have
to do it the hard way and and do a request.form for every form field
and construct each one manually?

I've had a quick google round but can't really find anything that
answers my question,

Maybe I'm just being lazy, but any tips would be really appreciated!

Thanks guys,

Tom

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Slim]

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:Of******** ******@TK2MSFTN GP11.phx.gbl...

Perhaps I'm missing something :-)
Could you explain why you go to the extra step of transferring the Form
collection to Session?
Also, how will the need for delimiters be handled? I would assume that not
all of these fields are numeric ...

no you don't have to change them to Session, but I already had the code in
one of my pages where I like to put them in session for persistence of the
form.

and yes you can simply test to see they are numeric

the important thing is that it was a help to Tom
Bob Barrows

Slim wrote:

for each el in Request.Form
Session("forms" &el) = Request.Form(el )
next

sql = "UPDATE myTab set "

dim coma

for each el in Session.Content s
if mid(el,1,5) = "forms" then
sql = sql & coma & mid(el,6) &" = "& Session(el) coma = ","
end if
next

Response.Write sql

"Tom" <sp**@tom-jordan.co.uk> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi everyone,

I don't know if anyone can help me,

I've got roughly 25 forms in a site, with between 10 and 70 fields on
each form. Now, these fields need to be inserted into a DB, each form
has it's own table and each column is named the same as it's
corresponding form field. Make sense?

My question is, is there a quicker way of constructng my SQL UPDATE
query string for all this lot? Is it possible to request all the form
fields from a single page and put them into and array that I can loop
through and construct the SQL query on the fly? Or am I going to have
to do it the hard way and and do a request.form for every form field
and construct each one manually?

I've had a quick google round but can't really find anything that
answers my question,

Maybe I'm just being lazy, but any tips would be really appreciated!

Thanks guys,

Tom

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Tom wrote:

Hi everyone,

I don't know if anyone can help me,

I've got roughly 25 forms in a site, with between 10 and 70 fields on
each form. Now, these fields need to be inserted into a DB,
What DB? Type and version please. It's almost always relevant.
each form
has it's own table and each column is named the same as it's
corresponding form field. Make sense?
My question is, is there a quicker way of constructng my SQL UPDATE
query string for all this lot?
These forms will only be used for maintaining existing data? Never for
inserting new rows into your tables?
Is it possible to request all the form
fields from a single page and put them into and array that I can loop
through and construct the SQL query on the fly? Or am I going to have
to do it the hard way and and do a request.form for every form field
and construct each one manually?

So you want to create a single generic form processing page to which all
your pages submit their data?
If so, how will the destination table be identified? A hidden form field? In
the querystring?

Or are you planning to put this code into an include file used in the pages
for each of these forms in which the table names are hard-coded? I will
assume this is the case. Something like this:

<!--#include file="UpdateCod e.asp"-->
<%
on error resume next
'PerformUpdate refers to a sub contained in UpdateCode.asp
PerformUpdate(" sometable")
if err <> 0 then
'notify user that an error occurred
'optionally. log the error somewhere for debugging purposes
'I would not tell the user the actual error - hackers can make
'use of information provided in error messages
else
'notify user that update was successful
end if
%>
I've had a quick google round but can't really find anything that
answers my question,

Maybe I'm just being lazy, but any tips would be really appreciated!
You are doing yourself and perhaps your employer a disservice by not giving
enough attention to security here. It sounds like you're just planning to
throw this data into your database without validating it first. This can
lead to problems with data integrity (inadvertant keystrokes by users) as
well as opening the door to malicious users. It's a mistake to blindly
assume that the data in Request.Form is coming from the form that you wrote.
One common hacker exploit is to create a new page containing all the form
elements in your form and use it to submit their own unvalidated data
(assuming your page has some sort of client-side validation built in)

If I was planning to do something like this, I would give some thought to
providing validation instructions for the code to use. You could perhaps
include this information in the form's field names, something like
<input name="fieldname |datatype|notnu ll" ... >

That way, when you loop through the Form collection, you can perform some
basic validation: make sure the value is a date (for date fields) or a
number (for numeric fields); make sure the value is not an empty string for
those fields that don't allow nulls.

An alternative would be to create an xml document containing this
information and store it in a file on the server, or load it into
Application (my preference).

I will assume you will use the form name approach.

Due to the dangers of SQL Injection
(http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDe...aspx?tabid=23), as well as the
issues involving delimiters (I'm assuming that not all of these fields are
numeric datatypes) I would avoid the dynamic sql solution.

My preference would be to create a stored procedure/saved parameter query
(depending on your database) for each table and pass the form values as
parameters. However, if you prefer not to go to the trouble of creating
these procedures, you can use a Command object to pass these values to a
statement containing parameter markers, like this (this sub will be in the
UpdateCode.asp include file):
<%
Sub PerformUpdate(t ablename)
dim sql,cn,cmd,curv al,fieldname,da tatype,required , ar,formitem
dim arParms,msg, i, connectionstrin g

redim arParms(request .form.count - 1)
sql = "Update " & tablename & " Set "
i=0
msg=""
for each formitem in Request.Form
curval=Request. Form(formitem)
ar=Split(formit em, "|")
fieldname=ar(0)
datatype=ar(1)
required=ar(2)
if required = "notnull" then
if len(curval)=0 then
msg=msg & "No data entered for required field - " & _
fieldname & ";"
loop
end if
end if
if len(curval) > 0 then
select case datatype
case "date"
if not isdate(curval) then
msg=msg & curval & " entered in " & fieldname & _
" is not a date;"
end if
case "numeric"
'for the sake of time, i will use IsNumeric() here - I suggest
'you use a more robust method such as the one described
' in http://www.aspfaq.com/show.asp?id=2390
If not isnumeric(curva l) then
msg=msg & curval & " entered in " & fieldname & _
" is not a number;"
end if
end select
end if
if len(msg)=0 then
arParms(i) = curval
sql = sql & fieldname & "=?,"
end if
i=i+1
next
if len(msg) = 0 then
sql=left(sql,le n(sql)-1) ' get rid of the last comma
'add a where clause so the correct record is updated
set cn=createobject ("adodb.connect ion")
cn.open connectionstrin g
set cmd=createobjec t("adodb.comman d")
with cmd
set .ActiveConnecti on=cn
.CommandType=1 'adCmdText
.CommandText=sq l
.Execute ,arParms,128 'adExecuteNoRec ords
end with
cn.close:set cn=nothing
else
err.raise 1 + vbObjectError, msg
end if
End Sub
%>

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Slim wrote:

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:Of******** ******@TK2MSFTN GP11.phx.gbl...

Perhaps I'm missing something :-)
Could you explain why you go to the extra step of transferring the
Form collection to Session?
Also, how will the need for delimiters be handled? I would assume
that not all of these fields are numeric ...

no you don't have to change them to Session, but I already had the
code in one of my pages where I like to put them in session for
persistence of the form.

and yes you can simply test to see they are numeric

No, you missed my point: how will he know WHEN to test whether they are
numeric (or date, or whatever ... )

Bob Barrows
PS. I was just asking for clarification - not criticising your post.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Slim]

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:e4******** *****@TK2MSFTNG P09.phx.gbl...

Slim wrote:

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:Of******** ******@TK2MSFTN GP11.phx.gbl...

Perhaps I'm missing something :-)
Could you explain why you go to the extra step of transferring the
Form collection to Session?
Also, how will the need for delimiters be handled? I would assume
that not all of these fields are numeric ...

no you don't have to change them to Session, but I already had the
code in one of my pages where I like to put them in session for
persistence of the form.

and yes you can simply test to see they are numeric

No, you missed my point: how will he know WHEN to test whether they are
numeric (or date, or whatever ... )

Bob Barrows
PS. I was just asking for clarification - not criticising your post.

fair enough.

But you could take this to ridicules lengths and say how does he know to
give the page .asp extensions, or wrap the script in <%%> tags,

I also posted 2 lines of code on the same line,
coma = "," ' should be on a line by itself
But he seems to have sorted that out by himself.

I guess you need to assume some things, he can always ask for more info

Sometimes it is obvious that the poster has no idea where to start and i
have made the page completely for them, where other times you can obtain
that the poster is quite advanced and a simple line of code is all they
need.

Some times you can even teach a new trick to those more advanced, and learn
something from those less advanced


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Slim wrote:

Bob Barrows
PS. I was just asking for clarification - not criticising your post.

fair enough.

But you could take this to ridicules lengths and say how does he know
to give the page .asp extensions, or wrap the script in <%%> tags,

I totally agree that those would be ridiculous lengths.

You seem to be getting very defensive. There is no need.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"