Populating DropDown at Runtime URGENT

ufobaby · May 19, 2002

hi, am an absolute newbie,
i have 2 dropdown menus. when the first one is clicked the 2nd one should be populated with values from recordset. The problem is when the page is displayed both the dropdowns should be visible. So far have written the below code please help me debug this. it just does not work !!!!
================
<%
Dim ConLog 'For Opening Connection to Db
Dim rs 'For RecordsSet
Dim aryClt
Set ConLog = Server.CreateObject("adodb.connection&quot

Set rs = Server.CreateObject("ADODB.Recordset&quot

ConLog.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\

http://wwwroot\login\user.mdb;pwd=sauser001"

rs.Open "select distinct cod_client from corp_2002 where left(cod_client,1)='A'",ConLog
aryClt = rs.GetRows()
Response.Write("Conn estb....&quot

Response.Write("<Script Language=JavaScript>&quot

Response.Write("<!--&quot

Response.Write("function PopulateOptions(){" & chr(13))
Response.Write("Conn estb 123....&quot

Response.Write("x= 0&quot
Response.Write("for x=0 to ubound(aryclt,2)&quot
Response.Write(" sSelect = sSelect + '<OPTION VALUE=' + aryClt(1,x) + '>' + aryClt(0,x) + '</OPTION>'" & chr(13))
Response.Write("Next&quot
Response.Write(" document.all['fill'].innerHTML = sSelect;" & chr(13))
Response.Write("}&quot

Response.Write("-->&quot

Response.Write("</Script>&quot

%>
<html>
<body>
<form method="post" action="results.asp">
<P><select name="reptype" size="1" onChange=PopulateOptions()>
<OPTION VALUE="none" SELECTED="Selected">Select Report Type</option>
<OPTION VALUE="Location">Location Wise</option>
<OPTION VALUE="Corp">Corp Code Wise</option>
</select></P>
<P>
<SPAN Id=fill>
<select name="cmbFill" size="1">
<option value=0 selected></option>
</select>
</span>
</P>
<P>
<input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset" name="B2">
</P>
</form>
</body>
</html>
=========================
You can also mail me at kniraj@hotmail.com if u need the database, but in a plain asp code the "for loop" in bold works fine to display data.

PS. Also if u can direct me to some site showing how to use JS and ASP together, it would be of great help.

Regards
Niraj [noevil]

SarkMan · May 19, 2002

<%
Dim ConLog 'For Opening Connection to Db
Dim rs 'For RecordsSet
Dim aryClt
Set ConLog = Server.CreateObject("adodb.connection&quot

Set rs = Server.CreateObject("ADODB.Recordset&quot

ConLog.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\

http://wwwroot\login\user.mdb;pwd=sauser001"

rs.Open "select distinct cod_client from corp_2002 where left(cod_client,1)='A'",ConLog
aryClt = rs.GetRows()
Response.Write("Conn estb....&quot

Response.Write("<Script Language=JavaScript>&quot

Response.Write("<!--&quot

Response.Write("function PopulateOptions(){" & chr(13))
Response.Write "//Conn estb 123...." & vbcrlf

Response.Write "var sSelect='<select name=cmbFill>'" & vbcrlf
x= 0

for x=0 to ubound(aryclt,2)

sSelect = sSelect + "<OPTION VALUE='"& aryClt(0,x) & " '> " & aryClt(1,x) & " </OPTION>"

next

Response.Write "var sSelect=sSelect + " & chr(34) & sSelect & chr(34) & vbcrlf
Response.Write "var sSelect=sSelect + '</select>'" & vbcrlf

Response.Write " document.all['fill'].innerHTML =sSelect" & vbcrlf

Response.Write("}&quot

Response.Write("-->&quot

Response.Write("</Script>&quot

%>
<html>
<body>
<form method="post" action="results.asp">
<P><select name="reptype" size="1" onChange=PopulateOptions()>
<OPTION VALUE="none" SELECTED="Selected">Select Report Type</option>
<OPTION VALUE="Location">Location Wise</option>
<OPTION VALUE="Corp">Corp Code Wise</option>
</select></P>
<P>
<SPAN Id=fill>
<select name="cmbFill" size="1">
<option value=0 selected></option>
</select>
</span>
</P>
<P>
<input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset" name="B2">
</P>
</form>
</body>
</html>

ufobaby · May 20, 2002

Thanks a ton SarkMan for u r time,
but now it does not execute beyond
Response.Write("Conn estb....&quot
the previous code did show both the dropdowns and buttons.

Do i write
Response.Write "var sSelect='<select name=cmbFill>'" & vbcrlf
and the loop on the same line or the way u have written. I believe the problem is with the way we have concatenated the string. And why do we again need
sSelect='<select name=cmbFill>' as we already have created the dropdown in thehtml code below.

Please Help !!!
Thanks & Regards
Niraj [noevil]

cbchumley · May 20, 2002

Try the following code...You don't need to use JavaScript to populate your combo box. Just loop through recordset.

<%
'----------New Code ------------------------
Dim rs 'For RecordsSet
set rs = Server.CreateObject("ADODB.Recordset&quot

rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\

http://wwwroot\login\user.mdb;pwd=sauser001;Jet

OLEDB:System Database=system.mdw;"
rs.Source = "select distinct cod_client from corp_2002 where left(cod_client,1)='A'"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

'----------Don't need all this ------------------
'Dim ConLog 'For Opening Connection to Db
'Dim rs 'For RecordsSet
'Dim aryClt
'Set ConLog = Server.CreateObject("adodb.connection&quot

'Set rs = Server.CreateObject("ADODB.Recordset&quot

'ConLog.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\

http://wwwroot\login\user.mdb;pwd=sauser001"

'rs.Open "select distinct cod_client from corp_2002 where left(cod_client,1)='A'",ConLog
'aryClt = rs.GetRows()
'Response.Write("Conn estb....&quot

'Response.Write("<Script Language=JavaScript>&quot

'Response.Write("<!--&quot

'Response.Write("function PopulateOptions(){" & chr(13))
'Response.Write("Conn estb 123....&quot

'Response.Write("x= 0&quot

'Response.Write("for x=0 to ubound(aryclt,2)&quot

'Response.Write(" sSelect = sSelect + '<OPTION VALUE=' + aryClt(1,x) + '>' + aryClt(0,x) + '</OPTION>'" & chr(13))
'Response.Write("Next&quot

'Response.Write(" document.all['fill'].innerHTML = sSelect;" & chr(13))
'Response.Write("}&quot

'Response.Write("-->&quot

'Response.Write("</Script>&quot

'----------------------------------------------------

%>
<html>
<body>
<form method="post" action="results.asp">
<P><select name="reptype" size="1">
<OPTION VALUE="none" SELECTED>Select Report Type</option>
<OPTION VALUE="Location">Location Wise</option>
<OPTION VALUE="Corp">Corp Code Wise</option>
</select>
</P>



<P><SELECT NAME="cboWhatever">
<OPTION VALUE=0 SELECTED>-make selection-</OPTION>
<%
While (NOT rs.EOF)
%>
<OPTION VALUE="<%=(rs.Fields.Item("cod_Client&quot

.Value)%>" ><%=(rs.Fields.Item("cod_Client&quot

.Value)%></OPTION>
<%
rs.MoveNext()
Wend
%>
</SELECT>



<P>
<input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset" name="B2">
</P>
</form>
</body>
</html>

ufobaby · May 21, 2002

Thanks, cbchumley for the help,
but the problem is that the Both the Drop Down boxes ahould be visible when the form loads. The one which we are filling with values will be displayed blank initially, and will be filled only onChange of the First Combo.

Any Ideas on this..... Please Help

Regards
Niraj [noevil]

cbchumley · May 21, 2002

ufobaby, do you already know what values you are going to populate in the combo boxes regardless of what users selects from first combo box or are you wanting to send page back up to server passing the value of first combo box to a SQL(WHERE)clause to then fill second combo box?

cbchumley
[yinyang]

ufobaby · May 21, 2002

no, would be keeping the table name in the first dropdown <Option Value=tabname>Text</Option>

and use this in the where clause. So the Recordset also will be dynamically created and would be run each time the user selects the First Combo. Actually i will be having about 4 Dropdown which will get populated with on single recordset. Have taken only one in the above example.

Regards
Niraj [noevil]

cbchumley · May 21, 2002

You can try this, it uses an array that is filled with nothing at first until you make a selection from first combo box.
Once selection made you are passing a querystring to the same page with table name that is used in SQL string.
Then array is filled with results and reiterated to populate second combo box.
Hope this helps...cbchumley

<%
If Request.QueryString("Table&quot

<> "" Then
Dim sTable
sTable = Request.QueryString("Table&quot

Dim rs 'For RecordsSet
set rs = Server.CreateObject("ADODB.Recordset&quot

rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\

http://wwwroot\login\user.mdb;pwd=sauser001;Jet

OLEDB:System Database=system.mdw;"
rs.Source = "select distinct cod_client from " & sTable & " where left(cod_client,1)='A'"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

iCount = rs.RecordCount
Dim aList()
ReDim aList(iCount,1)

For i = 1 to iCount
aList(i,1) = rs("cod_client&quot

rs.MoveNext
Next

rs.Close
Set rs = Nothing

Else

Dim aList()
ReDim aList(1,1)

For i = 1 to 1
aList(i,1) = ""
Next

End If
%>

<html>

<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>

</SCRIPT>

<body>
<form name="frmTableSearch" method="post" action="results.asp">
<P><select name="cboTable" size="1">
<OPTION VALUE="none" SELECTED>Select Report Type</option>
<OPTION VALUE="thispage.asp?Table=Location">Location Wise</option>
<OPTION VALUE="thispage.asp?Table=Corp">Corp Code Wise</option>
</select>
</P>

<P>
<SELECT NAME="cboWhatever">
<OPTION VALUE=0 SELECTED>-make selection-</OPTION>
<%
For i = 1 to ubound(aList)
Response.Write "<OPTION VALUE=" & aList(i,1) & ">" & aList(i,1) & "</OPTION>"
Next
%>
</SELECT>
</P>

<P>
<input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset" name="B2">
</P>
</form>
</body>
</html>

ufobaby · May 22, 2002

thanks,
i tired but the location.href (i.e the url) of the page does not change and so the values in the combo 2 also does not get populated. The file name is "options1.asp" and have replaced "thispage.asp". Do i need to give the full path ???

Could you please help... i think the change event is not getting fired or some thing...

Regards
Niraj [noevil]

Gatorajc · May 22, 2002

I had the same problem. I ended up putting the drop downs on two different pages but used a frame so it appears they are on the same page. Now when the first drop down is selected it refreshes the second page and populates the second one drop down. It actually turned out alright. You cannot tell the difference.

I was told this is the only way to do it because the page has to be refreshed in order to query the DB again.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Populating DropDown at Runtime URGENT

ufobaby

MIS

SarkMan

Programmer

ufobaby

MIS

cbchumley

Programmer

ufobaby

MIS

cbchumley

Programmer

ufobaby

MIS

cbchumley

Programmer

ufobaby

MIS

Gatorajc

MIS

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Populating DropDown at Runtime ***URGENT***

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor

Populating DropDown at Runtime URGENT