Help with Dropdown Looping

breeze76 · #1 (**permalink**) 10-14-04, 18:46

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

%>

rokslide · #2 (**permalink**) 10-14-04, 20:48

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">

breeze76 · #3 (**permalink**) 10-14-04, 20:58

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

rokslide · #4 (**permalink**) 10-14-04, 21:02

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

breeze76 · #5 (**permalink**) 10-14-04, 21:11

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..

rokslide · #6 (**permalink**) 10-14-04, 21:16

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.

breeze76 · #7 (**permalink**) 10-15-04, 00:22

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??

rokslide · #8 (**permalink**) 10-15-04, 00:25

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

rokslide · #9 (**permalink**) 10-15-04, 00:28

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.

breeze76 · #10 (**permalink**) 10-15-04, 09:18

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..

rokslide · #11 (**permalink**) 10-16-04, 19:32

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

breeze76 · #12 (**permalink**) 10-16-04, 20:16

I want to thank youfor the info.. that was very interesting..

Internet Services

Web Development

Digital Marketing

Consumer Tech