...

View Full Version : problem with dropdownlist



MastaKay
06-08-2007, 12:48 PM
Hi all.

I have a problem with this below code.I cant seem to find the error, and it is suppose to display the dropdownlist with all the values from the database.

Someone please help.I desperately need to figure this out soon.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
Option Explicit

'-- Variables we are using
dim objConn, objRS, objRS2, strSQL

'-- Define our objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")

'-- Database path and Data provider
objConn.ConnectionString = Server.MapPath("C:\dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

'-- Sub Procedure that builds the dropdown list
Sub showDropDownList(c, r, table)

'-- SQL Statement
strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
'-- Execute our SQL statement and store the recordset
Set r = c.Execute(strSQL)

'-- If we have records to return
if r.eof = false then
'-- Open a form/select tag
Response.Write "<form name=""form1"">"
Response.Write "<select name=""dropdownlist"">" & vbCrLf
Response.Write "<option selected=""selected"" value="""">Choose....</option>"
'-- loop and build each database entry as a selectable option
While r.EOF = false
Response.Write "<option value=""Index.html"">" _
& r.Fields("Title").Value & "</option>" & vbCrLf
'-- Move recordset to the next value
r.movenext
Wend
else
response.write"<"Sorry, no records selected">"
end if
'-- close select/form tags
Response.Write "</select></form>" & vbCrLf

End Sub

'-- Sub Procedure that builds the dropdown list
Sub showDropDownList2(c, r, table)

'-- SQL Statement
strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
'-- Execute our SQL statement and store the recordset
r.Open strSQL, c, 3, 3

'-- If we have records to return
if r.eof = false then
'-- Open a form/select tag
Response.Write "<form name=""form1"">"
Response.Write "<select name=""dropdownlist"">" & vbCrLf
'-- loop and build each database entry as a selectable option
'filter
r.Filter = ("Type = 'fruit'")
Response.Write "<option selected=""selected"" value="""">Choose....</option>"
Response.Write "<optgroup label=""Fruit""></optgroup>"
While r.EOF = false
Response.Write "<option value=""Index.html"">" _
& r.Fields("Title").Value & "</option>" & vbCrLf
'-- Move recordset to the next value
r.movenext
Wend

r.Filter = adFilterNone

r.Filter = ("Type = 'veg'")
Response.Write "<optgroup label=""Veg""></optgroup>"
While r.EOF = false
Response.Write "<option value=""Index.html"">" _
& r.Fields("Title").Value & "</option>" & vbCrLf
'-- Move recordset to the next value
r.movenext
Wend
end if
'-- close select/form tags
Response.Write "</select></form>" & vbCrLf

End Sub
%>
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Dynamic dropdown lists in ASP(VBScript) - designplace.org</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="description" content="Dynamic dropdown lists using Active Server Pages and Microsoft Access" />
<meta name="Author" content="Matt Millross" />
</head>

<body>
<h1>Dynamic dropdown lists using Active Server Pages and Microsoft Access</h1>
<p>Below is an example of a drop down list, dynamically populated from a
database table.</p>

<p>
<%
' -- Call the sub procedure to build the drop down list, with the connection and recordset objects and the source table (In quotes).
call showDropDownList(objConn, objRS, "tblDropDownTest")
Sub showDropDownList(c, r, table)

'-- SQL Statement
strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
'-- Execute our SQL statement and store the recordset
Set r = c.Execute(strSQL)

'-- If we have records to return
if r.eof = false then
'-- Open a form/select tag
Response.Write "<form name=""form1"">"
Response.Write "<select name=""dropdownlist"">" & vbCrLf
Response.Write "<option selected=""selected"" value="""">Choose....</option>"
'-- loop and build each database entry as a selectable option
While r.EOF = false
Response.Write "<option value=""Index.html"">" _
& r.Fields("Title").Value & "</option>" & vbCrLf
'-- Move recordset to the next value
r.movenext
Wend
else
response.write"<"Sorry, no records selected">"
end if
'-- close select/form tags
Response.Write "</select></form>" & vbCrLf

End Sub
%>
</p>
<p>Here is the same drop down list, but this time it has been filtered to show
fruit first under a heading, then veg.</p>
<p>
<%
' -- Call the sub procedure to build the drop down list, with the connection and recordset objects and the source table (In quotes).
call showDropDownList2(objConn, objRS2, "tblDropDownTest")
%>
</p>
<p> designplace.org - Feb 2003</p>

</body>
</html>

mehere
06-08-2007, 04:39 PM
tblDropDownTest appears to be the name of the table you're passing to the sub ... so this line:

strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
should be

strSQL = "SELECT * FROM " & table & " ORDER BY Title ASC"
as table is the variable that you put the table name into



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum