asked on

Building a dropdown using ASP 3.0 - hiding the key field

I have not yet coded this - but I'm just trying to think it through:
I need to display a dropdown of employees that a specific User is authorized to enter payroll time for. The User will then select one of the employees - and the application will use the Empl ID of the selected employee for ...doing the rest of what this app needs to do (that part is out of scope of this question). Note: Names of employees are displayed - but I need to get the Empl ID associated with the selected Name.
So - to build the dropdown:
1. Read a table containing all authorized Users and the employees who they are authorized to enter payroll time for - and build a recordset of all employees associated with this User.
2. Recordset stores all associated Names for this User (...does it also need Empl IDs?)
3. Fill the Option Values for the dropdown using Names, and display the form.
4. OnChange - execute some script that will grab the Empl ID from the Name selected...somehow.
Question: I only want to display Names in the dropdown - but the app will need the Empl ID of the selected Name. How do I build my dropdown to make sure I get the Empl ID?

Wayne Barron

You will need to fill in the database table and field information
But this is how it is done.

Good Luck
Carrzkiss

<%
Set dbopen = Server.CreateObject("ADODB.Connection")
dbopen.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("yourdb.mdb") & ";"
dbopen.Open
 
Set rsSelect = Server.CreateObject("ADODB.Recordset")
sql = "Select DISTINCT Name From TableName"
rsSelect.Open sql, dbopen
%>
 
 <select name="Name" style="font: 8pt verdana;">
 <option value="">Choose Bill Category</option>
                                      <%
While (NOT rsSelect.EOF)
%>
                                      <option value="<%=(rsSelect.Fields.Item("ID").Value)%>" <%if (CStr(rsSelect.Fields.Item("Name").Value) = CStr(rsSelect.Fields.Item("Name").Value)) then Response.Write("SELECTED") : Response.Write("Name")%>><%=(rsSelect.Fields.Item("Name").Value)%></option>
                                      <%
  rsSelect.MoveNext()
Wend
If (rsSelect.CursorType > 0) Then
  rsSelect.MoveFirst
Else
  rsSelect.Requery
End If
' added in to the bottom, and extra Blank Select, for those DEEP Select List, instead of scrolling to the top, the visitor can simply choose this to end the use of this area.
%>
 </select>

Open in new window

OGSan

ASKER

Thanks for the reply, Carrzkiss. Can you help me understand the IF statement inside of the While loop? I've boosted the points to maximum anticipating I may need additional help with getting this going. TIA!
Jeff (aka OGSan)

Wayne Barron

What do you need to know?

The [If (rsSelect.CursorType > 0) Then]

The CursorType property sets or returns the cursor type to use when opening a Recordset object.
If true, then movenext (Display the Records form the Table) if not true then requery the tablefield again.

If you cannot get this sample to work for you, then
Upload an example of your Database, and let me know which field that you want
To include in the Dropdown, and I will be more then happy to make this demo work for you.

Carrzkiss

OGSan

ASKER

Hi, Carrzkiss -
Thanks so much!
I've attached the database which consists of a single table, tbl_EmplID_RowSecurity_xref. This table is built from an extract of a series of PeopleSoft security tables - and should allow the application to provide a dropdown of all employees that the User has authority to input time for.
The sql would be something like, "Select * from tbl_EmplID_RowSecurity_xref where EmplID_(OPRDEFN) = strEmplID" - this would retrieve all rows for the signed-on User (I have stored the User's Empl ID value in the variable, strEmplID).
The dropdown would be built from all of the EE Name values returned.
What the application needs is the associated EmplID_(GROUP DTL) value for the EE Name selected in the dropdown.
Let me know if I confused you.
AbsTimeEntryOnline.mdb

Wayne Barron

Worked like a charm.

here are a few links to check it out with
1000003
1000012
1000017

Code
http://ee.cffcs.com/Q_24240327/Q_24240327.zip

Let me know if this is what you are wanting.
Carrzkiss

OGSan

ASKER

Hi, Carrzkiss - Yes, we're (more than) halfway there! The only part that is still a mystery to me, and which is the original question that I had (!), was how to get the associated Empl ID of the employee that has been selected.
Can the code be tweaked to display the Empl ID of the selected employee Name? For example, using 1000003 above - and then selecting employee "Fujimoto,Randy S," I would want to see displayed 1000397...which is Randy Fujimoto's Empl ID.
Thanks so much for sharing your skills, Carrzkiss!

Wayne Barron

yep

OGSan

ASKER

???

Wayne Barron

yep - meaning that it can be done.
I am working on it right now.
2 questions

#1: Do you want it to where when you Select a choice, it displays without sending to another page?
#2: Do you have it setup to have another page to display the users information?

OGSan

ASKER

Oh, sorry, I didn't understand the reply. :-)
#1 comes closest to the design as it stands today. The User won't leave the page - but more info will be asked of them for this selected employee (questions about the employee's absence e.g., start and ending date, reason, etc.).

Wayne Barron

ok.
I will work on this solution.
It maybe tomorrow before it is ready, as I am getting really tired.
Been a long day.

I should have it ready for you by mid-tomorrow.

Have a good one.
Carrzkiss

OGSan

ASKER

OK, thanks a bunch, Carrzkiss.

ASKER CERTIFIED SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OGSan

ASKER

Carrzkiss - this is absolutely perfect! Thank you much!
Jeff

Wayne Barron

:)
You are very welcome Jeff.
Have a good one and keep on coding.

Wayne (Carrzkiss)