How does the OLEDB driver handle RS Updates?

[John Beschler]

I know (now) that using ADO recordsets for updates is a
bad idea, and I now longer use them for new pages we
create; howevre, I just have a question for one of the MS
gurus here.

Several people here have mentioned that if you use an ADO
recordset for updates, the OLEDB driver converts that to
SQL anyway. I'm curious as to the specifics.

Let's say I code:
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM tblTemp WHERE ID = 133"...
objRS("Name") = "Joe"
objRS("Address") = "19 Main St."
..
..
..
objRS.Update
objRS.Close

Does OLEDB wait for the objRS.Update statement and package
the whole update into one SEL statement, or does it
generate an SQL UPDATE for each line that puts something
in the buffer?

I'm just curious.

Thanks,
John

[Ray at]

I don't have an answer to your question, but instead of going through all
the recordset nonsense, why don't you just do this?

objYourADOConnection.Execute "UPDATE tblTemp SET [Name]='Joe',[Address]='19
Mian St.' WHERE [ID]=133"

Ray at work

"John Beschler" <giles@geewhiz.com> wrote in message
news:052901c38e99$67827810$a301280a@phx.gbl...

> I know (now) that using ADO recordsets for updates is a
> bad idea, and I now longer use them for new pages we
> create; howevre, I just have a question for one of the MS
> gurus here.
>
> Several people here have mentioned that if you use an ADO
> recordset for updates, the OLEDB driver converts that to
> SQL anyway. I'm curious as to the specifics.
>
> Let's say I code:
> Set objRS = Server.CreateObject("ADODB.RecordSet")
> objRS.Open "SELECT * FROM tblTemp WHERE ID = 133"...
> objRS("Name") = "Joe"
> objRS("Address") = "19 Main St."
> .
> .
> .
> objRS.Update
> objRS.Close
>
> Does OLEDB wait for the objRS.Update statement and package
> the whole update into one SEL statement, or does it
> generate an SQL UPDATE for each line that puts something
> in the buffer?
>
> I'm just curious.
>
> Thanks,
> John
>

[Bob Barrows]

Check this out:
[url]http://web.archive.org/web/20010417214447/http://mspress.microsoft.com/books/ww/sampchap/3445.htm[/url]

John Beschler wrote:

> I know (now) that using ADO recordsets for updates is a
> bad idea, and I now longer use them for new pages we
> create; howevre, I just have a question for one of the MS
> gurus here.
>
> Several people here have mentioned that if you use an ADO
> recordset for updates, the OLEDB driver converts that to
> SQL anyway. I'm curious as to the specifics.
>
> Let's say I code:
> Set objRS = Server.CreateObject("ADODB.RecordSet")
> objRS.Open "SELECT * FROM tblTemp WHERE ID = 133"...
> objRS("Name") = "Joe"
> objRS("Address") = "19 Main St."
> .
> .
> .
> objRS.Update
> objRS.Close
>
> Does OLEDB wait for the objRS.Update statement and package
> the whole update into one SEL statement, or does it
> generate an SQL UPDATE for each line that puts something
> in the buffer?
>
> I'm just curious.
>
> Thanks,
> John

[Aaron Bertrand - MVP]

> objRS.Open "SELECT * FROM tblTemp WHERE ID = 133"...

Ugh, why do you think you need to bring *all* of that data into the ASP
page, just to update it? As Ray suggests, run an UPDATE statement. Gobs
more efficient, and much easier to debug.

A

[MSFT]

Hi John,

ADO wouldn't generate Update statement for each row inthe recordset. It
will only generate for modified ones and new records. If you are interested
on this issue, you may try to update a SQL server database with ADO, and
use SQL server profiler to trace all SQL statement passed from ADO to SQL
server.



Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[John Beschler]

Aaron, Ray, Bob, and Luke,

First thanks to all of you for responding.

I know that using an RS is a bad thing. I also do not ever
use SELECT * in production code. I've learned all that
from the many posts you guys have made. As a matter of
fact I have written generic functions in VBScript to
handle both updates and inserts for any table in any of
our SQL databases.

It's just that some of you have repeatedly stated that the
OLEDB driver always converts an ADO commands to the
equivilant SQL statements and out of curiousity I wondered
if the driver cached all the statements for the update
until it processed the RS.Update or it handled each one
individually.

Luke, thanks for your suggestion to watch the profiler. I
will give that a try.

Again, let me reiterate that we never use SELECT * in
production code (anymore) nor do we use ADO recordsets.
(Except for reading data to be displayed.)

You guys have been a great help to me over the last couple
years. Thanks to all.

>-----Original Message-----

>> objRS.Open "SELECT * FROM tblTemp WHERE ID = 133"...

>
>Ugh, why do you think you need to bring *all* of that

data into the ASP

>page, just to update it? As Ray suggests, run an UPDATE

statement. Gobs

>more efficient, and much easier to debug.
>
>A
>
>
>.
>

[¥xÆW·L³n§Þ³N³B¤ä´©³¡]

Hi John,

ADO will create and execute the SQL command like "Insert into..",
"Update..." after you call the recordset's Update method, it wouldn't store
such a command for each row. in fact, there are symbols in each to
indicate if the row has been changed or it is a new row. ADo will execute
Update or Insert command base on these symbols.

Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Bob Barrows]

It's not clear to me if you read the link I posted. David Sceppa does a very
good job of explaining all this in the article I directed you to.

Bob Barrows