Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Select box multiple SQL insert

I have a multiple select box in a form. I know that when this is submitted, it producers a string with all the selected values separated by commas. What I need to do is beable to take that string, no matter how many values it has, and insert a new row in to my table for each comma delimited item. (I am useing ASP classic and SQL Server 2000.) Here is a quick example of what I need:

1. User selects Widget A, Widget B, and Widget C from the select box and hits submit.

2. A string is passed to the next page that equals "Widget A, Widget B, Widget C"

3. I need to insert 3 new records into my SQL table, one for each item.

Of course, in my real world example, the user can, and most likely will, select a couple hundred items at once.

Any help in this matter would be greatly appreciated.

Bob Gibilaro

Comments

  • bosrbosr Posts: 13Member
    Parse your values into an array.
    Open a recordset using BatchOptimistic as the Lock Type.
    Now loop through the array and add each item to your recordset.
    Finally call the UpdateBatch method on the recordset to commit to DB.

    Dim SQL, RS, i

    SQL = "SELECT * FROM yourTable WHERE 1 = 0" ' returns an empty recordset.

    Set RS = Server.CreateObject("ADODB.Recordset")

    With RS

    .Open SQL, objConn, 1, 4

    For i = lbound(arrValues) To ubound(arrValues)
    .AddNew
    .Fields("FieldName") = arrValues(i)
    next

    .UpdateBatch

    End With

    RS.Close
    Set RS = Nothing


    : I have a multiple select box in a form. I know that when this is submitted, it producers a string with all the selected values separated by commas. What I need to do is beable to take that string, no matter how many values it has, and insert a new row in to my table for each comma delimited item. (I am useing ASP classic and SQL Server 2000.) Here is a quick example of what I need:
    :
    : 1. User selects Widget A, Widget B, and Widget C from the select box and hits submit.
    :
    : 2. A string is passed to the next page that equals "Widget A, Widget B, Widget C"
    :
    : 3. I need to insert 3 new records into my SQL table, one for each item.
    :
    : Of course, in my real world example, the user can, and most likely will, select a couple hundred items at once.
    :
    : Any help in this matter would be greatly appreciated.
    :
    : Bob Gibilaro
    :

  • gigsvoogigsvoo Posts: 328Member
    Basically, when you do posting of form, all the variables is binded as your said, there is a way to do this w/o passing a array. Every drop-down list has ID and NAME right? Do this on the next processing page:

    Dim cbo1, cbo2, cbo3

    cbo1 = request.Form("c1") 'c1 is the name of the first combo box in the previous page
    cbo2 = request.form("c2")
    ...

    get it? if you are using get method, then change request.form to request.querystring(..)

    Neo Gigs
    http://communities.msn.com.my/VisualBasicwithNeo

  • RadarBobRadarBob Posts: 7Member
    Yes, I believe there is a way to avoid using an array. BTW I'm having a similar problem.

    http://www.programmersheaven.com/c/MsgBoard/read.asp?Board=22&MsgID=133788&Setting=A9999F0001

    And yes, all the selected values come across as a comma delimited list under the same field name. That field has an attribute called "count." In fact all the fields in the response.form have it. The code below will display every value in your Response.Form

    http://www.devguru.com/Technologies/ado/quickref/recordset_fieldscollection.html

    [code]
    for Each Item in Request.Form
    for i=1 to Request.Form(Item).Count
    Response.Write(Item + " = ")
    Response.Write(Request.Form(Item)(i) + "
    ")
    Next
    Next[/code]


    : Basically, when you do posting of form, all the variables is binded as your said, there is a way to do this w/o passing a array. Every drop-down list has ID and NAME right? Do this on the next processing page:
    :
    : Dim cbo1, cbo2, cbo3
    :
    : cbo1 = request.Form("c1") 'c1 is the name of the first combo box in the previous page
    : cbo2 = request.form("c2")
    : ...
    :
    : get it? if you are using get method, then change request.form to request.querystring(..)
    :
    : Neo Gigs
    : http://communities.msn.com.my/VisualBasicwithNeo
    :
    :

Sign In or Register to comment.