Submit Form Elements to Insert Multiple Records into a Database

Discussion in 'ASP General' started by Sans Spam, May 12, 2004.

  1. Sans Spam

    Sans Spam Guest

    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!!!
     
    Sans Spam, May 12, 2004
    #1
    1. Advertising

  2. "Sans Spam" <> wrote in message
    news:...
    > 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
     
    Chris Hohmann, May 12, 2004
    #2
    1. Advertising

  3. Sans Spam

    Sans Spam Guest

    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" <> wrote in message news:<efYN#>...
    > "Sans Spam" <> wrote in message
    > news:...
    > > 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, May 13, 2004
    #3
  4. 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.
     
    Bob Barrows [MVP], May 13, 2004
    #4
  5. "Sans Spam" <> wrote in message
    news:...
    > 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, May 13, 2004
    #5
  6. Sans Spam

    Jeff Cochran Guest

    On Thu, 13 May 2004 13:36:15 -0400, "Bob Barrows [MVP]"
    <> 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
     
    Jeff Cochran, May 14, 2004
    #6
  7. Sans Spam

    Sans Spam Guest

    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" <> wrote in message news:<>...
    > "Sans Spam" <> wrote in message
    > news:...
    > > 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. :)
     
    Sans Spam, May 14, 2004
    #7
  8. "Sans Spam" <> wrote in message
    news:...
    > 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
     
    Chris Hohmann, May 15, 2004
    #8
  9. Sans Spam

    Sans Spam Guest

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

    "Chris Hohmann" <> wrote in message news:<>...
    > "Sans Spam" <> wrote in message
    > news:...
    > > 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, May 24, 2004
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew Bourne

    Insert records into database based on query

    Andrew Bourne, Oct 17, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    526
    Andrew Bourne
    Oct 19, 2003
  2. Ian Macey
    Replies:
    1
    Views:
    915
    =?Utf-8?B?U2NvdHQgU2ltb25z?=
    Jan 25, 2005
  3. Replies:
    1
    Views:
    1,826
    Albert Hopkins
    Dec 6, 2008
  4. kate
    Replies:
    0
    Views:
    3,083
  5. J.D. Buehls
    Replies:
    0
    Views:
    125
    J.D. Buehls
    Jun 18, 2004
Loading...

Share This Page