Populating a drop down

[bren11]

Hello,

I have been trying to no avail for the past week to get past what is probably a very straightforward topic to most of you.
I am pretty familiar with Access, but new to using ASP on the front end. Basically, my problem is drop down boxes and methods of populating them.

Currently, I have a function that creates the drop downs which looks like this:

Function BuildSelectControl(strName, strValue)

BuildSelectControl=""
Set rsTemp = server.CreateObject ("ADODB.Recordset")
strSQL =""
strSize = 1


if "Company" = strName then strSize = 1 : arr = Array("J", "C") end if

if "EventTypeCode" = strName then strSize = 1 : strSQL="SELECT [JobTypeType], [JobTypeCode] FROM [JOBTYPE] " end if

if "Contact" = strName then strSize = 1 : strSQL = "SELECT [CustomerCode], [CustomerCode] FROM [Customers]" end if

if "ContactCode" = strName then strSize = 1 : strSQL = "SELECT [ContactCode], [ContactCode] FROM [CONTACTS] " end if

if "CustomerCode" = strName then strSize = 1 : strSQL = "SELECT [CustomerCode], [CustomerCode] FROM [CUSTOMERS] " end if

if "LabCode" = strName then strSize = 1 : arr = Array("CCS32999", "CCS32998", "CCS32997", "CCS23590", "CCS8360", "CustomJ", "CustomC") end if


if strSQL <> "" then

rsTemp.open strSQL, dbConnection

if rsTemp.EOF then exit function


BuildSelectControl = BuildSelectControl & "<select size = " & strSize & " name=""" & strName & """>"
BuildSelectControl = BuildSelectControl & "<option value="""">Please select</option>"

while not rsTemp.Eof
if rsTemp(0)=strValue then
BuildSelectControl = BuildSelectControl & "<option value=""" & rsTemp(0) & """ selected>" & rsTemp(1) & "</option>"
else
BuildSelectControl = BuildSelectControl & "<option value=""" & rsTemp(0) & """>" & rsTemp(1) & "</option>"
end if
rsTemp.MoveNext
wend

BuildSelectControl = BuildSelectControl & "</select>"

rsTemp.Close
set rsTemp = Nothing
else


BuildSelectControl = BuildSelectControl & "<select size = " & strSize & " name=""" & strName & """>"
if CInt(strSize)<2 then _
BuildSelectControl = BuildSelectControl & "<option value="""">Please select</option>"

for ind=LBound(arr) to UBound(arr)
bYes = false
if IsNumeric(arr(ind)) then
if CInt(arr(ind)) = strValue then bYes = true
end if
if arr(ind)=strValue or bYes then
BuildSelectControl = BuildSelectControl & "<option value=""" & arr(ind) & """ selected>" & arr(ind) & "</option>"
else
BuildSelectControl = BuildSelectControl & "<option value=""" & arr(ind) & """>" & arr(ind) & "</option>"
end if
next
BuildSelectControl = BuildSelectControl & "</select>"


end if

End Function

I am trying to change the way [Contacts] [Customers] and [JobType]loads initially, because I need multiple columns/values from the database.

In looking into variable arrays, I guess I just don't understand how to assign this to my strSQL....

I have also tried adding to the SQL statement with the additional fields I need, but am unable to "write" the other values to my table.

I have gotten some great help from this forum in the past, and I must say, I have looked at this until I am blue in the face, and just don't see the clear solution. I know I need to use a multi dimensional array, but after that my eyes kinda glaze over...

Can someone help me?

Thanks in advance

 

[skotman]

Below is a snippit from one of my apps.
It calls RSconnect() which queries the database.
Then for each record it finds it prints out the
<option line

<%
Function RSconnect(SQL)
'REsponse.Write(SQL)
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = "dsn=testMachine;"
RS.Source = SQL
RS.CursorType = 0
RS.CursorLocation = 2
RS.LockType = 3
RS.Open
End Function
%>

<Select Name='testTech'>
    <%SQL = "Select * from tblTestTech order by ID asc"
    RSconnect(SQL)
    Do While NOT RS.EOF
     If testTech = RS("testTech") Then
 	Response.Write("<option value='" & RS("testTech") & "' Selected>" & RS("testTech") &  vbcrlf)
    Else
    	Response.Write("<option value='" & RS("testTech") & "'>" & RS("testTech") &  vbcrlf)
    End If
    RS.MoveNext()
    LooP
    %>
    </Select>

Scott Heath

http://www.scottspad.com

AIM: orange7288

 

[bren11]

Hi, Scott,

I am afraid I didn't have time today to try this out, but I really appreciate your quick reply...the first chance I get to try it out, I will. Looks a lot simpler than most of the examples I have seen...
Thanks
B