Creating a form with multi select

[bcap]

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]

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]

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]

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]

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

 

[Bob Barrows [MVP]]

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

 

[bcap]

thank you very much! =-)