How can I add to existing Record Set?

[michaaal]

I use code like this in order to create a recordset and enter data into the
recordset:

strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%red%';"
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &
Server.MapPath("database.mdb")
set recset = conn.execute (strSQL)
response.write(recset("Item"))
recset.movenext

How can I do an additional query and add it to the existing recordset
without removing the data from the first recordset. Maybe something like
this?...

strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%blue%';"
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &
Server.MapPath("database.mdb")
'I changed the line below as well as the first line
set recset.AddToRecordset = conn.execute (strSQL)
response.write(recset("Item"))
recset.movenext

I realize that I could change the first line to:
strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%red%' AND Desc
LIKE '%blue%';"

.....but I would like to avoid that in order to avoid a bunch of re-coding of
my site.

Any ideas?

[Bob Barrows [MVP]]

michaaal wrote:

> I use code like this in order to create a recordset and enter data
> into the recordset:
>
> strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%red%';"
> set conn = Server.CreateObject("ADODB.Connection")
> conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &
> Server.MapPath("database.mdb")
> set recset = conn.execute (strSQL)
> response.write(recset("Item"))
> recset.movenext
>
> How can I do an additional query and add it to the existing recordset
> without removing the data from the first recordset. Maybe something
> like this?...
>
> strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%blue%';"
> set conn = Server.CreateObject("ADODB.Connection")
> conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" &
> Server.MapPath("database.mdb")
> 'I changed the line below as well as the first line
> set recset.AddToRecordset = conn.execute (strSQL)
> response.write(recset("Item"))
> recset.movenext

It's not possible. Forget about this approach.

>
> I realize that I could change the first line to:
> strSQL = "SELECT * FROM InventoryItemList WHERE Desc LIKE '%red%' AND
> Desc LIKE '%blue%';"

You mean " ... OR Desc LIKE '%blue%'"

>
> ....but I would like to avoid that in order to avoid a bunch of
> re-coding of my site.
>
> Any ideas?

Several:
1. Open a new recordset that uses both the old and new criteria.
2. Use two recordsets
3. Add the data from each new recordset to an array.


Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.