Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    problem with dropdownlist

    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>

  • #2
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tblDropDownTest appears to be the name of the table you're passing to the sub ... so this line:
    Code:
    strSQL = "SELECT * FROM " & tblDropDownTest & " ORDER BY Title ASC"
    should be
    Code:
    strSQL = "SELECT * FROM " & table & " ORDER BY Title ASC"
    as table is the variable that you put the table name into


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •