RecordSets

Im unsing VB6 SP6 and SQl 2000
Im creating a recordset using the following codsample
Dim ContraRS as ADODB.Recordset
Dim SQLString as String
Set ContraRS = New ADODB.Recordset
SQL String = "Select * from Table1 where Id = 2
ContraRS.CursorLocation = adUseClient
ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly, adLockReadOnly
This returns a recordset. Is there a way i can repeat theis process and
append to the data. The reason for this is im trying to obtain a list of
Banking transactions and there may be multiple id's to search for, non are
fixed as we have no way of knowing which clients (id's) will be submitting
data in on any days. I could build a dynamic SQl query with lots of 'AND Id
=
'' but im looking to see if there is a simplier wayWhere do these ID numbers come from? Maybe you can use a subquery:
SELECT *
FROM Table1
WHERE id IN
(SELECT id
FROM SomeOtherTable
WHERE some_date = @.date
/* @.date = the date of the data you are searching for */)
Best not to use SELECT * in production code - list the required column
names instead.
Make use of stored procedures where you can because that approach has
many advantages over executing SELECT statements directly from VB. If
you use a stored proc you can pass a list of IDs as parameters and use
them in an IN clause:
...WHERE id IN (@.id1, @.id2, @.id3, ...)
Finally, why are you still developing VB6, which was officially retired
as of yesterday ;-).
David Portas
SQL Server MVP
--|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
No. I would consider moving this processing into a stored procedure which
returns only the final recordset needed by the client.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
How are you deciding what id's to search for?
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"