insert multiple records to table

Discussion in 'ASP General' started by Roy Adams, May 26, 2004.

  1. Roy Adams

    Roy Adams Guest

    Hi forum
    I have three text fields set up on a page that when submitted I want
    them to be inserted in to a table but in differnt records at once.
    the code below only seems to insert the last field

    <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    <%

    var colorForm = String(Request.Form("color"));
    colorForm_array = colorForm.split(",");

    if( colorForm != "" ){
    //make the sql connection object and open it here
    conn = Server.CreateObject('ADODB.Command');
    conn.ActiveConnection = "dsn=Mydsn;";

    for( i=0 ; i < colorForm_array.length ; i ++){

    conn.CommandText = ("insert into color (color) values ('" +
    colorForm_array + "')" );
    }
    conn.Execute();
    conn.ActiveConnection.Close();


    }

    %>

    all there is in the form are 3 text fields with the same name "color"
    and a submit button

    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html;
    charset=iso-8859-1">
    </head>

    <body>
    <form name="form1" action="" method="post">
    <table width="600" border="0" align="center" cellpadding="4"
    cellspacing="2">
    <tr>
    <td width="300">&nbsp;</td>
    <td width="278">&nbsp;</td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td><input name="color" type="text" id="color"></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td><input name="color" type="text" id="color"></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td><input name="color" type="text" id="color"></td>
    </tr>
    <tr>
    <td colspan="2"> <div align="center">
    <input type="submit" name="Submit" value="Submit">
    </div></td>
    </tr>
    </table>
    </form>
    </body>
    </html>

    I'm quite new to this so any help is appriciated!
    Thanks
     
    Roy Adams, May 26, 2004
    #1
    1. Advertising

  2. Roy Adams wrote:
    > Hi forum
    > I have three text fields set up on a page that when submitted I want
    > them to be inserted in to a table but in differnt records at once.
    > the code below only seems to insert the last field
    >
    > <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    > <%
    >
    > var colorForm = String(Request.Form("color"));
    > colorForm_array = colorForm.split(",");
    >
    > if( colorForm != "" ){
    > //make the sql connection object and open it here
    > conn = Server.CreateObject('ADODB.Command');
    > conn.ActiveConnection = "dsn=Mydsn;";
    >
    > for( i=0 ; i < colorForm_array.length ; i ++){
    >
    > conn.CommandText = ("insert into color (color) values ('" +
    > colorForm_array + "')" );
    > }
    > conn.Execute();
    > conn.ActiveConnection.Close();
    >


    You need to execute the statement within the loop, not outside:

    for( i=0 ; i < colorForm_array.length ; i ++){

    conn.CommandText = ("insert into color (color) values ('" +
    colorForm_array + "')" );
    conn.Execute(,,129);
    conn.ActiveConnection.Close();
    }

    However, I would prefer to make a single call to the database, like this:

    var sSQL = "insert into color (color) select'" + colorForm_array[0] + "'"
    for( i=1 ; i < colorForm_array.length ; i ++){
    sSQL += " union all select '" + colorForm_array + "'"
    }
    //for debugging:
    Response.write(sSQL)
    conn.Execute(,,129);
    conn.ActiveConnection.Close();


    HTH,
    Bob Barrows
    PS. The "129" is the addition of two settings: 1 and 128. "1" sets the
    command type to adCmdText, meaning that you are executing
    a string containing a sql statement. The "128" sets the execution
    type to adExecuteNoRecords, which is self-explanatory.
    "129" combines them, so you have the effect of telling ADO
    that you are executing a string containing a sql statement that
    does not return any records - the latter is important, without
    it, ADO automatically creates a recordset behind-the-scenes,
    which is a waste of resources if you're never going to look at
    that recordset.

    --
    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 26, 2004
    #2
    1. Advertising

  3. Roy Adams

    Roy Guest

    Thanks for the reply bob, although i did work it out just before i got
    your reply,
    but the way that you tweak my code works well
    the problem I'm hving now though is if a text field is empty it still
    adds a record, do you know of a way around this?
    Just when I thought it was over:(

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Roy, May 26, 2004
    #3
  4. Roy wrote:
    > Thanks for the reply bob, although i did work it out just before i got
    > your reply,
    > but the way that you tweak my code works well
    > the problem I'm hving now though is if a text field is empty it still
    > adds a record, do you know of a way around this?
    > Just when I thought it was over:(

    Use an If statement in the loop to check the content of the textbox.

    Bob Barrows
    --
    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 26, 2004
    #4
    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. Michael Ramey

    Re: insert multiple records using asp.net

    Michael Ramey, Jan 30, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    2,712
    Michael Ramey
    Jan 30, 2004
  2. Vijay Pathak via .NET 247

    Insert Multiple Records at a time based on checkbox selection

    Vijay Pathak via .NET 247, Sep 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    577
    Vijay Pathak via .NET 247
    Sep 22, 2004
  3. Ian Macey
    Replies:
    1
    Views:
    907
    =?Utf-8?B?U2NvdHQgU2ltb25z?=
    Jan 25, 2005
  4. Replies:
    1
    Views:
    1,798
    Albert Hopkins
    Dec 6, 2008
  5. shank

    insert multiple records

    shank, Feb 21, 2004, in forum: ASP General
    Replies:
    10
    Views:
    289
    Chris Barber
    Feb 22, 2004
Loading...

Share This Page