Professional Web Applications Themes

How does the OLEDB driver handle RS Updates? - ASP Database

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 ...

  1. #1

    Default How does the OLEDB driver handle RS Updates?

    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

    John Beschler Guest

  2. #2

    Default Re: How does the OLEDB driver handle RS Updates?

    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" <gilesgeewhiz.com> wrote in message
    news:052901c38e99$67827810$a301280aphx.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
    >

    Ray at Guest

  3. #3

    Default Re: How does the OLEDB driver handle RS Updates?

    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

    Bob Barrows Guest

  4. #4

    Default Re: How does the OLEDB driver handle RS Updates?

    > 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


    Aaron Bertrand - MVP Guest

  5. #5

    Default RE: How does the OLEDB driver handle RS Updates?

    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.)

    MSFT Guest

  6. #6

    Default Re: How does the OLEDB driver handle RS Updates?

    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
    >
    >
    >.
    >
    John Beschler Guest

  7. #7

    Default Re: How does the OLEDB driver handle RS Updates?

    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.)

    台灣微軟技術處支援部 Guest

  8. #8

    Default Re: How does the OLEDB driver handle RS Updates?

    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


    Bob Barrows Guest

Similar Threads

  1. Oracle Table Access With Oracle OLEDB Driver
    By FSchmitthammer in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 19th, 03:41 AM
  2. Replies: 4
    Last Post: July 2nd, 01:11 PM
  3. Replies: 5
    Last Post: February 18th, 09:54 PM
  4. How does Access database handle concurrent updates?
    By Perdit in forum ASP Database
    Replies: 2
    Last Post: January 11th, 02:52 PM
  5. IBM Informix OLEDB driver bug
    By Thomas in forum Informix
    Replies: 2
    Last Post: July 16th, 02:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139