Creating a form with multi select

Discussion in 'ASP General' started by bcap, Jul 6, 2007.

  1. bcap

    bcap Guest

    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)
    1 - mickey mouse
    2 - donald duck
    3 - goofy

    The row in the data base would look like this:

    (Meeting ID/ Person ID)

    1 - 1,2,3

    But I would like to do this:
    (Meeting ID/ Person ID)
    1 - 1
    1 - 2
    1 - 3

    I hope this makes sense, if it does does anyone have a suggest on how
    to best do this?
    bcap, Jul 6, 2007
    #1
    1. Advertising

  2. bcap

    bcap Guest

    Hi,

    Thank you, below is my code.

    The multiselect box is working fine. I just can't seem to find a way
    to Add each selected as individuals in my AddNew/Update statament. Do
    I need some kind of loop?



    <FORM NAME="Form2" METHOD="POST" ACTION="AddNewInterview.asp?
    Update=1">

    If request.querystring("Update") = "1" then

    Dim rs
    Set rs=Server.CreateObject("ADODB.Recordset")
    rs.Open "xxx", "dsn=xxx;User ID=xxx;Password=xxx;" ,adOpenKeyset,
    adLockPessimistic
    rs.AddNew

    rs.Fields("PlayerID")=(request.form("PlayerID"))

    rs.Update

    Else
    End If



    ' Multi-Select box

    set connpl = server.createobject("adodb.connection")
    connpl.open "DSN=xxx;user id=xxx;pwd=xxx"

    strSqlpl = "SELECT PlayerID, FirstName, LastName FROM PlayerContact
    ORDER BY LastName"
    set Rspl = server.createobject("adodb.recordset")
    Rspl.open strSqlpl, connpl

    <tr><td>
    <SELECT NAME='PlayerID' MULTIPLE SIZE='15'>

    <%
    Do While Not rspl1.EOF

    Dim Counter
    Counter = Counter +1

    Response.write "<option size='15' name='PID" & counter & "' value='" &
    rspl("PID") & "' style='font-size: 10px;'>" & rspl("LastName") &
    ",&nbsp;" & rspl("FirstName") & "</option>"

    rspl.MoveNext
    Loop
    %>


    </td></tr>
    bcap, Jul 6, 2007
    #2
    1. Advertising

  3. bcap

    bcap Guest

    Hi,

    I have created the following array:

    Dim getPID
    getPID = request.form("PID")

    for i = 0 to getPID

    oRS3.AddNew

    oRS3.Fields("PID")=(request.form("PID" & i))

    oRS3.Update
    Next

    However I am getting this error message:

    Microsoft VBScript runtime error '800a000d'
    Type mismatch: '[string: "51, 69, 132, 112, 10"]'


    Any thoughts? Thanks for all the help this far! =)



    Kind Regards,

    Ray
    bcap, Jul 9, 2007
    #3
  4. bcap

    bcap Guest

    Ok So I working the split. Here is what I have for code:

    Dim MyString, MyArray
    MyString = request.form("PID")
    MyArray = Split(MyString,",") 'the delimiter is the comma

    response.write MyArray


    I am getting this error message:

    Response object error 'ASP 0106 : 80020005'
    Type Mismatch
    An unhandled data type was encountered.

    Thanks for you help, thoughts, and pateince.
    bcap, Jul 9, 2007
    #4
  5. bcap

    bcap Guest

    Hi,

    Thank you very much. I am learning a lot I appecrciate the help.

    I understadn that the Ubound will give the highest array member. When
    I try to update my databse with the multiple records, it is only
    updating with one record ( I believe this is becuase of the Ubound
    fucntion).

    How can I make sure all record get into my db?

    Here is my curent code:

    Dim MyString, MyArray
    MyString = request.form("PID")
    MyArray = Split(MyString,",") 'the delimiter is the comma

    For i = 0 to UBound(MyArray)
    oRS3.Fields("PID")= MyArray(i)
    Next

    oRS3.Update
    bcap, Jul 9, 2007
    #5
  6. bcap wrote:
    > Hi,
    >
    > Thank you very much. I am learning a lot I appecrciate the help.
    >
    > I understadn that the Ubound will give the highest array member. When
    > I try to update my databse with the multiple records, it is only
    > updating with one record ( I believe this is becuase of the Ubound
    > fucntion).
    >
    > How can I make sure all record get into my db?
    >
    > Here is my curent code:
    >
    > Dim MyString, MyArray
    > MyString = request.form("PID")
    > MyArray = Split(MyString,",") 'the delimiter is the comma
    >
    > For i = 0 to UBound(MyArray)
    > oRS3.Fields("PID")= MyArray(i)
    > Next
    >
    > oRS3.Update


    Look at what your code is doing.
    You are looping through the array, setting the value of the PID field in
    the CURRENT record of the recordset to the ith value. No attempt to move
    to the next record in the recordset (we don't even have any idea if your
    recordset even contains multiple records), no attempt to add a new
    record to the recordset ...
    Then after the loop is finished, and PID field in the current record
    contains the last value of the array, you call the Update method. So of
    course, the current record in the database is updated with the last
    value in the array.

    Going back to your first post I see that you want to add a new record
    for each PID. Instead of confusing everone by using the word "update"
    (as in "... try to update my databse with the multiple records..."), you
    should use the word "insert" or even "append". "Update" implies
    modifying existing records.

    I always try to discourage people from using recordsets to maintain
    data, but since you have started ...

    Dim MyString, MyArray
    MyString = request.form("PID")
    MyArray = Split(MyString,",") 'the delimiter is the comma

    For i = 0 to UBound(MyArray)
    oRS3.AddNew
    oRS3.Fields("MeetingID") = 1
    oRS3.Fields("PID")= MyArray(i)
    oRS3.Update
    Next

    Better yet would be to use a sql statement to update your data:

    Dim sql, cmd, arParms
    sql="Insert Into tablename (MeetingID, PID) values (?,?)"
    set cmd=createobject("adodb.command")
    cmd.commandtype=1 'adCmdText
    cmd.commandtext = sql
    set cmd.activeconnection = yourconnectionobject
    MyString = request.form("PID")
    MyArray = Split(MyString,",") 'the delimiter is the comma

    For i = 0 to UBound(MyArray)
    arParms=Array(1, MyArray(i))
    cmd.Execute ,arParms,128 '128=adExecuteNoRecords
    Next
    'close and destroy your connection if finished with it


    --
    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], Jul 9, 2007
    #6
  7. bcap

    bcap Guest

    thank you very much! =-)
    bcap, Jul 11, 2007
    #7
    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. John Grandy
    Replies:
    0
    Views:
    596
    John Grandy
    Sep 13, 2005
  2. Dave
    Replies:
    3
    Views:
    384
    Kevin Goodsell
    Apr 19, 2004
  3. Replies:
    38
    Views:
    1,256
    Dennis Lee Bieber
    Feb 15, 2005
  4. Mike D
    Replies:
    1
    Views:
    175
    Aaron [SQL Server MVP]
    Mar 22, 2005
  5. palmiere
    Replies:
    1
    Views:
    384
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page