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"

Recordset Modification and Copy

Please Help.

I have written a custom DTS task in an ActiveX Script. My script creates three recordsets from three existing tables in an SQL Server 2k DB by copying all the data in these tables.

My script then modifies the data in these recordsets including dropping several records, creates a new table and inserts the modified data into the new table.

Unfortunately, I need to keep my original data unmodified...and currently my code changes my original data when it modifies the record sets.

Does anyone have a method for modifying a recordset without modifying the source data? I am writing my ActiveX Script through the VB scripting language.

From what I have read on the internet some people suggest creating a new recordset, copying the data from the original recordset to the new recordset, and then modify and insert the new recordset. This is supposed to keep my source data unchanged. Unfortunately, I have not been successful with this method mainly because I am having trouble creating a new recordset and copying my original recordset data into this new object.

Thank you in advance for any help or input you might have!!!

-TRocheI assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.|||I don't think this will work. You see I have to manipulate the data through several DO Until loops. The constraint on my DO loop is

"DO UNTIL rs.EOF"

Before I set the recordset ActiveConnection to nothing It tells me I have to first close the recordset. Unfortunately, I can not run the syntax of the above loop if the recrordset is closed, and if I open the recordset with a new connection it again changes my source data.

Does anyone have any additional insight?

Thanks!

-TRoche|||create a function READ UNCOMMITTED transaction isolation level (you can do it with nolock optimizer hint) and do your DO UNTIL against the rs that points to it.|||Wow...I am just a beginner at this stuff...I will look into it, but do you have a sample code for this by chance?

thanks,

TRoche|||create function dbo.fn_select_star_from_your_table (
@.parm1 <data type>,
@.parm2 <data type>,
@.parm3 <data type> ) returns table
as return (
select * from your_table (nolock)
where field1 = @.parm1
and field2 = @.parm2
and @.field3 = parm3)
go|||Hmmmmm....ok to start I am writing a custom DTS Task, using an ActiveX Script in the DTS Designer, coding in VBScript

Well I am having a few problems with the previous comments. First off I can't get the code to drop the function ms_sql_dba advised. Here is my DROP code for a function called FUNSTUFF:

dim Dropx
set Dropx = CreateObject("ADODB.Command")

Dropx = "Drop Function [dbo].[FUNSTUFF]"
connection.execute Dropx

I am only trying to drop the function because if I run the code multiple times it errors saying the the object "FunStuff" already exists in the database...and I looked...and it does...dang Does anyone see something wrong with the above DROP code?

Also, ms_sql_dba, I am not sure I understand what your function does. Here is how I thought to use your advice.

Write a function called ADVICE as follows:
dim ADVICE
set ADVICE = CreateObject("ADODB.Command")

ADVICE = "CREATE FUNCTION dbo.FUNSTUFF (@.time float, @.Position float ) RETURNS Table AS RETURN (select * from GPSy (nolock) where ty = time and GPS_y = Position)"

then:
Open.recordset ADVICE, Connection, adOpenKeyset

Then write my do loop:
DO UNTIL recordset.EOF
blah, blah, blah
Loop

Unfortunately, I run into all kinds of errors like saying the operation can not be performed if the recordset is closed. But if I write an open command like:

open.recordset.connection

I get the error that the function FUNSTUFF alreadyexists in the database.

Hmmmmmmmm...

Does anyone have any advice? Do I have to write the Function code before every loop or a drop code after every loop...or...hmmm...I am just confused about how the function works.

A million thanks to anyone who has any input!!

-TRoche