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]

Re: Submit Form Elements to Insert Multiple Records into a Database

"Sans Spam" <sans_spam@yaho o.com> wrote in message
news:18909fc8.0 405121231.12c68 335@posting.goo gle.com...[color=blue]
> My question is, how do I submit all this data in a form but yet still
> write 4 INDIVIDUAL records to the database?[/color]

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

INSERT INTO
tblFunctionRigh ts
(
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','eCommer ce',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.
[color=blue]
> 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.[/color]

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]

Re: Submit Form Elements to Insert Multiple Records into a Database

Thanks for your help and feedback Chris! Question about your
response...woul dn'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" <nospam@thankyo u.com> wrote in message news:<efYN#bGOE HA.1456@TK2MSFT NGP09.phx.gbl>. ..[color=blue]
> "Sans Spam" <sans_spam@yaho o.com> wrote in message
> news:18909fc8.0 405121231.12c68 335@posting.goo gle.com...[color=green]
> > My question is, how do I submit all this data in a form but yet still
> > write 4 INDIVIDUAL records to the database?[/color]
>
> This can be accomplished using a UNION subquery as the source for an INSERT
> statement. Something like
>
> INSERT INTO
> tblFunctionRigh ts
> (
> 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','eCommer ce',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.
>[color=green]
> > 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.[/color]
>
> 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[/color]

[Bob Barrows [MVP]]

Re: Submit Form Elements to Insert Multiple Records into a Database

Sans Spam wrote:[color=blue]
> Thanks for your help and feedback Chris! Question about your
> response...woul dn'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.
>[/color]

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','eCommer ce',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]

Re: Submit Form Elements to Insert Multiple Records into a Database

"Sans Spam" <sans_spam@yaho o.com> wrote in message
news:18909fc8.0 405130921.5b435 aa0@posting.goo gle.com...[color=blue]
> Thanks for your help and feedback Chris! Question about your
> response...woul dn'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??[/color]

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_tabl e>

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]

Re: Submit Form Elements to Insert Multiple Records into a Database

On Thu, 13 May 2004 13:36:15 -0400, "Bob Barrows [MVP]"
<reb01501@NOyah oo.SPAMcom> wrote:
[color=blue]
>PS. All queries should be tested and debugged in Query Analyzer before you
>attempt to run them from ASP (or any other client app)[/color]

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]

Re: Submit Form Elements to Insert Multiple Records into a Database

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><%=rsBPSSFu nction("Functio nDesc")%></td>
<input type="hidden" name="FunctionI D"
value="<%=rsBPS SFunction("Func tionID")%>">

<td align="center">
<input type="checkbox"
name="<%=rsBPSS Function("Funct ionID")%>Readab le">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSS Function("Funct ionID")%>Writab le">
</td>

<td align="center">
<input type="checkbox"
name="<%=rsBPSS Function("Funct ionID")%>Update able">
</td>

<td align="center"> <input type="checkbox"
name="<%=rsBPSS Function("Funct ionID")%>Deleta ble">
</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("F unctionID")
"SELECT '" & Request.Form("B PUser") & "','" & strVal & "'," &
Request.Form(st rVal & "Readable") & "," & Request.Form(st rVal &
"Writable") & "," & Request.Form(st rVal & "Updateable ") & ", " &
Request.Form(st rVal & "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" <nospam@thankyo u.com> wrote in message news:<ONrfxNROE HA.1340@TK2MSFT NGP12.phx.gbl>. ..[color=blue]
> "Sans Spam" <sans_spam@yaho o.com> wrote in message
> news:18909fc8.0 405130921.5b435 aa0@posting.goo gle.com...[color=green]
> > Thanks for your help and feedback Chris! Question about your
> > response...woul dn'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??[/color]
>
> 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_tabl e>
>
> 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. :)[/color]

[Chris Hohmann]

Re: Submit Form Elements to Insert Multiple Records into a Database

"Sans Spam" <sans_spam@yaho o.com> wrote in message
news:18909fc8.0 405141259.792da 82@posting.goog le.com...[color=blue]
> 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><%=rsBPSSFu nction("Functio nDesc")%></td>
> <input type="hidden" name="FunctionI D"
> value="<%=rsBPS SFunction("Func tionID")%>">
>
> <td align="center">
> <input type="checkbox"
> name="<%=rsBPSS Function("Funct ionID")%>Readab le">
> </td>
>
> <td align="center">
> <input type="checkbox"
> name="<%=rsBPSS Function("Funct ionID")%>Writab le">
> </td>
>
> <td align="center">
> <input type="checkbox"
> name="<%=rsBPSS Function("Funct ionID")%>Update able">
> </td>
>
> <td align="center"> <input type="checkbox"
> name="<%=rsBPSS Function("Funct ionID")%>Deleta ble">
> </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("F unctionID")
> "SELECT '" & Request.Form("B PUser") & "','" & strVal & "'," &
> Request.Form(st rVal & "Readable") & "," & Request.Form(st rVal &
> "Writable") & "," & Request.Form(st rVal & "Updateable ") & ", " &
> Request.Form(st rVal & "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??[/color]

Dim strVal,sql
For Each strVal In Request.Form("F unctionID")
sql = sql & vbCRLF &_
"UNION ALL SELECT '" &_
Request.Form("B PUser") & "','" &_
strVal & "'," &_
Request.Form(st rVal & "Readable") & "," &_
Request.Form(st rVal & "Writable") & "," &_
Request.Form(st rVal & "Updateable ") & ", " &_
Request.Form(st rVal & "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.Exec ute method is also left as an exercise.

HTH
-Chris Hohmann

[Sans Spam]

Re: Submit Form Elements to Insert Multiple Records into a Database

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

"Chris Hohmann" <nospam@thankyo u.com> wrote in message news:<etHehkgOE HA.3380@TK2MSFT NGP11.phx.gbl>. ..[color=blue]
> "Sans Spam" <sans_spam@yaho o.com> wrote in message
> news:18909fc8.0 405141259.792da 82@posting.goog le.com...[color=green]
> > 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><%=rsBPSSFu nction("Functio nDesc")%></td>
> > <input type="hidden" name="FunctionI D"
> > value="<%=rsBPS SFunction("Func tionID")%>">
> >
> > <td align="center">
> > <input type="checkbox"
> > name="<%=rsBPSS Function("Funct ionID")%>Readab le">
> > </td>
> >
> > <td align="center">
> > <input type="checkbox"
> > name="<%=rsBPSS Function("Funct ionID")%>Writab le">
> > </td>
> >
> > <td align="center">
> > <input type="checkbox"
> > name="<%=rsBPSS Function("Funct ionID")%>Update able">
> > </td>
> >
> > <td align="center"> <input type="checkbox"
> > name="<%=rsBPSS Function("Funct ionID")%>Deleta ble">
> > </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("F unctionID")
> > "SELECT '" & Request.Form("B PUser") & "','" & strVal & "'," &
> > Request.Form(st rVal & "Readable") & "," & Request.Form(st rVal &
> > "Writable") & "," & Request.Form(st rVal & "Updateable ") & ", " &
> > Request.Form(st rVal & "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??[/color]
>
> Dim strVal,sql
> For Each strVal In Request.Form("F unctionID")
> sql = sql & vbCRLF &_
> "UNION ALL SELECT '" &_
> Request.Form("B PUser") & "','" &_
> strVal & "'," &_
> Request.Form(st rVal & "Readable") & "," &_
> Request.Form(st rVal & "Writable") & "," &_
> Request.Form(st rVal & "Updateable ") & ", " &_
> Request.Form(st rVal & "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.Exec ute method is also left as an exercise.
>
> HTH
> -Chris Hohmann[/color]