HELP using dynamic form elements to post multiple INSERTs

buzzter66 · #1 (**permalink**) 03-02-06, 14:11

I am writing a system in ASP VB to manage news stories on a Website. The stories may appear in an unlimited number of multiple categories (for instance, it could be a feature story, a business story, a regional story, an advertiser’s story, etc.). So I’ve got three tables in the MS SQL 2000 database:

Story (contains the story info, headline, byline, etc.)
Category (contains the Category name)
StoryCat (a junction table with only three columns: StoryCatID, StoryID, and CategoryID)

StoryCat, as you can see, creates the many-to-many relationship between the Story and Category tables.

I have created a form for entering new stories. It saves the story data and then takes you to a second page where you select the categories it can use. This second page has the StoryID and a repeating region with the available category names next to a check box. I’d like the user to be able to select the check box and then, when they submit the form, it will post an INSERT create a new record for each of the check boxes they selected.

So, if you decided to check three boxes, it should create three records in the StoryCat table:

Code:

StoryCatID	StoryID		CategoryID
1		91		3
2		91		5
3		91		12

I’m really not sure how to approach this problem because the form elements (check boxes) are dynamic, so I guess I need to loop through them and then execute an INSERT command for each one that’s there? I really have no idea how to start this.

Any help or advice will be GREATLY appreciated!

kropes2001 · #2 (**permalink**) 03-03-06, 01:12

First.
that may be one of the most thorough and descriptive posts I have seen in a while.
THANK YOU !
hehehehe

when you are dynamically CREATING the list of cheek boxes (in the HTML page) give them all names that start off the same, and use the "CategoryID" as part of the name

so you would
1) retrieve a list of all the CategoryID's into a recordset.
2) loop though the recordset
3) create the check box

the lines for the creation of the check boxes would look something like this

Code:

<%
	do while Not RS.eof
	intCatID=RS("CategoryID")
	strCategoryDescription=RS("CategoryDescription")
%>
	<input type="checkbox" name="chkCat<%=intCatID%>" ID="ID<%=intCatID%>">
	<label for="ID<%=intCatID%>"><%=strCategoryDescription%></label><br>
<%
	RS.MoveNext
	loop
	RS.close
%>

this will produce a uniquely named check box for each category. it will also allow you to click on the description to toggle the check box

then when you hit submit for the page, do as you thought and loop through the list...using the FOR EACH loop, looping through the form object
you check all of the fields and only use the ones that start with "chkCat"
you extract the 7th character through the end of the field name as the categoroy ID
then you can do multiple inserts (I am assuming that your StoryCatID is an auto increment,)

Code:

<%
	intStoryID=request.form("StoryID")

	for each field in request.form
		if mid(field,1,6) = "chkCat" AND len(request.form(field)) > 0 then
			intCategoryID=mid(field,7,len(field))
			strSQL = "INSERT INTO StoryCat (StoryID, CategoryID) values ('" & intStoryID & "', '" & intCategoryID & "')"
			db.execute strSQL
		end if
	next

%>

I think that should be about it.

buzzter66 · #3 (**permalink**) 03-03-06, 01:56

Thank you SO MUCH! I will give this a shot tomorrow and see if I can get it working. And you're VERY welcome for the thorough and descriptive post! ;-) I'm not slamming anyone else around here, but sometimes when I read a post I can't even figure out what they are asking about!

And yup, StoryCatID is auto incremented!

I'll let ya know how it goes after I try this out.

Internet Services

Web Development

Digital Marketing

Consumer Tech