Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    75

    Question Unanswered: Help with Dropdown Looping

    I am trying to pull data from my db to poplulate my dopdown then pass a field to another page. The problem I am having is that it loops and instead of having say 200 entries in one dropdown ( I know, I will take care of that once I get this to work) I am getting 200 dorpdowns with an entry in each one..LOL.. so here is my code, can someone please tell me whatI am doing wrong:

    <%
    DIM FIRM_ID
    'create a DSNless database connection
    set cnn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.RecordSet")
    cnn.Open "driver={Microsoft Access Driver (*.mdb)};;dbq=C:\DB\db.mdb"
    sqltext = "SELECT FIRM_NAME, FIRM_ID FROM FIRMS ORDER BY FIRM_NAME ASC"
    rs.open sqltext,cnn

    %>
    <form action="users.asp" id="FORM1" method="post" name="FORM1">

    <%
    Do While Not rs.EOF %>
    <SELECT value="<%=rs("FIRM_ID")%>" name="<%=rs("FIRM_NAME")%>">

    <OPTION value="<%=rs("FIRM_ID")%>"><%=rs("FIRM_NAME")%></OPTION>


    <input name="FIRM_ID" type="hidden" value="<%=rs("FIRM_ID")%>">
    <input name="submit" type="submit" value="Submit">
    <% rs.MoveNext
    Loop%>
    </SELECT>
    </form>
    <%
    'close the recordset,cnn and set rs & cnn to nothing
    rs.close
    cnn.close
    set rs = nothing
    set cnn = nothing

    %>

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    heheh an easy one.... replace this...
    Code:
    <%
    Do While Not rs.EOF %>
    <SELECT value="<%=rs("FIRM_ID")%>" name="<%=rs("FIRM_NAME")%>">
    
    <OPTION value="<%=rs("FIRM_ID")%>"><%=rs("FIRM_NAME")%></OPTION>
    
    
    <input name="FIRM_ID" type="hidden" value="<%=rs("FIRM_ID")%>">
    <input name="submit" type="submit" value="Submit">
    <% rs.MoveNext
    Loop%>
    with this
    Code:
    <%
    <%if not rs.eof then%>
      <SELECT value="<%=rs("FIRM_ID")%>" name="<%=rs("FIRM_NAME")%>">
      Do While Not rs.EOF %>
        <OPTION value="<%=rs("FIRM_ID")%>"><%=rs("FIRM_NAME")%></OPTION>
    <% rs.MoveNext
      Loop%>
    </select>
    <%end if%>
    <input name="FIRM_ID" type="hidden" value="<%=rs("FIRM_ID")%>">
    <input name="submit" type="submit" value="Submit">

  3. #3
    Join Date
    Apr 2004
    Posts
    75
    did not work I get this error:

    Error Type:
    ADODB.Field (0x80020009)
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
    /firms2.asp

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    sorry my mistake....try this...
    Code:
    <%
    <%if not rs.eof then%>
      <input name="FIRM_ID" type="hidden" value="<%=rs("FIRM_ID")%>">
      <SELECT value="<%=rs("FIRM_ID")%>" name="<%=rs("FIRM_NAME")%>">
      Do While Not rs.EOF %>
        <OPTION value="<%=rs("FIRM_ID")%>"><%=rs("FIRM_NAME")%></OPTION>
    <% rs.MoveNext
      Loop%>
    </select>
    <%end if%>
    <input name="submit" type="submit" value="Submit">
    I'm not really sure what you are trying to achieve with that hidden input though so this may not do exactly what you are after

  5. #5
    Join Date
    Apr 2004
    Posts
    75
    Quote Originally Posted by rokslide
    sorry my mistake....try this...

    I'm not really sure what you are trying to achieve with that hidden input though so this may not do exactly what you are after
    I am trying to pass the FIRM_ID to another page and capture it with a request.querystring = FRIM_ID on the next page..

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie then use this instead...
    Code:
    <%
    <%if not rs.eof then%>
      <SELECT name="FIRM_ID" id="FIRM_ID">">
      Do While Not rs.EOF %>
        <OPTION value="<%=rs("FIRM_ID")%>"><%=rs("FIRM_NAME")%></OPTION>
    <% rs.MoveNext
      Loop%>
    </select>
    <%end if%>
    <input name="submit" type="submit" value="Submit">
    you can then use request.form("FIRM_ID") which will return the currently selected value of the dropdown.

  7. #7
    Join Date
    Apr 2004
    Posts
    75
    OK I got this page to post to the next one but I get this error on the second page:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
    /users.asp, line 18


    Here is my code:

    <HTML>
    <HEAD>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
    <TITLE>USERS</TITLE>
    </HEAD>
    <BODY>

    <%
    DIM str, sql, rs, conn, count, bg, FIRM_ID
    set conn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.RecordSet")
    conn.Open "driver={Microsoft Access Driver (*.mdb)};;dbq=C:\database\MYdb.mdb"
    str = request.form("FIRM_ID")
    count = 1

    sql= "SELECT FIRM_NAME, USER_NAME, USER_ID, PASSWORD, EMAIL, NOTES FROM USERS WHERE FIRM_NAME = " & str

    rs.Open sql, conn, 0, 1, 2 <<-- line 18
    If rs.eof Then


    %>
    <TABLE BORDER=1 BORDERCOLOR=#ffffff BGCOLOR=#ffffff CELLSPACING=0 width= 100%><FONT FACE="Arial" COLOR=#000000><CAPTION><B>USERS INFORMATION</B></CAPTION></FONT>

    <THEAD>
    <TR>

    <TH BGCOLOR=#000000 BORDERCOLOR=#ffffff ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#ffffff>User Name</FONT></TH>
    <TH BGCOLOR=#000000 BORDERCOLOR=#ffffff ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#ffffff>User ID</FONT></TH>
    <TH BGCOLOR=#000000 BORDERCOLOR=#ffffff ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#ffffff>Password</FONT></TH>
    <TH BGCOLOR=#000000 BORDERCOLOR=#ffffff ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#ffffff>Email</FONT></TH>
    <TH BGCOLOR=#000000 BORDERCOLOR=#ffffff ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#ffffff>Notes</FONT></TH>
    </TR>
    </THEAD>
    <TBODY>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof

    if (count mod 2) = 1 then
    bg = "#FFFFFF" 'Main Color
    else
    bg = "#CCCCCC" 'Alternate Color
    end if

    count = count + 1
    %>
    <TR VALIGN=TOP>


    <TD BORDERCOLOR=#ffffff bgColor=<%=bg%> ALIGN=left width=30%><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("USER _NAME").Value)%><BR></FONT></TD>
    <TD BORDERCOLOR=#ffffff bgColor=<%=bg%> ALIGN=center width=10%><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("USER _ID").Value)%><BR></FONT></TD>
    <TD BORDERCOLOR=#ffffff bgColor=<%=bg%> ALIGN=center width=10%><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PASS WORD").Value)%><BR></FONT></TD>
    <TD BORDERCOLOR=#ffffff bgColor=<%=bg%> ALIGN=center width=25%><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("EMAI L").Value)%><BR></FONT></TD>
    <TD BORDERCOLOR=#ffffff bgColor=<%=bg%> ALIGN=center width=25%><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("NOTE S").Value)%><BR></FONT></TD>

    </TR>
    <%
    rs.MoveNext
    loop%>
    <%
    rs.close
    conn.close
    set rs = nothing
    set conn = nothing
    end if
    %>

    </TBODY>
    <TFOOT></TFOOT>
    </TABLE>
    </BODY>
    </HTML>

    Any suggestions??

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    change this bit
    Code:
    sql= "SELECT FIRM_NAME, USER_NAME, USER_ID, PASSWORD, EMAIL, NOTES FROM USERS WHERE FIRM_NAME = " & str
    
    rs.Open sql, conn, 0, 1, 2
    to....
    Code:
    sql= "SELECT FIRM_NAME, USER_NAME, USER_ID, PASSWORD, EMAIL, NOTES FROM USERS WHERE FIRM_NAME = " & str
    response.write sql
    rs.Open sql, conn, 0, 1, 2
    so we can see the sql that is trying to be passed to the database

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    actually I think I can guess. try this...
    Code:
    sql= "SELECT FIRM_NAME, USER_NAME, USER_ID, PASSWORD, EMAIL, NOTES FROM USERS WHERE FIRM_NAME = '" & str & "'"
    
    rs.Open sql, conn, 0, 1, 2
    because you are looking for a text value you need to wrap it in quotes.

  10. #10
    Join Date
    Apr 2004
    Posts
    75
    Can you tell me what these are for:


    rs.Open sql, conn, 0, 1, 2


    The 0, 1, 2 I know you can put other numbers there, but what are they for and what do they do? ALso, what other numbers can be put there and what will they do or not do...

    Thanks for your reply..

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    taken from MSDN

    recordset.Open Source, ActiveConnection, CursorType, LockType,
    Options

    So 0 is your CursorType, 1 is your LockType, 2 is your options.... more info can be found here http://msdn.microsoft.com/library/de...ject_oledb.asp

    These represent ADO COnstants. For more details on the constants here are the links to MSDN

    CursorTypes
    http://msdn.microsoft.com/library/de...ortypeenum.asp

    LockTypes
    http://msdn.microsoft.com/library/de...cktypeenum.asp

    CommandTypes (for Options)
    http://msdn.microsoft.com/library/de...ndtypeenum.asp

  12. #12
    Join Date
    Apr 2004
    Posts
    75
    I want to thank youfor the info.. that was very interesting..

Posting Permissions

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