|
 |
asp_databases thread: Multiple Select Boxes
Message #1 by PenPrints <info@p...> on Fri, 26 Jan 2001 23:08:07 -0500
|
|
Question:
Do I have to create 2 separate Recordset objects in order to populate 2
different select boxes coming from 2 unrelated tables in 1 database?
Or can I do some sort of join using SQL and then call the appropriate table
for my needs?
Thanks
Mitch
_______________________________________________
PenPrints - Clamshell Boxes, Hand Bound Books,
and Fine Art
http://www.penprints.com
Message #2 by Imar Spaanjaars <Imar@S...> on Sat, 27 Jan 2001 15:14:33 +0100
|
|
As you say, a join wouldn't be possible because the two tables are unrelated.
In SQL you could retrieve two recordsets by issuing two SELECT statements
right after each other. Then you could use .NextRecordset property of the
recordset object. However, IMO a better approach is to execute two queries
right after each other with only one recordset object, like this:
' Create Connection
' Create Recordset
Dim sSQL
Dim arrListbox1
Dim arrListbox2
sSQL = "SELECT ID, Description FROM Table1 ORDER BY Description"
Connection.Open
With Recordset
.Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
if not .EOF then
arrListbox1 = .GetRows()
end if
.Close()
sSQL = "SELECT ID, Description FROM Table2 ORDER BY Description"
.Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
if not .EOF then
arrListbox2 = .GetRows()
end if
.Close()
End with
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
Now you have two arrays with the contents of the recordset. Use a loop in
combination with UBound to fill your SELECTs
HtH
Imar
At 08:08 AM 1/27/2001 -0800, you wrote:
>Question:
>Do I have to create 2 separate Recordset objects in order to populate 2
>different select boxes coming from 2 unrelated tables in 1 database?
>Or can I do some sort of join using SQL and then call the appropriate table
>for my needs?
>
>Thanks
>
>Mitch
Message #3 by "Dallas Martin" <dmartin@z...> on Sat, 27 Jan 2001 13:51:25 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0008_01C08868.3D580990
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You can also combine the two SELECT statements into one string and
submit to the db.
sSQL =3D "SELECT ID, Description FROM Table1 ORDER BY Description;
SELECT ID, Description FROM Table2 ORDER BY Description"
'Please notice the semi-colon separating the two SELECT queries.
set rs1 =3D Connection.Execute(sSQL)
arrListBox1 =3D rs1.GetRows()
set rs2 =3D rs1.NextRecordSet
arrListBox2 =3D rs2.GetRows()
rs1.close
rs2.close
Connection.Close
set rs1 =3D nothing
set rs2 =3D nothing
set connection =3D Nothing
----- Original Message -----
From: "Imar Spaanjaars" <Imar@S...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 27, 2001 9:14 AM
Subject: [asp_databases] Re: Multiple Select Boxes
> As you say, a join wouldn't be possible because the two tables are
unrelated.
> In SQL you could retrieve two recordsets by issuing two SELECT
statements
> right after each other. Then you could use .NextRecordset property of
the
> recordset object. However, IMO a better approach is to execute two
queries
> right after each other with only one recordset object, like this:
>
> ' Create Connection
> ' Create Recordset
> Dim sSQL
> Dim arrListbox1
> Dim arrListbox2
>
> sSQL =3D "SELECT ID, Description FROM Table1 ORDER BY Description"
>
> Connection.Open
> With Recordset
> .Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly,
adCmdText
> if not .EOF then
> arrListbox1 =3D .GetRows()
> end if
> .Close()
> sSQL =3D "SELECT ID, Description FROM Table2 ORDER BY
Description"
> .Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly,
adCmdText
> if not .EOF then
> arrListbox2 =3D .GetRows()
> end if
> .Close()
> End with
> Set Recordset =3D Nothing
> Connection.Close
> Set Connection =3D Nothing
>
> Now you have two arrays with the contents of the recordset. Use a loop
in
> combination with UBound to fill your SELECTs
>
>
> HtH
>
> Imar
>
> At 08:08 AM 1/27/2001 -0800, you wrote:
> >Question:
> >Do I have to create 2 separate Recordset objects in order to populate
2
> >different select boxes coming from 2 unrelated tables in 1 database?
> >Or can I do some sort of join using SQL and then call the appropriate
table
> >for my needs?
> >
> >Thanks
> >
> >Mitch
>
>
> ---
> FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
> INSIGHTS IN YOUR INBOX!
> Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
> developments from the experts. Sign up for one or more of EarthWeb?s
> FREE IT newsletters at http://www.earthweb.com today!
$subst('Email.Unsub')
>
Message #4 by Imar Spaanjaars <Imar@S...> on Sun, 28 Jan 2001 11:30:27 +0100
|
|
Or even more efficient: do not create the second recordset. After
.GetRows() you won't need rs1 anymore, so you can set the results of the
.NextRecordSet to itself:
arrListBox1 = rs1.GetRows()
set rs1 = rs1.NextRecordSet
arrListBox2 = rs1.GetRows()
Set rs1 = Nothing
Imar
At 01:51 PM 1/27/2001 -0500, you wrote:
>You can also combine the two SELECT statements into one string and submit
>to the db.
>
>sSQL = "SELECT ID, Description FROM Table1 ORDER BY Description; SELECT
>ID, Description FROM Table2 ORDER BY Description"
>
>'Please notice the semi-colon separating the two SELECT queries.
>
>set rs1 = Connection.Execute(sSQL)
>arrListBox1 = rs1.GetRows()
>set rs2 = rs1.NextRecordSet
>arrListBox2 = rs2.GetRows()
>rs1.close
>rs2.close
>Connection.Close
>set rs1 = nothing
>set rs2 = nothing
>set connection = Nothing
>
>
>
>
>
>
>----- Original Message -----
>From: "Imar Spaanjaars" <<mailto:Imar@S...>Imar@S...>
>To: "ASP Databases"
><<mailto:asp_databases@p...>asp_databases@p...>
>Sent: Saturday, January 27, 2001 9:14 AM
>Subject: [asp_databases] Re: Multiple Select Boxes
>
> > As you say, a join wouldn't be possible because the two tables are
> unrelated.
> > In SQL you could retrieve two recordsets by issuing two SELECT statements
> > right after each other. Then you could use .NextRecordset property of the
> > recordset object. However, IMO a better approach is to execute two queries
> > right after each other with only one recordset object, like this:
> >
> > ' Create Connection
> > ' Create Recordset
> > Dim sSQL
> > Dim arrListbox1
> > Dim arrListbox2
> >
> > sSQL = "SELECT ID, Description FROM Table1 ORDER BY Description"
> >
> > Connection.Open
> > With Recordset
> > .Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly,
> adCmdText
> > if not .EOF then
> > arrListbox1 = .GetRows()
> > end if
> > .Close()
> > sSQL = "SELECT ID, Description FROM Table2 ORDER BY Description"
> > .Open sSQL, Connection, adOpenForwardOnly, adLockReadOnly,
> adCmdText
> > if not .EOF then
> > arrListbox2 = .GetRows()
> > end if
> > .Close()
> > End with
> > Set Recordset = Nothing
> > Connection.Close
> > Set Connection = Nothing
> >
> > Now you have two arrays with the contents of the recordset. Use a loop in
> > combination with UBound to fill your SELECTs
> >
> >
> > HtH
> >
> > Imar
> >
> > At 08:08 AM 1/27/2001 -0800, you wrote:
> > >Question:
> > >Do I have to create 2 separate Recordset objects in order to populate 2
> > >different select boxes coming from 2 unrelated tables in 1 database?
> > >Or can I do some sort of join using SQL and then call the appropriate
> table
> > >for my needs?
> > >
> > >Thanks
> > >
> > >Mitch
> >
> >
> > ---
> > FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
> > INSIGHTS IN YOUR INBOX!
> > Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
> tips, tools, and
> > developments from the experts. Sign up for one or more of EarthWeb?s
> > FREE IT newsletters at <http://www.earthweb.com>http://www.earthweb.com
> today!
> <mailto:dmartin@z...>dmartin@z...
> <mailto:$subst('Email.Unsub')>$subst('Email.Unsub')
> > ---
>FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
>INSIGHTS IN YOUR INBOX!
>Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips,
>tools, and
>developments from the experts. Sign up for one or more of EarthWeb?s
>FREE IT newsletters at http://www.earthweb.com today!
|
|
|
View Cart
