Multiple insert - iterate form collection - checkbox name dynamic?

J

jason

How does one loop through the contents of a form complicated by dynamic
construction of checkboxes which are assigned a 'model' and 'listingID' to
the NAME field on the fly in this syntax: Hunter_69. Here is what the form
looks like. I have the difficulty of inserting the multiple items selected
by the user the first time he visits and uses the screen and then using an
UPDATE when he visits later.

Model | Original Price | Reduced Price | Watch Item
F Pajot | 452000 |30000 |[Tick ]
Lagoon | 60000 |38000 |[ ]
Hunter | 552000 |350000 |[ ]
SUBMIT


<FORM action="formhandler_databaseinsert.asp">

<INPUT type="checkbox" id=checkbox1 name=<%=model%>_<%=ListingsID%>>
</form>


....However, I am not sure how to do a multiple update when iterating through
the form collection as I don't know in advance what the NAME field would be
as it is based on the concantenation of the 'Model' and the 'ListingID' eg:
FoutainPajot_33.

How does on tackle mutiple inserts or update when you have to loop through a
form with DYNAMIC names for the eg: Checkbox field?

Many thanks
jason
 
A

Alan

If your listingID is unique for the entities you're listing then you don't
need the 'Hunter' in the control name - you can derive it if necessary in
your form processor. If the listingID isn't unique then your database may
benefit from additional normalisation, or perhaps your application needs a
bit of tweaking. Either way it's not a show-stopper.

Anyway, the trick is to give all your checkboxes the same name, and use the
identifier as the value. Like this (are they cars?):

<FORM action="formhandler_databaseinsert.asp">
<INPUT type="checkbox" name="CarIDs" value="<%=model%>_<%=ListingsID%>">
</FORM>

When you do a Request.Form("CarIDs") in the processor you'll get a comma
separated list of the zero or more options that were selected.
Request.Form("CarIDs") = "Hunter_69, Lagoon_70" for example.

Split this string on a ',' separator, trim each term, and then split each
term on the '_' to get your original make and listingID back again. You can
now do your insert.

Only those items that were selected on the form will be passed through in
the form collection to the processor.

Try that.

Alan




Two things
 
J

jason

Hey Alan - that works like an absolute dream - I was really struggling with
iterating that form collection on the fly and this really helps me - thanks.

If I wanted to make my code more streamlined could I place the insert
section into a sub or function (shaky on these) in this manner? Is this
correct?

CustomerID=Session("CustomerID") '//should this part go in sub?
ListingsID = Request.Form("ListingsID") ' will contain "69, 71" ' - should
this part go in sub to?

Sub insertPageWatch(CustomerID, ListingsID)
aListingIDs = Split(ListingsID, ",") ' will contain ("69", " 71")

For Each ListingsID In aListingIDs

SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID)
VALUES ("
SQL=SQL & "'" & Trim(ListingsID) & "', "
SQL=SQL & "'" & CustomerID & "')"
'//Response.Write sql
cnn.Execute SQL, , adCmdText
Next
End

Call insertPageWatch


Alan said:
Lets assume that your numeric IDs are unique and you don't need the model
prefix. I've removed the ASP tags but you should get the idea - if your form
now looks like this:

<form name="..." method="..." action="...">
<input type="checkbox" name="ListingIDs" value="69">
<input type="checkbox" name="ListingIDs" value="70">
<input type="checkbox" name="ListingIDs" value="71">
</form>

...and you select just the first and third items (69 and 71)...

Your processor will look like this (you can optimize the code if you want):

Dim ListingIDs
Dim aListingIDs
Dim ListingID
Dim CustomerID

CustomerID = Session("CustomerID")

ListingIDs = Request.Form("ListingIDs") ' will contain "69, 71"
aListingIDs = Split (ListingIDs, ",") ' will contain ("69", " 71")

For Each ListingID In aListingIDs

' Insert the value.

SQL = "INSERT INTO tblPageWatch (ListingID, CustomerID) VALUES ("
SQL=SQL & "'" & Trim(ListingID) & "', "
SQL=SQL & "'" & CustomerID & "')"

cnn.Execute SQL, , adCmdText

Next

Notice the Trim() on the ListingID - from memory multiple values passed for
the same named control will be delimited with a comma and space (', '). You
could also Split() on a comma-and-space instead of just the comma if you
want.

One other thing, you don't need the Recordset that the INSERT returns - you
can use what I've shown above instead.

Hope this helps a bit.

Alan



jason said:
Alan I took your advice and removed the "model" and "_" from the NAME
syntax. Thus, the NAME will always be numeric. But I don't know how to
seperate the numeric items from the text items. This is what I have so far
but it is not doing the job. How would I extract the checkboxes if I did
decide to prefix the form items:
"chk_" ....Thanks Jason

For lngCnt=1 to Request.Form.Count

If Request.Form.key(lngCnt) <> "submit1" or
IsNumeric(Request.Form.key(lngCnt))then


ListingsID = Request.Form.Key(lngCnt)

SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
SQL=SQL & "'" & ListingsID & "', "
SQL=SQL & "'" & Session("CustID") & "')"
Response.Write SQL

Set rs = cnn.Execute(SQL)

End if
Next
needs
a
bit of tweaking. Either way it's not a show-stopper.

Anyway, the trick is to give all your checkboxes the same name, and
use
the
identifier as the value. Like this (are they cars?):

<FORM action="formhandler_databaseinsert.asp">
<INPUT type="checkbox" name="CarIDs"
value= said:
</FORM>

When you do a Request.Form("CarIDs") in the processor you'll get a comma
separated list of the zero or more options that were selected.
Request.Form("CarIDs") = "Hunter_69, Lagoon_70" for example.

Split this string on a ',' separator, trim each term, and then split each
term on the '_' to get your original make and listingID back again.
You
can
now do your insert.

Only those items that were selected on the form will be passed through in
the form collection to the processor.

Try that.

Alan




Two things
How does one loop through the contents of a form complicated by dynamic
construction of checkboxes which are assigned a 'model' and
'listingID'
to
the NAME field on the fly in this syntax: Hunter_69. Here is what
the
form
looks like. I have the difficulty of inserting the multiple items
selected
by the user the first time he visits and uses the screen and then
using
an
UPDATE when he visits later.

Model | Original Price | Reduced Price | Watch Item
F Pajot | 452000 |30000 |[Tick ]
Lagoon | 60000 |38000 |[ ]
Hunter | 552000 |350000 |[ ]
SUBMIT


<FORM action="formhandler_databaseinsert.asp">

<INPUT type="checkbox" id=checkbox1 name=<%=model%>_<%=ListingsID%>>
</form>


...However, I am not sure how to do a multiple update when iterating
through
the form collection as I don't know in advance what the NAME field would
be
as it is based on the concantenation of the 'Model' and the 'ListingID'
eg:
FoutainPajot_33.

How does on tackle mutiple inserts or update when you have to loop through
a
form with DYNAMIC names for the eg: Checkbox field?

Many thanks
jason
 
A

Alan

Yep, that should work. I normally keep all my Subs and Functions together
after all of the 'immediate' code so you might move your Sub to somewhere
after the Call.
CustomerID=Session("CustomerID") '//should this part go in sub?
ListingsID = Request.Form("ListingsID") ' will contain "69, 71" ' -
should

Because your CustomerID and ListingsID vars are populated from intrinsic
objects (which have global scope) you _could_ put these parts in the Sub,
but if you do then your Sub can only be used for this one specific purpose.
If a page uses values like these more than once I tend to retrieve them at
the top of the script and pass them around. It makes your code more
maintainable - if you change your persistence model from, say, using the
Session, to using a database, you only have to change one reference to point
to the new location of a variable rather than hunting through all your code.

On a stylistic note you might consider leaving the loop in the main page
code and instead putting just the insert in a Sub. This is because the loop
code is very specific and it doesn't add any value to separate it out.
Here's another approach:

' Get passed in variable values.
CustomerID=Session("CustomerID") '//should this part go in sub?
ListingsID = Request.Form("ListingsID") ' will contain "69, 71"

' Split the listings string into an array of listing IDs.
aListingIDs = Split(ListingsID, ",") ' will contain ("69", " 71")

' Iterate through the array of listing IDs.
For Each ListingsID In aListingIDs

' Insert a listingID for a CustomerID.
insertPageWatch ListingsID, CustomerID

Next

' Inserts a Listing record into the PageWatch table.
Sub insertPageWatch (CustomerID, ListingsID)

Dim SQL

' Create the SQL string.
SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID)
VALUES ("
SQL=SQL & "'" & Trim(ListingsID) & "', "
SQL=SQL & "'" & CustomerID & "')"
'//Response.Write sql

' Execute the SQL.
cnn.Execute SQL, , adCmdText

End Sub

See how you go.

Alan

jason said:
Hey Alan - that works like an absolute dream - I was really struggling with
iterating that form collection on the fly and this really helps me - thanks.

If I wanted to make my code more streamlined could I place the insert
section into a sub or function (shaky on these) in this manner? Is this
correct?

CustomerID=Session("CustomerID") '//should this part go in sub?
ListingsID = Request.Form("ListingsID") ' will contain "69, 71" ' - should
this part go in sub to?

Sub insertPageWatch(CustomerID, ListingsID)
aListingIDs = Split(ListingsID, ",") ' will contain ("69", " 71")

For Each ListingsID In aListingIDs

SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID)
VALUES ("
SQL=SQL & "'" & Trim(ListingsID) & "', "
SQL=SQL & "'" & CustomerID & "')"
'//Response.Write sql
cnn.Execute SQL, , adCmdText
Next
End

Call insertPageWatch


Alan said:
Lets assume that your numeric IDs are unique and you don't need the model
prefix. I've removed the ASP tags but you should get the idea - if your form
now looks like this:

<form name="..." method="..." action="...">
<input type="checkbox" name="ListingIDs" value="69">
<input type="checkbox" name="ListingIDs" value="70">
<input type="checkbox" name="ListingIDs" value="71">
</form>

...and you select just the first and third items (69 and 71)...

Your processor will look like this (you can optimize the code if you want):

Dim ListingIDs
Dim aListingIDs
Dim ListingID
Dim CustomerID

CustomerID = Session("CustomerID")

ListingIDs = Request.Form("ListingIDs") ' will contain "69, 71"
aListingIDs = Split (ListingIDs, ",") ' will contain ("69", " 71")

For Each ListingID In aListingIDs

' Insert the value.

SQL = "INSERT INTO tblPageWatch (ListingID, CustomerID) VALUES ("
SQL=SQL & "'" & Trim(ListingID) & "', "
SQL=SQL & "'" & CustomerID & "')"

cnn.Execute SQL, , adCmdText

Next

Notice the Trim() on the ListingID - from memory multiple values passed for
the same named control will be delimited with a comma and space (', '). You
could also Split() on a comma-and-space instead of just the comma if you
want.

One other thing, you don't need the Recordset that the INSERT returns - you
can use what I've shown above instead.

Hope this helps a bit.

Alan
VALUES
("
SQL=SQL & "'" & ListingsID & "', "
SQL=SQL & "'" & Session("CustID") & "')"
Response.Write SQL

Set rs = cnn.Execute(SQL)

End if
Next

If your listingID is unique for the entities you're listing then you don't
need the 'Hunter' in the control name - you can derive it if
necessary
in
your form processor. If the listingID isn't unique then your
database
may
benefit from additional normalisation, or perhaps your application
needs
a
bit of tweaking. Either way it's not a show-stopper.

Anyway, the trick is to give all your checkboxes the same name, and use
the
identifier as the value. Like this (are they cars?):

<FORM action="formhandler_databaseinsert.asp">
<INPUT type="checkbox" name="CarIDs"
value= said:
</FORM>

When you do a Request.Form("CarIDs") in the processor you'll get a comma
separated list of the zero or more options that were selected.
Request.Form("CarIDs") = "Hunter_69, Lagoon_70" for example.

Split this string on a ',' separator, trim each term, and then split each
term on the '_' to get your original make and listingID back again. You
can
now do your insert.

Only those items that were selected on the form will be passed
through
in
the form collection to the processor.

Try that.

Alan




Two things
How does one loop through the contents of a form complicated by dynamic
construction of checkboxes which are assigned a 'model' and 'listingID'
to
the NAME field on the fly in this syntax: Hunter_69. Here is what the
form
looks like. I have the difficulty of inserting the multiple items
selected
by the user the first time he visits and uses the screen and then using
an
UPDATE when he visits later.

Model | Original Price | Reduced Price | Watch Item
F Pajot | 452000 |30000 |[Tick ]
Lagoon | 60000 |38000 |[ ]
Hunter | 552000 |350000 |[ ]
SUBMIT


<FORM action="formhandler_databaseinsert.asp">

<INPUT type="checkbox" id=checkbox1
name= said:
</form>


...However, I am not sure how to do a multiple update when iterating
through
the form collection as I don't know in advance what the NAME field would
be
as it is based on the concantenation of the 'Model' and the 'ListingID'
eg:
FoutainPajot_33.

How does on tackle mutiple inserts or update when you have to loop
through
a
form with DYNAMIC names for the eg: Checkbox field?

Many thanks
jason
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top