Submit Form Elements to Insert Multiple Records into a Database

[Sans Spam]

Greetings! I have a table that contains all of the function
permissions within a given application. These functions are different
sections of a site and each has its own permissions (READ, WRITE,
UPDATE, DELETE) which are controlled by a web frontend and the table
records are manipulated to control the permissions.

Example:
The Press Release section record would look like this:
Username: John Doe
Function Name: Press Release
Read: 1
Write: 0
Update: 1
Delete: 0

....that would be one record. Another record would be:

Username: Jane Doe
Function Name: Online Seminars
Read: 1
Write: 1
Update: 1
Delete: 0

So, let's say I want to add a new user named 'John Smith' and I want
to assign all of his permissions. I want to pull all of the functions
(6 in total) and then I insert a new record for each function from the
cumulative form collection. Here's what the interface for adding a new
user would look like in text (Permissions will be READ, WRITE, UPDATE,
DELETE in that order from left to right)

User: John Smith
Press Releases, 1, 0, 1, 0
Online Seminars, 1, 1, 0, 1
eCommerce, 1, 0, 0, 1
Support, 1, 1, 1, 1

I have all of the 1's and 0's denoted in my interface in check boxes
under each respective permission heading.

My question is, how do I submit all this data in a form but yet still
write 4 INDIVIDUAL records to the database? i.e. I want to write 1
record for Press Releases, 1 for Online Seminars, 1 for eCommerce, and
1 for Support. How do I break up the form collection to write 4
individual records??? Each one has a unique Function ID so I can
individually identify them, but I don't know how to parse the form
collection to create 4 different records. Then, in addition, how do I
loop through 4 records to make each of them insert into the database??

I know this thread is incredibly long but if anyone could offer me any
help at all (please submit code examples) I would really appreciate
it.

Thanks in Advance!!!

[Chris Hohmann]

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18**************************@posting.google.c om...

My question is, how do I submit all this data in a form but yet still
write 4 INDIVIDUAL records to the database?
This can be accomplished using a UNION subquery as the source for an INSERT
statement. Something like

INSERT INTO
tblFunctionRights
(
Username,
[Function Name],
[Read],
Write,
[Update],
[Delete]
)
SELECT 'John Smith','Press Releases',1,0,1,0 UNION ALL
SELECT 'John Smith','Online Seminars',1,1,0,1 UNION ALL
SELECT 'John Smith','eCommerce',1,0,0,1 UNION ALL
SELECT 'John Smith','Support',1,1,1,1

You will need to modify the above based on your particular database
structure.
I know this thread is incredibly long but if anyone could offer me any
help at all (please submit code examples) I would really appreciate
it.

It's not so much the length that's an issue. It's the narrative form of your
question. In the future, please provide the following information:
1. Database
2. Version
3. Data Definition Language (ie. CREATE TABLE ...)
4. Sample data
5. Desired output/outcome

Here are some articles:
http://aspfaq.com/etiquette.asp?id=5006
http://aspfaq.com/etiquette.asp?id=5009

HTH
-Chris Hohmann

[Sans Spam]

Thanks for your help and feedback Chris! Question about your
response...wouldn't your solution just append each set of values to
each other causing the insert statement to feed in one long string of
the cumulative values??? I want to process those 4 strings as each
being a separate record. It just looks like your solution puts all of
the values into one INSERT statement. How does SQL interpret to end
one insert statement and then start a new one with the below solution
you proposed??

Also, thanks for the heads up on the etiquette as that is very
helpful...and it will probably get my questions answered more quickly.

BTW, I'm using SQL Server 2000 for my database platform.

Thanks!
"Chris Hohmann" <no****@thankyou.com> wrote in message news:<ef**************@TK2MSFTNGP09.phx.gbl>...

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18**************************@posting.google.c om...

My question is, how do I submit all this data in a form but yet still
write 4 INDIVIDUAL records to the database?

This can be accomplished using a UNION subquery as the source for an INSERT
statement. Something like

INSERT INTO
tblFunctionRights
(
Username,
[Function Name],
[Read],
Write,
[Update],
[Delete]
)
SELECT 'John Smith','Press Releases',1,0,1,0 UNION ALL
SELECT 'John Smith','Online Seminars',1,1,0,1 UNION ALL
SELECT 'John Smith','eCommerce',1,0,0,1 UNION ALL
SELECT 'John Smith','Support',1,1,1,1

You will need to modify the above based on your particular database
structure.

I know this thread is incredibly long but if anyone could offer me any
help at all (please submit code examples) I would really appreciate
it.

It's not so much the length that's an issue. It's the narrative form of your
question. In the future, please provide the following information:
1. Database
2. Version
3. Data Definition Language (ie. CREATE TABLE ...)
4. Sample data
5. Desired output/outcome

Here are some articles:
http://aspfaq.com/etiquette.asp?id=5006
http://aspfaq.com/etiquette.asp?id=5009

HTH
-Chris Hohmann

[Bob Barrows [MVP]]

Sans Spam wrote:

Thanks for your help and feedback Chris! Question about your
response...wouldn't your solution just append each set of values to
each other causing the insert statement to feed in one long string of
the cumulative values??? I want to process those 4 strings as each
being a separate record. It just looks like your solution puts all of
the values into one INSERT statement. How does SQL interpret to end
one insert statement and then start a new one with the below solution
you proposed??

Also, thanks for the heads up on the etiquette as that is very
helpful...and it will probably get my questions answered more quickly.

BTW, I'm using SQL Server 2000 for my database platform.

In that case, open Query Analyzer and paste the union query in so you can
run it to see the result:

SELECT 'John Smith','Press Releases',1,0,1,0 UNION ALL
SELECT 'John Smith','Online Seminars',1,1,0,1 UNION ALL
SELECT 'John Smith','eCommerce',1,0,0,1 UNION ALL
SELECT 'John Smith','Support',1,1,1,1

Four SELECT statements will result in four rows in your resultset.

Bob Barrows

PS. All queries should be tested and debugged in Query Analyzer before you
attempt to run them from ASP (or any other client app)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Chris Hohmann]

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18**************************@posting.google.c om...

Thanks for your help and feedback Chris! Question about your
response...wouldn't your solution just append each set of values to
each other causing the insert statement to feed in one long string of
the cumulative values??? I want to process those 4 strings as each
being a separate record. It just looks like your solution puts all of
the values into one INSERT statement. How does SQL interpret to end
one insert statement and then start a new one with the below solution
you proposed??

The INSERT statement has two* forms.

The first to insert a single row of data:
INSERT INTO <table_name> (<column_list>) VALUES (<value_list>)

The second to insert multiple rows of data:
INSERT INTO <table_name> (<column_list>) <derived_table>

The derived_table is any SQL statement that returns a dataset that matches
the column_list in terms of number of columns, order and data types. In this
case the derived table was a UNION query. Try cutting-and-pasting just the
UNION query portion of the INSERT statement into Query Analyser. You'll see
that it returns a dataset with four rows.

HTH
-Chris Hohmann

*Note: Actually in MS SQL Server 2000, there are three forms for the INSERT
statement. Looking up the third form in Books Online (BOL) is left as an
exercise for the reader. :)

[Jeff Cochran]

On Thu, 13 May 2004 13:36:15 -0400, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:

PS. All queries should be tested and debugged in Query Analyzer before you
attempt to run them from ASP (or any other client app)

I'm going to hang a 6 foot poster of that saying in the hall outside
one of our developer's office... :)

Jeff

[Sans Spam]

Thanks Chris! Everything worked correctly in Query Analyzer. My last
hurdle that I need to get over is how to take the dynamic form
collection and loop through it so that I can write my INSERT statement
using the data from the form collection. It's not as easy as I
thought. Here's what I'm currently doing to display the form:
<%
If not rsBPSSFunction.EOF then
rsBPSSFunction.MoveFirst
while not rsBPSSFunction.EOF
%>

<tr>
<td><%=rsBPSSFunction("FunctionDesc")%></td>
<input type="hidden" name="FunctionID"
value="<%=rsBPSSFunction("FunctionID")%>">

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Readable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Writable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Updateable" >
</td>

<td align="center"><input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Deletable">
</td>

</tr>

<%
rsBPSSFunction.MoveNext
wend
Else
%>
<h3>No Records were Found!</h3>
<% End If %>

When the form is submitted I have this For Each Next Loop to process
all the function records that were created in the dynamic form
(functions were dynamically pulled from a ref table). Below, I loop
through the form collection based on the Function ID to gather all the
functions, plus the permissions selected for each one. I can do that
just fine with the code below.

For Each strVal In Request.Form("FunctionID")
"SELECT '" & Request.Form("BPUser") & "','" & strVal & "'," &
Request.Form(strVal & "Readable") & "," & Request.Form(strVal &
"Writable") & "," & Request.Form(strVal & "Updateable") & ", " &
Request.Form(strVal & "Deletable")
next
Here's my problem...How do I write my INSERT statement to append the
UNION ALL statements?? I'm formatting them correctly but I don't know
how to append the results of my For Each Next Loop (the 4 functions
with their permissions as selected by the user). I keep getting syntax
errors. If I can just append those SELECT statements successfully to
my INSERT statement within my ADO code, I should be OK. Also, is there
an easier way to do this than what I'm trying to do??

Thanks in Advance!

"Chris Hohmann" <no****@thankyou.com> wrote in message news:<ON**************@TK2MSFTNGP12.phx.gbl>...

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18**************************@posting.google.c om...

Thanks for your help and feedback Chris! Question about your
response...wouldn't your solution just append each set of values to
each other causing the insert statement to feed in one long string of
the cumulative values??? I want to process those 4 strings as each
being a separate record. It just looks like your solution puts all of
the values into one INSERT statement. How does SQL interpret to end
one insert statement and then start a new one with the below solution
you proposed??

The INSERT statement has two* forms.

The first to insert a single row of data:
INSERT INTO <table_name> (<column_list>) VALUES (<value_list>)

The second to insert multiple rows of data:
INSERT INTO <table_name> (<column_list>) <derived_table>

The derived_table is any SQL statement that returns a dataset that matches
the column_list in terms of number of columns, order and data types. In this
case the derived table was a UNION query. Try cutting-and-pasting just the
UNION query portion of the INSERT statement into Query Analyser. You'll see
that it returns a dataset with four rows.

HTH
-Chris Hohmann

*Note: Actually in MS SQL Server 2000, there are three forms for the INSERT
statement. Looking up the third form in Books Online (BOL) is left as an
exercise for the reader. :)

[Chris Hohmann]

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18*************************@posting.google.co m...

Thanks Chris! Everything worked correctly in Query Analyzer. My last
hurdle that I need to get over is how to take the dynamic form
collection and loop through it so that I can write my INSERT statement
using the data from the form collection. It's not as easy as I
thought. Here's what I'm currently doing to display the form:
<%
If not rsBPSSFunction.EOF then
rsBPSSFunction.MoveFirst
while not rsBPSSFunction.EOF
%>

<tr>
<td><%=rsBPSSFunction("FunctionDesc")%></td>
<input type="hidden" name="FunctionID"
value="<%=rsBPSSFunction("FunctionID")%>">

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Readable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Writable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Updateable" >
</td>

<td align="center"><input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Deletable">
</td>

</tr>

<%
rsBPSSFunction.MoveNext
wend
Else
%>
<h3>No Records were Found!</h3>
<% End If %>

When the form is submitted I have this For Each Next Loop to process
all the function records that were created in the dynamic form
(functions were dynamically pulled from a ref table). Below, I loop
through the form collection based on the Function ID to gather all the
functions, plus the permissions selected for each one. I can do that
just fine with the code below.

For Each strVal In Request.Form("FunctionID")
"SELECT '" & Request.Form("BPUser") & "','" & strVal & "'," &
Request.Form(strVal & "Readable") & "," & Request.Form(strVal &
"Writable") & "," & Request.Form(strVal & "Updateable") & ", " &
Request.Form(strVal & "Deletable")
next
Here's my problem...How do I write my INSERT statement to append the
UNION ALL statements?? I'm formatting them correctly but I don't know
how to append the results of my For Each Next Loop (the 4 functions
with their permissions as selected by the user). I keep getting syntax
errors. If I can just append those SELECT statements successfully to
my INSERT statement within my ADO code, I should be OK. Also, is there
an easier way to do this than what I'm trying to do??

Dim strVal,sql
For Each strVal In Request.Form("FunctionID")
sql = sql & vbCRLF &_
"UNION ALL SELECT '" &_
Request.Form("BPUser") & "','" &_
strVal & "'," &_
Request.Form(strVal & "Readable") & "," &_
Request.Form(strVal & "Writable") & "," &_
Request.Form(strVal & "Updateable") & ", " &_
Request.Form(strVal & "Deletable")
Next
sql = "INSERT INTO ..." & Mid(sql,12)
Response.Write sql
Response.End

Notes:
1. You still haven't provided DDL info, so filling in "INSERT INTO ..." is
left as an exercise.
2. Replacing Response.Write/Response.End with a call to the
Connection.Execute method is also left as an exercise.

HTH
-Chris Hohmann

[Sans Spam]

Thanks for all of your help Chris! I really appreciate your quick
responses and functionality solutions!

"Chris Hohmann" <no****@thankyou.com> wrote in message news:<et**************@TK2MSFTNGP11.phx.gbl>...

"Sans Spam" <sa*******@yahoo.com> wrote in message
news:18*************************@posting.google.co m...

Thanks Chris! Everything worked correctly in Query Analyzer. My last
hurdle that I need to get over is how to take the dynamic form
collection and loop through it so that I can write my INSERT statement
using the data from the form collection. It's not as easy as I
thought. Here's what I'm currently doing to display the form:
<%
If not rsBPSSFunction.EOF then
rsBPSSFunction.MoveFirst
while not rsBPSSFunction.EOF
%>

<tr>
<td><%=rsBPSSFunction("FunctionDesc")%></td>
<input type="hidden" name="FunctionID"
value="<%=rsBPSSFunction("FunctionID")%>">

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Readable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Writable">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Updateable" >
</td>

<td align="center"><input type="checkbox"
name="<%=rsBPSSFunction("FunctionID")%>Deletable">
</td>

</tr>

<%
rsBPSSFunction.MoveNext
wend
Else
%>
<h3>No Records were Found!</h3>
<% End If %>

When the form is submitted I have this For Each Next Loop to process
all the function records that were created in the dynamic form
(functions were dynamically pulled from a ref table). Below, I loop
through the form collection based on the Function ID to gather all the
functions, plus the permissions selected for each one. I can do that
just fine with the code below.

For Each strVal In Request.Form("FunctionID")
"SELECT '" & Request.Form("BPUser") & "','" & strVal & "'," &
Request.Form(strVal & "Readable") & "," & Request.Form(strVal &
"Writable") & "," & Request.Form(strVal & "Updateable") & ", " &
Request.Form(strVal & "Deletable")
next
Here's my problem...How do I write my INSERT statement to append the
UNION ALL statements?? I'm formatting them correctly but I don't know
how to append the results of my For Each Next Loop (the 4 functions
with their permissions as selected by the user). I keep getting syntax
errors. If I can just append those SELECT statements successfully to
my INSERT statement within my ADO code, I should be OK. Also, is there
an easier way to do this than what I'm trying to do??

Dim strVal,sql
For Each strVal In Request.Form("FunctionID")
sql = sql & vbCRLF &_
"UNION ALL SELECT '" &_
Request.Form("BPUser") & "','" &_
strVal & "'," &_
Request.Form(strVal & "Readable") & "," &_
Request.Form(strVal & "Writable") & "," &_
Request.Form(strVal & "Updateable") & ", " &_
Request.Form(strVal & "Deletable")
Next
sql = "INSERT INTO ..." & Mid(sql,12)
Response.Write sql
Response.End

Notes:
1. You still haven't provided DDL info, so filling in "INSERT INTO ..." is
left as an exercise.
2. Replacing Response.Write/Response.End with a call to the
Connection.Execute method is also left as an exercise.

HTH
-Chris Hohmann