Re: query on recordset in visual basic

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"ekkehard.horner" <ekkehard.horner@xxxxxxxx> wrote in message
news:478e0abe$0$25384$9b4e6d93@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Someone is asking me to do a "query on a recordset" in VBScript,
claiming that in Visual Basic this

set conn=ADODB.connection
set rs1=ADODB.recordset
set rs2=ADODB.recordset
conn=<<set to someDB>>
rs1.open "select ename,age from emp"

rs2.open "select ename from rs1 where age >10"

msgbox rs2.recordcount

works. I doubt that - how could someDB know/access rs1 -
but not being familiar with Visual Basic, I thought it
wise to ask the experts.

If it is possible in Visual Basic, I would be very grateful
for a pointer to pertaining docs/samples.

Anybody who can do this in any other language could make my
day by telling me about ist.

Thanks.

An example similar to code I use for SQL Server below. The connection string
varies with DBMS.
==============
Option Explicit

Dim strConnect, adoConnection, adoRecordset, strSQL
Dim strFirst, strLast

' Connection string for Windows Authentication.
' If default instance, omit "\MyInstance".
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=MyDatabase;" _
& "SERVER=MyServer\MyInstance"

' SQL query
strSQL = "SELECT FirstName, LastName " _
& "FROM dbo.Students"

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.Open

Set adoRecordset = CreateObject("ADODB.Recordset")
Set adoRecordset.ActiveConnection = adoConnection

adoRecordset.Source = strSQL
adoRecordset.Open

Do Until adoRecordset.EOF
strFirst = adoRecordset.Fields("Member_First_Name").Value
strLast = adoRecordset.Fields("Member_Last_Name").Value
Wscript.Echo strLast & ", " & strFirst
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--


.