Code Integration

[MetroToday]

I have two pieces of code. Both are used to populate a drop down list box dynamically. However I would like to integrate them into one.

First part of code:
There are two dropdown boxes which are linked to each other. That means if I select a value from the first then the second one populates with values accordingly.

Second Part of code
If from the previous page a user enters a barcode ID, the on the next page the two drop down boxes should populate with the correct data.


I am having trouble combining both the codes.

Here they are.

For first Dropdown box: (is accesed through the include command)
<%
Set oTmp = Server.CreateObject(&quot;ADODB.Recordset&quot
oTmp.Open &quot;SELECT * FROM ctrldept&quot;, &quot;DSN=storage&quot;
Do While Not oTmp.EOF
Response.Write(&quot;<OPTION VALUE=&quot;&quot;&quot; & oTmp(&quot;ctrlDeptID&quot & &quot;&quot;&quot; &quot
If Not oRS.EOF And Not oRS.BOF Then
If oRS(&quot;ctrlDeptID&quot = oTmp(&quot;ctrlDeptID&quot Then
Response.Write(&quot;SELECTED &quot
End If
End if
Response.Write(&quot;>&quot
Response.Write(oTmp(&quot;department&quot & &quot; - &quot; & oTmp(&quot;deptid&quot & &quot; &quot
Response.Write(&quot;</OPTION>&quot
oTmp.MoveNext
Loop
oTmp.Close
Set oTmp = Nothing
%>

For second Dropdown box: (is accesed through the include command)
<%
Set oTmp = Server.CreateObject(&quot;ADODB.Recordset&quot
oTmp.Open &quot;SELECT * FROM ctrlDocs&quot;, &quot;DSN=storage&quot;
Do While Not oTmp.EOF
Response.Write(&quot;<OPTION VALUE=&quot;&quot;&quot; & oTmp(&quot;ctrlDocsID&quot & &quot;&quot;&quot; &quot
If Not oRS.EOF And Not oRS.BOF Then
If oRS(&quot;ctrlDocsID&quot = oTmp(&quot;ctrlDocsID&quot Then
Response.Write(&quot;SELECTED &quot
End If
End if
Response.Write(&quot;>&quot
Response.Write(oTmp(&quot;Doctype&quot & &quot; - &quot; & oTmp(&quot;DocID&quot & &quot; &quot
Response.Write(&quot;</OPTION>&quot
oTmp.MoveNext
Loop
oTmp.Close
Set oTmp = Nothing
%>

Code for auto populating both the drop down boxes and where the second dropdown box adjusts it self based on the selection of the first drop downbox.

dim selectedDept
If Request(&quot;ctrlDeptID&quot = &quot;&quot; then
selectedDept = &quot;0&quot;
Else
selectedDept = Request(&quot;ctrlDeptID&quot
end if


<% <td width=&quot;22%&quot;><select size=&quot;1&quot; name=&quot;ctrlDeptID&quot; OnChange=&quot;frmtypes.submit();&quot;>

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;DSN=storage&quot;
Set rs = conn.Execute(&quot;SELECT * FROM ctrldept&quot
Do While Not rs.EOF %>


<OPTION VALUE=&quot;<%= RS(&quot;ctrlDeptID&quot %>&quot; <% If RS(&quot;ctrlDeptID&quot = CInt(selectedDept) Then Response.Write(&quot;SELECTED&quot%>> <%= RS(&quot;department&quot %> - <%= RS(&quot;deptid&quot %>


<% rs.MoveNext
Loop
rs.Close
conn.Close
%>


<select size=&quot;1&quot; name=&quot;ctrlDocsID&quot;>
<% Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;DSN=storage&quot;

Select Case selectedDept
Case 0
Set rs = conn.Execute(&quot;SELECT * FROM ctrldocs WHERE ctrlDeptID=1&quot
Case Else
Set rs = conn.Execute(&quot;SELECT * FROM ctrldocs WHERE ctrlDeptID=&quot; & selectedDept)
End Select
Do While Not rs.EOF %>
<OPTION VALUE=&quot;<%= rs(&quot;ctrlDocsID&quot %>&quot;> <%= rs(&quot;doctype&quot %> - <%= rs(&quot;docid&quot %>
<% rs.MoveNext
Loop
rs.Close
conn.Close %>
</select>
________________

Note that for the first drop down box I have made it submit to itself in the form.

Any help would be appreciated.

 

[Tarwn]

Not knowing more about your data structure you are using I would guess that by altering the SQL statement on the first dropdown you ought to get what you are aiming for:

If barcodeID is a numeric field:
oTmp.Open "SELECT * FROM ctrldept WHERE barcodeID = "&Request.Form("barcodeID"), "DSN=storage"

If barcode ID is a text field:
oTmp.Open "SELECT * FROM ctrldept WHERE barcodeID = '"&Request.Form("barcodeID")&"'", "DSN=storage"

This is the general concept, it will only work if the barcode id is in the ctrldept, if it is in a related table you will need to select like this:
SELECT ctrl.* FROM ctrldept, othertable WHERE ctrldept.yourKey = othertable.yourKey AND othertable.barcodeID = &quot;&Request.Form(&quot;barcodeID&quot

That is just the numeric solution this time, but I think you should be able to figure it out from there.

One note: SELECTing * is generally an easy way to add processing time, basically this causes a double query in the background, 1 query to determine the field names, and then another to actually run your query using those field names instead of the *. Actually naming the fields you want (and only those) will improve performance (as well as communication performance between db and ASP as you will only be receiving the fields you need instead of all of them).

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html