Select Statement - Using Multiple

Results 1 to 2 of 2

Thread: Select Statement - Using Multiple

  1. #1
    MG Guest

    Default Select Statement - Using Multiple

    Hi There <BR><BR>I have posted this question numerous times and hope that there is someone out there that can give me some advice. I have a table called branch. One person can work in many branches and a branch can have many people working for them. What is the best way to allow on the adding people page, the ability to choose multiple branches. Could someone give me a quick code snippet as well as an erd for the this. I also need to be able to edit the people, which i can do but the thing is, how do you bring back the same drop down box with the list of branches the person already belongs to. <BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Select Statement - Using Multiple

    Table: Person<BR>Key: PersonID (autonumber)<BR>Att: PersonName (text)<BR>Att: etc...<BR><BR>Table: Branch<BR>Key: BranchID (autonumber)<BR>Att: BranchName (text)<BR>Att: etc...<BR><BR>Table: BranchPerson<BR>Key: BranchID (number)<BR>Key: PersonID (number)<BR>Att: StartDate (date/time)<BR>Att: EndDate (date/time)<BR>Att: etc...<BR><BR>Use the BranchPerson table to record the links between a person and a branch (possibly also recording when they started/finshed working there).<BR><BR>Add/Edit Person page - code snippet:<BR><BR>set rsB = server.creatobject("adobd.recordset")<BR>set rsPB = server.creatobject("adobd.recordset")<BR>response. write "&#060;select name=&#039branch&#039 multiple&#062;"<BR><BR> "select * from Branch", conn, adOpenForwardOnly, adLockOptimistic, adCmdText<BR>do while not rsB.eof<BR> "select count(*) from BranchPerson where PersonID=" & intPID & " and BranchID=" & rsB("BranchID"), conn, adOpenForwardOnly, adLockOptimistic, adCmdText<BR>if rsPB(0)&#062;0 then strSel = " SELECTED" else strSel=""<BR>rsPB.close<BR>response.write "&#060;option value=" & rsB("BranchID") & strSel & "&#062;" & rsB("BranchName") & "&#060;/option"<BR>rsB.movenext<BR>loop<BR>response.write "&#060;/select&#062;"<BR>rsB.close<BR><BR>etc...<BR><BR>Ho pe this gives you an idea. intPID is the PersonID that is being edited, code uses constants from<BR>Think there might be a more effecient way of doing the above using a single query but I couln&#039t figure it out.<BR><BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts