473,856 Members | 1,720 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Submit Form Elements to Insert Multiple Records into a Database

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!!!
Jul 19 '05 #1
8 5525
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** ***@posting.goo gle.com...
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
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.
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

Jul 19 '05 #2
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" <no****@thankyo u.com> wrote in message news:<ef******* *******@TK2MSFT NGP09.phx.gbl>. ..
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** ***@posting.goo gle.com...
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
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.
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

Jul 19 '05 #3
Sans Spam wrote:
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.


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.
Jul 19 '05 #4
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** ***@posting.goo gle.com...
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??


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. :)

Jul 19 '05 #5
On Thu, 13 May 2004 13:36:15 -0400, "Bob Barrows [MVP]"
<re******@NOyah oo.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
Jul 19 '05 #6
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" <no****@thankyo u.com> wrote in message news:<ON******* *******@TK2MSFT NGP12.phx.gbl>. ..
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** ***@posting.goo gle.com...
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??


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. :)

Jul 19 '05 #7
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** **@posting.goog le.com...
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??


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






Jul 19 '05 #8
Thanks for all of your help Chris! I really appreciate your quick
responses and functionality solutions!

"Chris Hohmann" <no****@thankyo u.com> wrote in message news:<et******* *******@TK2MSFT NGP11.phx.gbl>. ..
"Sans Spam" <sa*******@yaho o.com> wrote in message
news:18******** *************** **@posting.goog le.com...
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??


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

Jul 19 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3245
by: shank | last post by:
I have a recordset that contains multiple records of product a user is purchasing. For clarity, I converted the recordset fields to variables. I need to take that entire recordset and insert it into another table on a remote server. The below code only inserts 1 record. How do I change the code to get all records inserted? thanks! <% Dim DataConn2 Set DataConn2 = Server.CreateObject("ADODB.Connection")
5
8457
by: rjames.clarke | last post by:
I have the following. $result=mysql_query($sql); $nrows=mysql_num_rows($result); for ($i=0;$i<$nrows;$i++) { $row_array=mysql_fetch_row($result); echo "<form name='testform' action='ins_op.php' method='post'>"; lots of form stuff
5
5909
by: Codeman II | last post by:
Hi there, I am building a form where the user must upload a picture and fill in his details. Now I have a problem as all of this is on the same form. How will I be able to have the Browse button to open the "file browse" dialog and the Submit button to submit the form data.
25
10289
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
4
2210
by: Mike Hnatt | last post by:
My goal is to get data from an XML file into a couple of tables in an Access database. The XML file is a little complex so I need control over what I do (I can't just read it into a dataset). The way I have it now is ennumerating through my XML file and for each record, running an INSERT INTO SQL statement to put the values in the database. Is this going to be okay with 50 or so records (hitting the database with 50 or so INSERT...
3
2572
by: D. Shane Fowlkes | last post by:
Sorry for the length of this post. I have created a rather complex form which has a header/line item (parent and child records) structure. It's for an intranet. A screenshot can be seen here: http://www.drpt.virginia.gov/temp1.gif All the fields on this form have validation controls on them so they can not submit the form unless all fields are completed and some fall within a specified numeric range. When the form first is loaded,...
6
5009
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a PDF. It works fine so far....
7
3543
by: bcap | last post by:
hi, I am trying to create a form where you may have more than one person at a meeting, but want to have them be related to the same meeting. I have a mulitple select text area and if you select more than one, all the records are being added to the same row. so if I picked the following three people: (Person ID/Desc)
5
10327
by: Rider | last post by:
Hi All, Here is the reason why i ak asking for ur help. I have a edit form in which the values already stored in DB are populated. User can edit some or all the values in the form. then he submit the form. NOW I WANT SOME JAVASCRIPT / PHP FUNCTIONALITY WHICH WILL GIVE ME THE types & values OF ALL ELEMENTS ON FORM WHICH SHOULD BE before
0
9908
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10695
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10777
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7930
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7090
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5759
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4172
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3198
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.