p2p.wrox.com: Archives - asp

asp_databases thread: Creating a List Box without duplicate records

Message #1 by linda.sulflow@b... on Fri, 25 May 2001 13:50:55

I'd like to create a list box and populate it with records from an Access 

DB. There will be several dublicate values in the database. How do I 

filter out and duplicates? My code as it exists now is as follows:



<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test.asp">

  <p> SELECT NAME: 

    <SELECT name="GLCname" size="1">

      <%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p> 

    <INPUT TYPE="submit">

  </p>

</form>



Thanks for any help you can offer.

Message #2 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Fri, 25 May 2001 15:58:31 +0200

Hi!



Use a select statement like this:



SELECT DISTINCT Fieldname1, Fieldname2 FROM sometable WHERE somecondition





Hakan



-----Original Message-----

From: linda.sulflow@b... [mailto:linda.sulflow@b...]

Sent: den 25 maj 2001 13:51

To: ASP Databases

Subject: [asp_databases] Creating a List Box without duplicate records





I'd like to create a list box and populate it with records from an Access

DB. There will be several dublicate values in the database. How do I

filter out and duplicates? My code as it exists now is as follows:



<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test.asp">

  <p> SELECT NAME:

    <SELECT name="GLCname" size="1">

      <%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE="submit">

  </p>

</form>



Thanks for any help you can offer.

Message #3 by Hal Levy <hal.levy@s...> on Fri, 25 May 2001 10:08:29 -0400

add a DISTINCT directive to you SQL statement. (seems to be a stored

procedure named Inventory)





Hal Levy

StarMedia Network, Inc.

Intranet Development Manager



-----Original Message-----

From: linda.sulflow@b... [mailto:linda.sulflow@b...]

Sent: Friday, May 25, 2001 9:51 AM

To: ASP Databases

Subject: [asp_databases] Creating a List Box without duplicate records





I'd like to create a list box and populate it with records from an 

Access

DB. There will be several dublicate values in the database. How do I

filter out and duplicates? My code as it exists now is as follows:



<% Dim oRSlist, StrConn

set oRSlist =3D Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=3DInventory"

oRSlist.MoveFirst

%>

<form name=3D"form1" method=3D"get" action=3D"test.asp">

  <p> SELECT NAME:

    <SELECT name=3D"GLCname" size=3D"1">

      <%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE=3D'" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=3Dnothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE=3D"submit">

  </p>

</form>



Thanks for any help you can offer.

Message #4 by "Ian Richardson" <ian@i...> on Fri, 25 May 2001 20:34:29 +0100

try this:

"SELECT DISTINCT yout fieldname FROM  yourtblName WHERE '" & (your

condition) & "' "



-----Original Message-----

From: linda.sulflow@b... [mailto:linda.sulflow@b...]

Sent: 25 May 2001 13:51

To: ASP Databases

Subject: [asp_databases] Creating a List Box without duplicate records





I'd like to create a list box and populate it with records from an Access

DB. There will be several dublicate values in the database. How do I

filter out and duplicates? My code as it exists now is as follows:



<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test.asp">

  <p> SELECT NAME:

    <SELECT name="GLCname" size="1">

      <%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE="submit">

  </p>

</form>



Thanks for any help you can offer.

Message #5 by linda.sulflow@b... on Fri, 25 May 2001 17:16:00

I've tried the DISTINCT OPTION, but I still get everything in the 

database. What am I doing wrong.  Attached is my code:



<%LANGUAGE="VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body bgcolor="#FFFFFF" text="#000000">

<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

  <p> SELECT NAME: 

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p> 

    <INPUT TYPE="submit">

  </p>

</form>



</body>

</html>





> I'd like to create a list box and populate it with records from an 

Access 

> DB. There will be several duplicate values in the database. How do I 

> filter out any duplicates? My code as it exists now is as follows:

> 

> <% Dim oRSlist, StrConn

> set oRSlist = Server.CreateObject("ADODB.recordset")

> 

> oRSlist.Open "Inventory", "DSN=Inventory"

> oRSlist.MoveFirst

> %>

> <form name="form1" method="get" action="test.asp">

>   <p> SELECT NAME: 

>     <SELECT name="GLCname" size="1">

>       <%

> 	Do While NOT oRSlist.EOF

> 		Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

> 		Response.Write oRSlist("GLC") & "</OPTION>"

> 		oRSlist.MoveNext

> 	Loop

> 	oRSlist.Close

> 	Set oRSlist=nothing

> %>

>     </SELECT>

>   </p>

>   <p> 

>     <INPUT TYPE="submit">

>   </p>

> </form>

>

Message #6 by "Linda" <linda.sulflow@b...> on Fri, 25 May 2001 20:36:33


The SELECT DISTINCT doesn't seem to work. Here's what my code looks like. 

The problem seems to be with my Response.Write statements. It's still 

pulling every record in the database. Please HELP, what am I doing wrong?



<%LANGUAGE="VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body bgcolor="#FFFFFF" text="#000000">

<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

  <p> SELECT NAME: 

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p> 

    <INPUT TYPE="submit">

  </p>

</form>



</body>

</html>

Message #7 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Sat, 26 May 2001 11:12:21 +0200

Hi!



Try this instead:



<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "SELECT DISTINCT GLC FROM inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

<p> SELECT NAME=GLC>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

</SELECT>







You have Distinct on the wrong place seems to me...





Hakan





-----Original Message-----

From: Linda [mailto:linda.sulflow@b...]

Sent: den 25 maj 2001 20:37

To: ASP Databases

Subject: [asp_databases] RE: Creating a List Box without duplicate

records







The SELECT DISTINCT doesn't seem to work. Here's what my code looks like.

The problem seems to be with my Response.Write statements. It's still

pulling every record in the database. Please HELP, what am I doing wrong?



<%LANGUAGE="VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body bgcolor="#FFFFFF" text="#000000">

<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

  <p> SELECT NAME:

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE="submit">

  </p>

</form>



</body>

</html>

Message #8 by "John Stallings" <jstallings@i...> on Fri, 25 May 2001 17:18:16 -0700

You are mixed up in the interpretations of the other peoples comments.  I

believe your code should look like the following:



...

...



<%  Dim oRSlist, StrConn

 set oRSlist = Server.CreateObject("ADODB.recordset")

 oRSlist.Open "SELECT DISTINCT GLC FROM Inventory", "DSN=Inventory"

 oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

<p> SELECT NAME: <SELECT name="GLCname" size="1">

<%

 Do While NOT oRSlist.EOF

 Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

 Response.Write oRSlist("GLC") & "</OPTION>"

 oRSlist.MoveNext



...

...



-----Original Message-----

From: Linda [mailto:linda.sulflow@b...]

Sent: Friday, May 25, 2001 8:37 PM

To: ASP Databases

Subject: [asp_databases] RE: Creating a List Box without duplicate

records







The SELECT DISTINCT doesn't seem to work. Here's what my code looks like.

The problem seems to be with my Response.Write statements. It's still

pulling every record in the database. Please HELP, what am I doing wrong?



<%LANGUAGE="VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body bgcolor="#FFFFFF" text="#000000">

<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

  <p> SELECT NAME:

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE="submit">

  </p>

</form>



</body>

</html>

Message #9 by "Dallas Martin" <dmartin@z...> on Fri, 25 May 2001 20:32:05 -0400

DISTINCT means DISTINCT.

It appears that you are opening a TABLE in your database.



Here's my version of code that should work for you.



Remember to DIM all variables first before using them.



strSQL  = "SELECT DISTINCT glc FROM inventory ORDER BY glc"



set rs = YourConnection.Execute(strSQL)



' assumes your query will return data, else you shoud check for empty

recordset

if NOT rs.eof then

   arrGLC = rs.GetRows()

end if



rs.close

set rs = nothing

YourConnection.close

set YourConnection = Nothing



.....all your opening html code



<%

if IsArray(arrGLC) then

  y = ubound(arrGLC,2)

  response.write("<select name=""glc"" size=""1""><option

value=""None"">None Selected</option>")

  for x = 0 to y

      response.write("<option value=""")

      response.write(arrGLC(0,x)

      response.write("">")

      response.write(arrGLC(0,x)

      response.write("</option>")

  next

else

   response.write("<select name=""glc"" size=""1""><option

value=""None"">None Selected</option>")

  response.write("</select>")

end if

%>



Dallas



----- Original Message -----

From: <linda.sulflow@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, May 25, 2001 5:16 PM

Subject: [asp_databases] Re: Creating a List Box without duplicate records





> I've tried the DISTINCT OPTION, but I still get everything in the

> database. What am I doing wrong.  Attached is my code:

>

> <%LANGUAGE="VBSCRIPT" %>

> <html>

> <head>

> <title>Untitled Document</title>

> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

> </head>

>

> <body bgcolor="#FFFFFF" text="#000000">

> <% Dim oRSlist, StrConn

> set oRSlist = Server.CreateObject("ADODB.recordset")

>

> oRSlist.Open "Inventory", "DSN=Inventory"

> oRSlist.MoveFirst

> %>

> <form name="form1" method="get" action="test1.asp">

>   <p> SELECT NAME:

>     <SELECT DISTINCT GLC FROM inventory>

> <%

> Do While NOT oRSlist.EOF

> Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

> Response.Write oRSlist("GLC") & "</OPTION>"

> oRSlist.MoveNext

> Loop

> oRSlist.Close

> Set oRSlist=nothing

> %>

>     </SELECT>

>   </p>

>   <p>

>     <INPUT TYPE="submit">

>   </p>

> </form>

>

> </body>

> </html>

>

>

> > I'd like to create a list box and populate it with records from an

> Access

> > DB. There will be several duplicate values in the database. How do I

> > filter out any duplicates? My code as it exists now is as follows:

> >

> > <% Dim oRSlist, StrConn

> > set oRSlist = Server.CreateObject("ADODB.recordset")

> >

> > oRSlist.Open "Inventory", "DSN=Inventory"

> > oRSlist.MoveFirst

> > %>

> > <form name="form1" method="get" action="test.asp">

> >   <p> SELECT NAME:

> >     <SELECT name="GLCname" size="1">

> >       <%

> > Do While NOT oRSlist.EOF

> > Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

> > Response.Write oRSlist("GLC") & "</OPTION>"

> > oRSlist.MoveNext

> > Loop

> > oRSlist.Close

> > Set oRSlist=nothing

> > %>

> >     </SELECT>

> >   </p>

> >   <p>

> >     <INPUT TYPE="submit">

> >   </p>

> > </form>

> >

>

Message #10 by "Ian Richardson" <ian@i...> on Sat, 26 May 2001 06:53:53 +0100

As far as I can see you have not used an SQL statement in this code.



Try the code below:



<%

Set oRSList = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT distinct GLC FROM Inventory"

oRSList.Open strSQL, StrConn, adOpenStatic, adLockOptimistic

%>

<form name="form1" method="get" action="test1.asp">

  <p> <SELECT NAME="yourselectname">

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= " & oRSlist("GLC") & ">"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

		Loop

	oRSList.Close

	Set oRSlist=nothing

%>

    </SELECT>



-----Original Message-----

From: linda.sulflow@b... [mailto:linda.sulflow@b...]

Sent: 25 May 2001 17:16

To: ASP Databases

Subject: [asp_databases] Re: Creating a List Box without duplicate

records





I've tried the DISTINCT OPTION, but I still get everything in the

database. What am I doing wrong.  Attached is my code:



<%LANGUAGE="VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>



<body bgcolor="#FFFFFF" text="#000000">

<% Dim oRSlist, StrConn

set oRSlist = Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=Inventory"

oRSlist.MoveFirst

%>

<form name="form1" method="get" action="test1.asp">

  <p> SELECT NAME:

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE= '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=nothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE="submit">

  </p>

</form>



</body>

</html>





> I'd like to create a list box and populate it with records from an

Access

> DB. There will be several duplicate values in the database. How do I

> filter out any duplicates? My code as it exists now is as follows:

>

> <% Dim oRSlist, StrConn

> set oRSlist = Server.CreateObject("ADODB.recordset")

>

> oRSlist.Open "Inventory", "DSN=Inventory"

> oRSlist.MoveFirst

> %>

> <form name="form1" method="get" action="test.asp">

>   <p> SELECT NAME:

>     <SELECT name="GLCname" size="1">

>       <%

> 	Do While NOT oRSlist.EOF

> 		Response.Write "<OPTION VALUE='" & oRSlist("GLC") & "'>"

> 		Response.Write oRSlist("GLC") & "</OPTION>"

> 		oRSlist.MoveNext

> 	Loop

> 	oRSlist.Close

> 	Set oRSlist=nothing

> %>

>     </SELECT>

>   </p>

>   <p>

>     <INPUT TYPE="submit">

>   </p>

> </form>

>

Message #11 by Hal Levy <hal.levy@s...> on Tue, 29 May 2001 09:33:07 -0400

Linda, the change needs to be made in your SQL Statement. Which, from 

your

code, seems to be a stored procedure.



Talk to whoever is in charge of managing the database.





Hal Levy

StarMedia Network, Inc.

Intranet Development Manager



-----Original Message-----

From: Linda [mailto:linda.sulflow@b...]

Sent: Friday, May 25, 2001 4:37 PM

To: ASP Databases

Subject: [asp_databases] RE: Creating a List Box without duplicate

records







The SELECT DISTINCT doesn't seem to work. Here's what my code looks 

like.

The problem seems to be with my Response.Write statements. It's still

pulling every record in the database. Please HELP, what am I doing 

wrong?



<%LANGUAGE=3D"VBSCRIPT" %>

<html>

<head>

<title>Untitled Document</title>

<meta http-equiv=3D"Content-Type" content=3D"text/html; 

charset=3Diso-8859-1">

</head>



<body bgcolor=3D"#FFFFFF" text=3D"#000000">

<% Dim oRSlist, StrConn

set oRSlist =3D Server.CreateObject("ADODB.recordset")



oRSlist.Open "Inventory", "DSN=3DInventory"

oRSlist.MoveFirst

%>

<form name=3D"form1" method=3D"get" action=3D"test1.asp">

  <p> SELECT NAME:

    <SELECT DISTINCT GLC FROM inventory>

<%

	Do While NOT oRSlist.EOF

		Response.Write "<OPTION VALUE=3D '" & oRSlist("GLC") & "'>"

		Response.Write oRSlist("GLC") & "</OPTION>"

		oRSlist.MoveNext

	Loop

	oRSlist.Close

	Set oRSlist=3Dnothing

%>

    </SELECT>

  </p>

  <p>

    <INPUT TYPE=3D"submit">

  </p>

</form>



</body>

</html>

Return to Index

P2P Archives