Execute method vs Recordset object

[Thomas Scheiderich]

Why would you use the Recordset object over the Execute method of
getting data from your Sql database.

For example, I have the following:

Execute Method
*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=connectionToD atabase.Execute ("Select * from Customers")
*************** *************** *************** *************** ***

*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=Server.Create Object("ADODB.R ecordset")
recordCollectio n.Open "Select * from Customers",conn ectionToDatabas e
*************** *************** *************** *************** ***

They both seem to work about the same. Why is one better than the other?

In the Execute method you just do a connection and select in one statement.

In the Recordset method, you have to create the object and then do a
connect and select.

Thanks,

Tom

[Aaron Bertrand [MVP]]

Re: Execute method vs Recordset object

Some pros and cons here, mostly cons of using ADODB.Recordset

http://www.aspfaq.com/2191

I prefer the connection object alone; in just about every case where one
might say, "you HAVE to use ADODB.Recordset property for this," I have found
or developed a better solution without using it...

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"Thomas Scheiderich" <tfs@deltanet.c om> wrote in message
news:4029853F.4 080905@deltanet .com...[color=blue]
> Why would you use the Recordset object over the Execute method of
> getting data from your Sql database.
>
> For example, I have the following:
>
> Execute Method
> *************** *************** *************** *************** ***
> Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
> connectionToDat abase.Connectio nTimeout = 60
> connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "
>
> Set RecordCollectio n=connectionToD atabase.Execute ("Select * from[/color]
Customers")[color=blue]
> *************** *************** *************** *************** ***
>
> *************** *************** *************** *************** ***
> Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
> connectionToDat abase.Connectio nTimeout = 60
> connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "
>
> Set RecordCollectio n=Server.Create Object("ADODB.R ecordset")
> recordCollectio n.Open "Select * from Customers",conn ectionToDatabas e
> *************** *************** *************** *************** ***
>
> They both seem to work about the same. Why is one better than the other?
>
> In the Execute method you just do a connection and select in one[/color]
statement.[color=blue]
>
> In the Recordset method, you have to create the object and then do a
> connect and select.
>
> Thanks,
>
> Tom
>[/color]

[Chris Barber]

Re: Execute method vs Recordset object

Using the recordset object allows you to get a 'Disconnected Recordset'
which is the 'preferred' method if you are looking for maximum scaleability.
A disconnected recordset does not hold expensive connections to the database
except during retrieval of the data and updating of new data back to the
server (see BatchUpdate).

http://www.4guysfromrolla.com/webtech/080101-1.shtml

However, there are times (for example when wanting to work with a large
recordset) that a standard server-side cursor - thus a fully connected
recordset - is beneficial since the requirement of a disconnected recordset
is that *all* the data be retrieved to the client in one go. A server-side
cursor only retrieves each record as navigated.

So .. pros and cons.

I have a VB class that returns disconnected recordsets and I generally try
and ensure that each recordset is as small as possible. Even for looping
through large datasets I can generally retrieve a recordset of the record
IDs required and then do individual requests and updates for each record
identified.

Chris Barber.

"Thomas Scheiderich" <tfs@deltanet.c om> wrote in message
news:4029853F.4 080905@deltanet .com...
Why would you use the Recordset object over the Execute method of
getting data from your Sql database.

For example, I have the following:

Execute Method
*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=connectionToD atabase.Execute ("Select * from Customers")
*************** *************** *************** *************** ***

*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=Server.Create Object("ADODB.R ecordset")
recordCollectio n.Open "Select * from Customers",conn ectionToDatabas e
*************** *************** *************** *************** ***

They both seem to work about the same. Why is one better than the other?

In the Execute method you just do a connection and select in one statement.

In the Recordset method, you have to create the object and then do a
connect and select.

Thanks,

Tom

[Chris Barber]

Re: Execute method vs Recordset object

Also, a disconnected recordset can be passed to remote clients to use (eg.
cache) and either throwaway or return with updates. A classic example is
putting it into an MSMQ message to a remote laptop machine that may be
offline for a day or so. When it returns it can send that same (persisted to
disk as XML as presume) recordset back to the DB to update the entries. If
any records fail to update (eg. deleted in the interim) then each records
'status' can be interrogated to see why it failed thus allowing the app /
user to make a decision about what to do.

Very groovy and to some extent the 'defacto' standard preferred by Microsoft
for large scale database applications with lots of potential concurrency of
connections (because of the minimal time that a connection is held to the
DB). There is nor real degradation in performance for this connect and
reconnect scenario as long as connection pooling becomes activated
(reconnects are simply provided from the pool and are almost instantaneous).

Chris.

"Thomas Scheiderich" <tfs@deltanet.c om> wrote in message
news:4029853F.4 080905@deltanet .com...
Why would you use the Recordset object over the Execute method of
getting data from your Sql database.

For example, I have the following:

Execute Method
*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=connectionToD atabase.Execute ("Select * from Customers")
*************** *************** *************** *************** ***

*************** *************** *************** *************** ***
Set connectionToDat abase=Server.Cr eateObject("ADO DB.Connection")
connectionToDat abase.Connectio nTimeout = 60
connectionToDat abase.Open "DSN=Customer;u ser id=SA;password= "

Set RecordCollectio n=Server.Create Object("ADODB.R ecordset")
recordCollectio n.Open "Select * from Customers",conn ectionToDatabas e
*************** *************** *************** *************** ***

They both seem to work about the same. Why is one better than the other?

In the Execute method you just do a connection and select in one statement.

In the Recordset method, you have to create the object and then do a
connect and select.

Thanks,

Tom

[Bob Barrows]

Re: Execute method vs Recordset object

Thomas Scheiderich wrote:[color=blue]
> Why would you use the Recordset object over the Execute method of
> getting data from your Sql database.
>[/color]

You are ALWAYS using a recordset object, even if you don't create one
explicitly. When Execute is used, a recordset object with the default
property settings is created implicitly (behind the scenes). The default
property settings are:

CursorLocation = adUseServer (server-side)
CursorType = adOpenForwardOn ly (forward-only - non-scrollable)

According to the documentation:
LockType = adLockReadOnly
but in my experience, this does not appear to be true. It's irrelevant since
I rarely need to make updates to my recordsets, using them only to retrieve
data for display purposes.

If you need any other settings for these or any other recordset properties
(which should be very rare in asp, if you are using good programming
practices) then you need to explicitly create the recordset object yourself
and set the properties. then use Open to open it (if you use Execute, your
carefully created object will NOT get used).

In addition, if you wish to use the "stored-procedure-as-connection-method"
technique to execute a stored procedure, then you need to create the
recordset object explicitly:
set rs=createobject ("adodb.records et")
cn.storedproced urename param1,...,parm N, rs

HTH,
Bob Barrows
--
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"