dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: How to Update Identity Col on Disconnected Recordset

  1. #1
    hendry Guest

    How to Update Identity Col on Disconnected Recordset


    Hi,

    I've got a problem on updating my Disconnected Recordset. If I update a field
    which is specified as a type of identity column in the database, then it
    always failed. I got an error: "Multiple-step operation generated code errors.
    check each status value"

    For example, I've got a table named MyTable with three columns (A,B,C). I
    set Column A as identity column. Then I code my disconnected rs as follows:


    Dim RS as ADODB.Recordset

    set RS = new Recordset
    RS.cursorLocation = adUseClient
    RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    set RS.ActiveConnection = nothing

    '** try to Add new
    RS.AddNew
    RS.Fields("A") = 123
    RS.Fields("B") = "bla"
    RS.Fields("C") = "bla"
    RS.Update

    Either add new or just update would always fail. If I remove identity from
    column A then it runs smoothly, What's wrong here??

    Is there any workaround for this? BTW, I'm using MDAC 2.7

    Thanks for any help

    Hendry




  2. #2
    Chris Hylton Guest

    Re: How to Update Identity Col on Disconnected Recordset


    Why do you want to update the identity columns...this really doesn't make
    sense...the point is that it is populated automatically by the database when
    you issue the update (or updatebatch in the case of a disconnected recordset).
    Not sure why you'd want to do this.

    If you really need to insert a value here...you need to make the field something
    other than an identity...

    If you only want to do this locally for some purpose...knowing that the field
    will be set to something else once you reconnect and 'update' the database..if
    that's what you'll eventually do w/ the recordset...you could just select
    and numeric field w/ an alias and not even select your identify field in
    your select statement...

    for example...

    select 0 as whatever_field, customer_id, customer_name from tbl_whatever

    Then you can update 'whatever_field' to whatever you want...and when you
    go back to update the database, just send the data back in as insert statements
    or via SP...or through direct recordset update if it will even work w/ that
    extra field selected (not sure).

    Chris

    "hendry" <hendry@blg.co.id> wrote:
    >
    >Hi,
    >
    >I've got a problem on updating my Disconnected Recordset. If I update a

    field
    >which is specified as a type of identity column in the database, then it
    >always failed. I got an error: "Multiple-step operation generated code errors.
    >check each status value"
    >
    >For example, I've got a table named MyTable with three columns (A,B,C).

    I
    >set Column A as identity column. Then I code my disconnected rs as follows:
    >
    >
    > Dim RS as ADODB.Recordset
    >
    > set RS = new Recordset
    > RS.cursorLocation = adUseClient
    > RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    > set RS.ActiveConnection = nothing
    >
    > '** try to Add new
    > RS.AddNew
    > RS.Fields("A") = 123
    > RS.Fields("B") = "bla"
    > RS.Fields("C") = "bla"
    > RS.Update
    >
    >Either add new or just update would always fail. If I remove identity from
    >column A then it runs smoothly, What's wrong here??
    >
    >Is there any workaround for this? BTW, I'm using MDAC 2.7
    >
    >Thanks for any help
    >
    >Hendry
    >
    >
    >



  3. #3
    michael Guest

    Re: How to Update Identity Col on Disconnected Recordset


    If you are obtaining the disconnected recordset from the database, e.g. 'Select
    A, B, C from MyTable where 0=1' then the following is probably occuring.
    If your Identity column, columns A, is auto-incrementing, I believe you will
    never be able to set a value to that column. You would simply add values
    to columns B & C then give the recordset an open datasource, Filter the pending
    records and UpdateBatch.

    If you are creating a recordset on the client side without the database,
    e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps will
    have to be taken in the middle tier of the application. This recordset will
    not be able to be updated by adding a datasource and executing UpdateBatch.
    In the middle tier you will have to get an empty recordset from the database
    then set clientside recordset's column values B & C to the new empty recordset
    obtained from the database then updatebatch. After Updating you would simply
    set the clientside created recordset to nothing and return database recordset
    the was just updated back to the client.

    Hope this helps.

    Michael


    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Why do you want to update the identity columns...this really doesn't make
    >sense...the point is that it is populated automatically by the database

    when
    >you issue the update (or updatebatch in the case of a disconnected recordset).
    > Not sure why you'd want to do this.
    >
    >If you really need to insert a value here...you need to make the field something
    >other than an identity...
    >
    >If you only want to do this locally for some purpose...knowing that the

    field
    >will be set to something else once you reconnect and 'update' the database..if
    >that's what you'll eventually do w/ the recordset...you could just select
    >and numeric field w/ an alias and not even select your identify field in
    >your select statement...
    >
    >for example...
    >
    >select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >
    >Then you can update 'whatever_field' to whatever you want...and when you
    >go back to update the database, just send the data back in as insert statements
    >or via SP...or through direct recordset update if it will even work w/ that
    >extra field selected (not sure).
    >
    >Chris
    >
    >"hendry" <hendry@blg.co.id> wrote:
    >>
    >>Hi,
    >>
    >>I've got a problem on updating my Disconnected Recordset. If I update a

    >field
    >>which is specified as a type of identity column in the database, then it
    >>always failed. I got an error: "Multiple-step operation generated code

    errors.
    >>check each status value"
    >>
    >>For example, I've got a table named MyTable with three columns (A,B,C).

    >I
    >>set Column A as identity column. Then I code my disconnected rs as follows:
    >>
    >>
    >> Dim RS as ADODB.Recordset
    >>
    >> set RS = new Recordset
    >> RS.cursorLocation = adUseClient
    >> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >> set RS.ActiveConnection = nothing
    >>
    >> '** try to Add new
    >> RS.AddNew
    >> RS.Fields("A") = 123
    >> RS.Fields("B") = "bla"
    >> RS.Fields("C") = "bla"
    >> RS.Update
    >>
    >>Either add new or just update would always fail. If I remove identity from
    >>column A then it runs smoothly, What's wrong here??
    >>
    >>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>
    >>Thanks for any help
    >>
    >>Hendry
    >>
    >>
    >>

    >



  4. #4
    Hendry Guest

    Re: How to Update Identity Col on Disconnected Recordset


    Thanks very much for your helpful feedback guys.

    I do an update on clientside recordset just for UI purposes (displaying data).
    In the middle tier, the actual insert/update was done thru Stored Proc.
    I do this in order to avoid passing the new data (recordset) back to the
    UI (client) so that it'd always be one trip to the server.

    Based on all your suggestions, I've got an idea to append virtual field just
    right after the recordset created and then any insert/update on the clientside
    which related to auto-incrementing field will be done thru this field. My
    Application will check whether the virtual field is empty or not, if so then
    this is a new record made on the clientside therefore any update on the clientside
    will refer to this field.

    This is still only in my mind. I'll try to figure it out.

    Thanks

    Hendry

    "michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >
    >If you are obtaining the disconnected recordset from the database, e.g.

    'Select
    >A, B, C from MyTable where 0=1' then the following is probably occuring.
    >If your Identity column, columns A, is auto-incrementing, I believe you

    will
    >never be able to set a value to that column. You would simply add values
    >to columns B & C then give the recordset an open datasource, Filter the

    pending
    >records and UpdateBatch.
    >
    >If you are creating a recordset on the client side without the database,
    >e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps

    will
    >have to be taken in the middle tier of the application. This recordset will
    >not be able to be updated by adding a datasource and executing UpdateBatch.
    >In the middle tier you will have to get an empty recordset from the database
    >then set clientside recordset's column values B & C to the new empty recordset
    >obtained from the database then updatebatch. After Updating you would simply
    >set the clientside created recordset to nothing and return database recordset
    >the was just updated back to the client.
    >
    >Hope this helps.
    >
    >Michael
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Why do you want to update the identity columns...this really doesn't make
    >>sense...the point is that it is populated automatically by the database

    >when
    >>you issue the update (or updatebatch in the case of a disconnected recordset).
    >> Not sure why you'd want to do this.
    >>
    >>If you really need to insert a value here...you need to make the field

    something
    >>other than an identity...
    >>
    >>If you only want to do this locally for some purpose...knowing that the

    >field
    >>will be set to something else once you reconnect and 'update' the database..if
    >>that's what you'll eventually do w/ the recordset...you could just select
    >>and numeric field w/ an alias and not even select your identify field in
    >>your select statement...
    >>
    >>for example...
    >>
    >>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>
    >>Then you can update 'whatever_field' to whatever you want...and when you
    >>go back to update the database, just send the data back in as insert statements
    >>or via SP...or through direct recordset update if it will even work w/

    that
    >>extra field selected (not sure).
    >>
    >>Chris
    >>
    >>"hendry" <hendry@blg.co.id> wrote:
    >>>
    >>>Hi,
    >>>
    >>>I've got a problem on updating my Disconnected Recordset. If I update

    a
    >>field
    >>>which is specified as a type of identity column in the database, then

    it
    >>>always failed. I got an error: "Multiple-step operation generated code

    >errors.
    >>>check each status value"
    >>>
    >>>For example, I've got a table named MyTable with three columns (A,B,C).

    >>I
    >>>set Column A as identity column. Then I code my disconnected rs as follows:
    >>>
    >>>
    >>> Dim RS as ADODB.Recordset
    >>>
    >>> set RS = new Recordset
    >>> RS.cursorLocation = adUseClient
    >>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>> set RS.ActiveConnection = nothing
    >>>
    >>> '** try to Add new
    >>> RS.AddNew
    >>> RS.Fields("A") = 123
    >>> RS.Fields("B") = "bla"
    >>> RS.Fields("C") = "bla"
    >>> RS.Update
    >>>
    >>>Either add new or just update would always fail. If I remove identity

    from
    >>>column A then it runs smoothly, What's wrong here??
    >>>
    >>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>
    >>>Thanks for any help
    >>>
    >>>Hendry
    >>>
    >>>
    >>>

    >>

    >



  5. #5
    Chris Hylton Guest

    Re: How to Update Identity Col on Disconnected Recordset


    Hendry, just a note...once you create a recordset...i.e. open it either from
    a connection or from a file/stream...you can't append fields through the
    object model...which means if your recordset is a result of a sql statement,
    then you can't directly append a field...

    You'll have to go w/ the previous suggestion I made of including a non-table
    based field in your select statement...i.e. 'select field1, field1, 0 as
    field3 from tbl_whatever'...that should do it...

    Chris

    "Hendry" <hendry@blg.co.id> wrote:
    >
    >Thanks very much for your helpful feedback guys.
    >
    >I do an update on clientside recordset just for UI purposes (displaying

    data).
    > In the middle tier, the actual insert/update was done thru Stored Proc.
    >I do this in order to avoid passing the new data (recordset) back to the
    >UI (client) so that it'd always be one trip to the server.
    >
    >Based on all your suggestions, I've got an idea to append virtual field

    just
    >right after the recordset created and then any insert/update on the clientside
    >which related to auto-incrementing field will be done thru this field. My
    >Application will check whether the virtual field is empty or not, if so

    then
    >this is a new record made on the clientside therefore any update on the

    clientside
    >will refer to this field.
    >
    >This is still only in my mind. I'll try to figure it out.
    >
    >Thanks
    >
    >Hendry
    >
    >"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >>
    >>If you are obtaining the disconnected recordset from the database, e.g.

    >'Select
    >>A, B, C from MyTable where 0=1' then the following is probably occuring.
    >>If your Identity column, columns A, is auto-incrementing, I believe you

    >will
    >>never be able to set a value to that column. You would simply add values
    >>to columns B & C then give the recordset an open datasource, Filter the

    >pending
    >>records and UpdateBatch.
    >>
    >>If you are creating a recordset on the client side without the database,
    >>e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps

    >will
    >>have to be taken in the middle tier of the application. This recordset

    will
    >>not be able to be updated by adding a datasource and executing UpdateBatch.
    >>In the middle tier you will have to get an empty recordset from the database
    >>then set clientside recordset's column values B & C to the new empty recordset
    >>obtained from the database then updatebatch. After Updating you would simply
    >>set the clientside created recordset to nothing and return database recordset
    >>the was just updated back to the client.
    >>
    >>Hope this helps.
    >>
    >>Michael
    >>
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>Why do you want to update the identity columns...this really doesn't make
    >>>sense...the point is that it is populated automatically by the database

    >>when
    >>>you issue the update (or updatebatch in the case of a disconnected recordset).
    >>> Not sure why you'd want to do this.
    >>>
    >>>If you really need to insert a value here...you need to make the field

    >something
    >>>other than an identity...
    >>>
    >>>If you only want to do this locally for some purpose...knowing that the

    >>field
    >>>will be set to something else once you reconnect and 'update' the database..if
    >>>that's what you'll eventually do w/ the recordset...you could just select
    >>>and numeric field w/ an alias and not even select your identify field

    in
    >>>your select statement...
    >>>
    >>>for example...
    >>>
    >>>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>>
    >>>Then you can update 'whatever_field' to whatever you want...and when you
    >>>go back to update the database, just send the data back in as insert statements
    >>>or via SP...or through direct recordset update if it will even work w/

    >that
    >>>extra field selected (not sure).
    >>>
    >>>Chris
    >>>
    >>>"hendry" <hendry@blg.co.id> wrote:
    >>>>
    >>>>Hi,
    >>>>
    >>>>I've got a problem on updating my Disconnected Recordset. If I update

    >a
    >>>field
    >>>>which is specified as a type of identity column in the database, then

    >it
    >>>>always failed. I got an error: "Multiple-step operation generated code

    >>errors.
    >>>>check each status value"
    >>>>
    >>>>For example, I've got a table named MyTable with three columns (A,B,C).
    >>>I
    >>>>set Column A as identity column. Then I code my disconnected rs as follows:
    >>>>
    >>>>
    >>>> Dim RS as ADODB.Recordset
    >>>>
    >>>> set RS = new Recordset
    >>>> RS.cursorLocation = adUseClient
    >>>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>>> set RS.ActiveConnection = nothing
    >>>>
    >>>> '** try to Add new
    >>>> RS.AddNew
    >>>> RS.Fields("A") = 123
    >>>> RS.Fields("B") = "bla"
    >>>> RS.Fields("C") = "bla"
    >>>> RS.Update
    >>>>
    >>>>Either add new or just update would always fail. If I remove identity

    >from
    >>>>column A then it runs smoothly, What's wrong here??
    >>>>
    >>>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>>
    >>>>Thanks for any help
    >>>>
    >>>>Hendry
    >>>>
    >>>>
    >>>>
    >>>

    >>

    >



  6. #6
    hendry Guest

    Re: How to Update Identity Col on Disconnected Recordset


    Chris, Thanks for your info.

    Yup, it didn't work as I expected. I have also tried your suggestion, it
    worked but unfortunately the recordset can't be updated on the clientside.
    It gave me an error message when I try to update a non-table based field.
    So I did another workaround. First, I made a dummy recordset which appended
    with my virtual field. Next I copied all the structure of 'real' recordset
    into my dummy recordset. Then, finally I transfered all the values of the
    recordset into my dummy recordset. Yes, It works! But I'm afraid that this
    way would cause more overhead.

    I'm now just thinking whether I should make my recordset disconnected or
    not. If the recordset has many rows to be fetched, I'm afraid that the disconnected
    recorset would hurt performance since I made a duplication in the clientside.
    Would it be much better if I just use a Server-Side Recordset with a FireHorse
    cursor (OpenForwardOnly & LockReadOnly) i.e. the Connection is still exists
    until I made the duplication recordset in the clientside.

    Any ideas? Thanks

    Hendry



    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Hendry, just a note...once you create a recordset...i.e. open it either

    from
    >a connection or from a file/stream...you can't append fields through the
    >object model...which means if your recordset is a result of a sql statement,
    >then you can't directly append a field...
    >
    >You'll have to go w/ the previous suggestion I made of including a non-table
    >based field in your select statement...i.e. 'select field1, field1, 0 as
    >field3 from tbl_whatever'...that should do it...
    >
    >Chris
    >
    >"Hendry" <hendry@blg.co.id> wrote:
    >>
    >>Thanks very much for your helpful feedback guys.
    >>
    >>I do an update on clientside recordset just for UI purposes (displaying

    >data).
    >> In the middle tier, the actual insert/update was done thru Stored Proc.
    >>I do this in order to avoid passing the new data (recordset) back to the
    >>UI (client) so that it'd always be one trip to the server.
    >>
    >>Based on all your suggestions, I've got an idea to append virtual field

    >just
    >>right after the recordset created and then any insert/update on the clientside
    >>which related to auto-incrementing field will be done thru this field.

    My
    >>Application will check whether the virtual field is empty or not, if so

    >then
    >>this is a new record made on the clientside therefore any update on the

    >clientside
    >>will refer to this field.
    >>
    >>This is still only in my mind. I'll try to figure it out.
    >>
    >>Thanks
    >>
    >>Hendry
    >>
    >>"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >>>
    >>>If you are obtaining the disconnected recordset from the database, e.g.

    >>'Select
    >>>A, B, C from MyTable where 0=1' then the following is probably occuring.
    >>>If your Identity column, columns A, is auto-incrementing, I believe you

    >>will
    >>>never be able to set a value to that column. You would simply add values
    >>>to columns B & C then give the recordset an open datasource, Filter the

    >>pending
    >>>records and UpdateBatch.
    >>>
    >>>If you are creating a recordset on the client side without the database,
    >>>e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps

    >>will
    >>>have to be taken in the middle tier of the application. This recordset

    >will
    >>>not be able to be updated by adding a datasource and executing UpdateBatch.
    >>>In the middle tier you will have to get an empty recordset from the database
    >>>then set clientside recordset's column values B & C to the new empty recordset
    >>>obtained from the database then updatebatch. After Updating you would

    simply
    >>>set the clientside created recordset to nothing and return database recordset
    >>>the was just updated back to the client.
    >>>
    >>>Hope this helps.
    >>>
    >>>Michael
    >>>
    >>>
    >>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>
    >>>>Why do you want to update the identity columns...this really doesn't

    make
    >>>>sense...the point is that it is populated automatically by the database
    >>>when
    >>>>you issue the update (or updatebatch in the case of a disconnected recordset).
    >>>> Not sure why you'd want to do this.
    >>>>
    >>>>If you really need to insert a value here...you need to make the field

    >>something
    >>>>other than an identity...
    >>>>
    >>>>If you only want to do this locally for some purpose...knowing that the
    >>>field
    >>>>will be set to something else once you reconnect and 'update' the database..if
    >>>>that's what you'll eventually do w/ the recordset...you could just select
    >>>>and numeric field w/ an alias and not even select your identify field

    >in
    >>>>your select statement...
    >>>>
    >>>>for example...
    >>>>
    >>>>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>>>
    >>>>Then you can update 'whatever_field' to whatever you want...and when

    you
    >>>>go back to update the database, just send the data back in as insert

    statements
    >>>>or via SP...or through direct recordset update if it will even work w/

    >>that
    >>>>extra field selected (not sure).
    >>>>
    >>>>Chris
    >>>>
    >>>>"hendry" <hendry@blg.co.id> wrote:
    >>>>>
    >>>>>Hi,
    >>>>>
    >>>>>I've got a problem on updating my Disconnected Recordset. If I update

    >>a
    >>>>field
    >>>>>which is specified as a type of identity column in the database, then

    >>it
    >>>>>always failed. I got an error: "Multiple-step operation generated code
    >>>errors.
    >>>>>check each status value"
    >>>>>
    >>>>>For example, I've got a table named MyTable with three columns (A,B,C).
    >>>>I
    >>>>>set Column A as identity column. Then I code my disconnected rs as follows:
    >>>>>
    >>>>>
    >>>>> Dim RS as ADODB.Recordset
    >>>>>
    >>>>> set RS = new Recordset
    >>>>> RS.cursorLocation = adUseClient
    >>>>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>>>> set RS.ActiveConnection = nothing
    >>>>>
    >>>>> '** try to Add new
    >>>>> RS.AddNew
    >>>>> RS.Fields("A") = 123
    >>>>> RS.Fields("B") = "bla"
    >>>>> RS.Fields("C") = "bla"
    >>>>> RS.Update
    >>>>>
    >>>>>Either add new or just update would always fail. If I remove identity

    >>from
    >>>>>column A then it runs smoothly, What's wrong here??
    >>>>>
    >>>>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>>>
    >>>>>Thanks for any help
    >>>>>
    >>>>>Hendry
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  7. #7
    michael Guest

    Re: How to Update Identity Col on Disconnected Recordset


    This is what I do. I used to create client-side recordset and upon adding
    a new record I would have the middle tier fetch an empty on and put all the
    columns to from client-side created to newly server-side one.
    No I have gotten away from it, whether bad or good, it not that much of a
    performace hit. e.g.

    Public Function GetCustomer(Optional CustomerID as Long = 0)
    Const SQL as String = "SELECT * from TBL_CUSTOMER WHERE Customer_ID = "
    Dim cn as New ADODB.Connection
    Dim rs as ADODB.Recordset

    On Error Goto LocalError

    cn.Open CONNECT_STRING




    "hendry" <hendry@blg.co.id> wrote:
    >
    >Chris, Thanks for your info.
    >
    >Yup, it didn't work as I expected. I have also tried your suggestion, it
    >worked but unfortunately the recordset can't be updated on the clientside.
    >It gave me an error message when I try to update a non-table based field.
    >So I did another workaround. First, I made a dummy recordset which appended
    >with my virtual field. Next I copied all the structure of 'real' recordset
    >into my dummy recordset. Then, finally I transfered all the values of the
    >recordset into my dummy recordset. Yes, It works! But I'm afraid that this
    >way would cause more overhead.
    >
    >I'm now just thinking whether I should make my recordset disconnected or
    >not. If the recordset has many rows to be fetched, I'm afraid that the disconnected
    >recorset would hurt performance since I made a duplication in the clientside.
    >Would it be much better if I just use a Server-Side Recordset with a FireHorse
    >cursor (OpenForwardOnly & LockReadOnly) i.e. the Connection is still exists
    >until I made the duplication recordset in the clientside.
    >
    >Any ideas? Thanks
    >
    >Hendry
    >
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Hendry, just a note...once you create a recordset...i.e. open it either

    >from
    >>a connection or from a file/stream...you can't append fields through the
    >>object model...which means if your recordset is a result of a sql statement,
    >>then you can't directly append a field...
    >>
    >>You'll have to go w/ the previous suggestion I made of including a non-table
    >>based field in your select statement...i.e. 'select field1, field1, 0 as
    >>field3 from tbl_whatever'...that should do it...
    >>
    >>Chris
    >>
    >>"Hendry" <hendry@blg.co.id> wrote:
    >>>
    >>>Thanks very much for your helpful feedback guys.
    >>>
    >>>I do an update on clientside recordset just for UI purposes (displaying

    >>data).
    >>> In the middle tier, the actual insert/update was done thru Stored Proc.
    >>>I do this in order to avoid passing the new data (recordset) back to the
    >>>UI (client) so that it'd always be one trip to the server.
    >>>
    >>>Based on all your suggestions, I've got an idea to append virtual field

    >>just
    >>>right after the recordset created and then any insert/update on the clientside
    >>>which related to auto-incrementing field will be done thru this field.

    >My
    >>>Application will check whether the virtual field is empty or not, if so

    >>then
    >>>this is a new record made on the clientside therefore any update on the

    >>clientside
    >>>will refer to this field.
    >>>
    >>>This is still only in my mind. I'll try to figure it out.
    >>>
    >>>Thanks
    >>>
    >>>Hendry
    >>>
    >>>"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >>>>
    >>>>If you are obtaining the disconnected recordset from the database, e.g.
    >>>'Select
    >>>>A, B, C from MyTable where 0=1' then the following is probably occuring.
    >>>>If your Identity column, columns A, is auto-incrementing, I believe you
    >>>will
    >>>>never be able to set a value to that column. You would simply add values
    >>>>to columns B & C then give the recordset an open datasource, Filter the
    >>>pending
    >>>>records and UpdateBatch.
    >>>>
    >>>>If you are creating a recordset on the client side without the database,
    >>>>e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps
    >>>will
    >>>>have to be taken in the middle tier of the application. This recordset

    >>will
    >>>>not be able to be updated by adding a datasource and executing UpdateBatch.
    >>>>In the middle tier you will have to get an empty recordset from the database
    >>>>then set clientside recordset's column values B & C to the new empty

    recordset
    >>>>obtained from the database then updatebatch. After Updating you would

    >simply
    >>>>set the clientside created recordset to nothing and return database recordset
    >>>>the was just updated back to the client.
    >>>>
    >>>>Hope this helps.
    >>>>
    >>>>Michael
    >>>>
    >>>>
    >>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>>
    >>>>>Why do you want to update the identity columns...this really doesn't

    >make
    >>>>>sense...the point is that it is populated automatically by the database
    >>>>when
    >>>>>you issue the update (or updatebatch in the case of a disconnected recordset).
    >>>>> Not sure why you'd want to do this.
    >>>>>
    >>>>>If you really need to insert a value here...you need to make the field
    >>>something
    >>>>>other than an identity...
    >>>>>
    >>>>>If you only want to do this locally for some purpose...knowing that

    the
    >>>>field
    >>>>>will be set to something else once you reconnect and 'update' the database..if
    >>>>>that's what you'll eventually do w/ the recordset...you could just select
    >>>>>and numeric field w/ an alias and not even select your identify field

    >>in
    >>>>>your select statement...
    >>>>>
    >>>>>for example...
    >>>>>
    >>>>>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>>>>
    >>>>>Then you can update 'whatever_field' to whatever you want...and when

    >you
    >>>>>go back to update the database, just send the data back in as insert

    >statements
    >>>>>or via SP...or through direct recordset update if it will even work

    w/
    >>>that
    >>>>>extra field selected (not sure).
    >>>>>
    >>>>>Chris
    >>>>>
    >>>>>"hendry" <hendry@blg.co.id> wrote:
    >>>>>>
    >>>>>>Hi,
    >>>>>>
    >>>>>>I've got a problem on updating my Disconnected Recordset. If I update
    >>>a
    >>>>>field
    >>>>>>which is specified as a type of identity column in the database, then
    >>>it
    >>>>>>always failed. I got an error: "Multiple-step operation generated code
    >>>>errors.
    >>>>>>check each status value"
    >>>>>>
    >>>>>>For example, I've got a table named MyTable with three columns (A,B,C).
    >>>>>I
    >>>>>>set Column A as identity column. Then I code my disconnected rs as

    follows:
    >>>>>>
    >>>>>>
    >>>>>> Dim RS as ADODB.Recordset
    >>>>>>
    >>>>>> set RS = new Recordset
    >>>>>> RS.cursorLocation = adUseClient
    >>>>>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>>>>> set RS.ActiveConnection = nothing
    >>>>>>
    >>>>>> '** try to Add new
    >>>>>> RS.AddNew
    >>>>>> RS.Fields("A") = 123
    >>>>>> RS.Fields("B") = "bla"
    >>>>>> RS.Fields("C") = "bla"
    >>>>>> RS.Update
    >>>>>>
    >>>>>>Either add new or just update would always fail. If I remove identity
    >>>from
    >>>>>>column A then it runs smoothly, What's wrong here??
    >>>>>>
    >>>>>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>>>>
    >>>>>>Thanks for any help
    >>>>>>
    >>>>>>Hendry
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    michael Guest

    Re: How to Update Identity Col on Disconnected Recordset


    This is what I do. I used to create client-side recordset and upon adding
    a new record I would have the middle tier fetch an empty on and put all the
    columns from client-side created to newly server-side one.
    Now I have gotten away from it, whether bad or good, its not that much of
    a performace hit. e.g.

    Public Function GetCustomer(Optional CustomerID as Long = 0) as Object

    Now when adding new record I go ahead obtain an empty recordset and bring
    it back to the client for updating. When the client Saves the new record
    or Saves an edited record I simply call the same procedure and update that
    recordset by passing it back to the middle tier, set the datasource & updatebatch.
    I usually do this ByRef as well. I have not seen a big performance hit by
    doing this. If I do have a performance hit by updating disconnected recordset
    on the server I would take a harder look at my indexes and data model.

    Michael


    "hendry" <hendry@blg.co.id> wrote:
    >
    >Chris, Thanks for your info.
    >
    >Yup, it didn't work as I expected. I have also tried your suggestion, it
    >worked but unfortunately the recordset can't be updated on the clientside.
    >It gave me an error message when I try to update a non-table based field.
    >So I did another workaround. First, I made a dummy recordset which appended
    >with my virtual field. Next I copied all the structure of 'real' recordset
    >into my dummy recordset. Then, finally I transfered all the values of the
    >recordset into my dummy recordset. Yes, It works! But I'm afraid that this
    >way would cause more overhead.
    >
    >I'm now just thinking whether I should make my recordset disconnected or
    >not. If the recordset has many rows to be fetched, I'm afraid that the disconnected
    >recorset would hurt performance since I made a duplication in the clientside.
    >Would it be much better if I just use a Server-Side Recordset with a FireHorse
    >cursor (OpenForwardOnly & LockReadOnly) i.e. the Connection is still exists
    >until I made the duplication recordset in the clientside.
    >
    >Any ideas? Thanks
    >
    >Hendry
    >
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Hendry, just a note...once you create a recordset...i.e. open it either

    >from
    >>a connection or from a file/stream...you can't append fields through the
    >>object model...which means if your recordset is a result of a sql statement,
    >>then you can't directly append a field...
    >>
    >>You'll have to go w/ the previous suggestion I made of including a non-table
    >>based field in your select statement...i.e. 'select field1, field1, 0 as
    >>field3 from tbl_whatever'...that should do it...
    >>
    >>Chris
    >>
    >>"Hendry" <hendry@blg.co.id> wrote:
    >>>
    >>>Thanks very much for your helpful feedback guys.
    >>>
    >>>I do an update on clientside recordset just for UI purposes (displaying

    >>data).
    >>> In the middle tier, the actual insert/update was done thru Stored Proc.
    >>>I do this in order to avoid passing the new data (recordset) back to the
    >>>UI (client) so that it'd always be one trip to the server.
    >>>
    >>>Based on all your suggestions, I've got an idea to append virtual field

    >>just
    >>>right after the recordset created and then any insert/update on the clientside
    >>>which related to auto-incrementing field will be done thru this field.

    >My
    >>>Application will check whether the virtual field is empty or not, if so

    >>then
    >>>this is a new record made on the clientside therefore any update on the

    >>clientside
    >>>will refer to this field.
    >>>
    >>>This is still only in my mind. I'll try to figure it out.
    >>>
    >>>Thanks
    >>>
    >>>Hendry
    >>>
    >>>"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >>>>
    >>>>If you are obtaining the disconnected recordset from the database, e.g.
    >>>'Select
    >>>>A, B, C from MyTable where 0=1' then the following is probably occuring.
    >>>>If your Identity column, columns A, is auto-incrementing, I believe you
    >>>will
    >>>>never be able to set a value to that column. You would simply add values
    >>>>to columns B & C then give the recordset an open datasource, Filter the
    >>>pending
    >>>>records and UpdateBatch.
    >>>>
    >>>>If you are creating a recordset on the client side without the database,
    >>>>e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps
    >>>will
    >>>>have to be taken in the middle tier of the application. This recordset

    >>will
    >>>>not be able to be updated by adding a datasource and executing UpdateBatch.
    >>>>In the middle tier you will have to get an empty recordset from the database
    >>>>then set clientside recordset's column values B & C to the new empty

    recordset
    >>>>obtained from the database then updatebatch. After Updating you would

    >simply
    >>>>set the clientside created recordset to nothing and return database recordset
    >>>>the was just updated back to the client.
    >>>>
    >>>>Hope this helps.
    >>>>
    >>>>Michael
    >>>>
    >>>>
    >>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>>
    >>>>>Why do you want to update the identity columns...this really doesn't

    >make
    >>>>>sense...the point is that it is populated automatically by the database
    >>>>when
    >>>>>you issue the update (or updatebatch in the case of a disconnected recordset).
    >>>>> Not sure why you'd want to do this.
    >>>>>
    >>>>>If you really need to insert a value here...you need to make the field
    >>>something
    >>>>>other than an identity...
    >>>>>
    >>>>>If you only want to do this locally for some purpose...knowing that

    the
    >>>>field
    >>>>>will be set to something else once you reconnect and 'update' the database..if
    >>>>>that's what you'll eventually do w/ the recordset...you could just select
    >>>>>and numeric field w/ an alias and not even select your identify field

    >>in
    >>>>>your select statement...
    >>>>>
    >>>>>for example...
    >>>>>
    >>>>>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>>>>
    >>>>>Then you can update 'whatever_field' to whatever you want...and when

    >you
    >>>>>go back to update the database, just send the data back in as insert

    >statements
    >>>>>or via SP...or through direct recordset update if it will even work

    w/
    >>>that
    >>>>>extra field selected (not sure).
    >>>>>
    >>>>>Chris
    >>>>>
    >>>>>"hendry" <hendry@blg.co.id> wrote:
    >>>>>>
    >>>>>>Hi,
    >>>>>>
    >>>>>>I've got a problem on updating my Disconnected Recordset. If I update
    >>>a
    >>>>>field
    >>>>>>which is specified as a type of identity column in the database, then
    >>>it
    >>>>>>always failed. I got an error: "Multiple-step operation generated code
    >>>>errors.
    >>>>>>check each status value"
    >>>>>>
    >>>>>>For example, I've got a table named MyTable with three columns (A,B,C).
    >>>>>I
    >>>>>>set Column A as identity column. Then I code my disconnected rs as

    follows:
    >>>>>>
    >>>>>>
    >>>>>> Dim RS as ADODB.Recordset
    >>>>>>
    >>>>>> set RS = new Recordset
    >>>>>> RS.cursorLocation = adUseClient
    >>>>>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>>>>> set RS.ActiveConnection = nothing
    >>>>>>
    >>>>>> '** try to Add new
    >>>>>> RS.AddNew
    >>>>>> RS.Fields("A") = 123
    >>>>>> RS.Fields("B") = "bla"
    >>>>>> RS.Fields("C") = "bla"
    >>>>>> RS.Update
    >>>>>>
    >>>>>>Either add new or just update would always fail. If I remove identity
    >>>from
    >>>>>>column A then it runs smoothly, What's wrong here??
    >>>>>>
    >>>>>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>>>>
    >>>>>>Thanks for any help
    >>>>>>
    >>>>>>Hendry
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    hendry Guest

    Re: How to Update Identity Col on Disconnected Recordset


    michael, That's really a good idea. Thanks very much for your help.

    hendry

    "michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >
    >This is what I do. I used to create client-side recordset and upon adding
    >a new record I would have the middle tier fetch an empty on and put all

    the
    >columns from client-side created to newly server-side one.
    >Now I have gotten away from it, whether bad or good, its not that much of
    >a performace hit. e.g.
    >
    > Public Function GetCustomer(Optional CustomerID as Long = 0) as Object
    >
    >Now when adding new record I go ahead obtain an empty recordset and bring
    >it back to the client for updating. When the client Saves the new record
    >or Saves an edited record I simply call the same procedure and update that
    >recordset by passing it back to the middle tier, set the datasource & updatebatch.
    >I usually do this ByRef as well. I have not seen a big performance hit by
    >doing this. If I do have a performance hit by updating disconnected recordset
    >on the server I would take a harder look at my indexes and data model.
    >
    >Michael
    >
    >
    >"hendry" <hendry@blg.co.id> wrote:
    >>
    >>Chris, Thanks for your info.
    >>
    >>Yup, it didn't work as I expected. I have also tried your suggestion, it
    >>worked but unfortunately the recordset can't be updated on the clientside.
    >>It gave me an error message when I try to update a non-table based field.
    >>So I did another workaround. First, I made a dummy recordset which appended
    >>with my virtual field. Next I copied all the structure of 'real' recordset
    >>into my dummy recordset. Then, finally I transfered all the values of the
    >>recordset into my dummy recordset. Yes, It works! But I'm afraid that this
    >>way would cause more overhead.
    >>
    >>I'm now just thinking whether I should make my recordset disconnected or
    >>not. If the recordset has many rows to be fetched, I'm afraid that the

    disconnected
    >>recorset would hurt performance since I made a duplication in the clientside.
    >>Would it be much better if I just use a Server-Side Recordset with a FireHorse
    >>cursor (OpenForwardOnly & LockReadOnly) i.e. the Connection is still exists
    >>until I made the duplication recordset in the clientside.
    >>
    >>Any ideas? Thanks
    >>
    >>Hendry
    >>
    >>
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>Hendry, just a note...once you create a recordset...i.e. open it either

    >>from
    >>>a connection or from a file/stream...you can't append fields through the
    >>>object model...which means if your recordset is a result of a sql statement,
    >>>then you can't directly append a field...
    >>>
    >>>You'll have to go w/ the previous suggestion I made of including a non-table
    >>>based field in your select statement...i.e. 'select field1, field1, 0

    as
    >>>field3 from tbl_whatever'...that should do it...
    >>>
    >>>Chris
    >>>
    >>>"Hendry" <hendry@blg.co.id> wrote:
    >>>>
    >>>>Thanks very much for your helpful feedback guys.
    >>>>
    >>>>I do an update on clientside recordset just for UI purposes (displaying
    >>>data).
    >>>> In the middle tier, the actual insert/update was done thru Stored Proc.
    >>>>I do this in order to avoid passing the new data (recordset) back to

    the
    >>>>UI (client) so that it'd always be one trip to the server.
    >>>>
    >>>>Based on all your suggestions, I've got an idea to append virtual field
    >>>just
    >>>>right after the recordset created and then any insert/update on the clientside
    >>>>which related to auto-incrementing field will be done thru this field.

    >>My
    >>>>Application will check whether the virtual field is empty or not, if

    so
    >>>then
    >>>>this is a new record made on the clientside therefore any update on the
    >>>clientside
    >>>>will refer to this field.
    >>>>
    >>>>This is still only in my mind. I'll try to figure it out.
    >>>>
    >>>>Thanks
    >>>>
    >>>>Hendry
    >>>>
    >>>>"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >>>>>
    >>>>>If you are obtaining the disconnected recordset from the database, e.g.
    >>>>'Select
    >>>>>A, B, C from MyTable where 0=1' then the following is probably occuring.
    >>>>>If your Identity column, columns A, is auto-incrementing, I believe

    you
    >>>>will
    >>>>>never be able to set a value to that column. You would simply add values
    >>>>>to columns B & C then give the recordset an open datasource, Filter

    the
    >>>>pending
    >>>>>records and UpdateBatch.
    >>>>>
    >>>>>If you are creating a recordset on the client side without the database,
    >>>>>e.g. set rs = new ADODB.Recordset: rs.Fields.Append..., then more steps
    >>>>will
    >>>>>have to be taken in the middle tier of the application. This recordset
    >>>will
    >>>>>not be able to be updated by adding a datasource and executing UpdateBatch.
    >>>>>In the middle tier you will have to get an empty recordset from the

    database
    >>>>>then set clientside recordset's column values B & C to the new empty

    >recordset
    >>>>>obtained from the database then updatebatch. After Updating you would

    >>simply
    >>>>>set the clientside created recordset to nothing and return database

    recordset
    >>>>>the was just updated back to the client.
    >>>>>
    >>>>>Hope this helps.
    >>>>>
    >>>>>Michael
    >>>>>
    >>>>>
    >>>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>>>
    >>>>>>Why do you want to update the identity columns...this really doesn't

    >>make
    >>>>>>sense...the point is that it is populated automatically by the database
    >>>>>when
    >>>>>>you issue the update (or updatebatch in the case of a disconnected

    recordset).
    >>>>>> Not sure why you'd want to do this.
    >>>>>>
    >>>>>>If you really need to insert a value here...you need to make the field
    >>>>something
    >>>>>>other than an identity...
    >>>>>>
    >>>>>>If you only want to do this locally for some purpose...knowing that

    >the
    >>>>>field
    >>>>>>will be set to something else once you reconnect and 'update' the database..if
    >>>>>>that's what you'll eventually do w/ the recordset...you could just

    select
    >>>>>>and numeric field w/ an alias and not even select your identify field
    >>>in
    >>>>>>your select statement...
    >>>>>>
    >>>>>>for example...
    >>>>>>
    >>>>>>select 0 as whatever_field, customer_id, customer_name from tbl_whatever
    >>>>>>
    >>>>>>Then you can update 'whatever_field' to whatever you want...and when

    >>you
    >>>>>>go back to update the database, just send the data back in as insert

    >>statements
    >>>>>>or via SP...or through direct recordset update if it will even work

    >w/
    >>>>that
    >>>>>>extra field selected (not sure).
    >>>>>>
    >>>>>>Chris
    >>>>>>
    >>>>>>"hendry" <hendry@blg.co.id> wrote:
    >>>>>>>
    >>>>>>>Hi,
    >>>>>>>
    >>>>>>>I've got a problem on updating my Disconnected Recordset. If I update
    >>>>a
    >>>>>>field
    >>>>>>>which is specified as a type of identity column in the database, then
    >>>>it
    >>>>>>>always failed. I got an error: "Multiple-step operation generated

    code
    >>>>>errors.
    >>>>>>>check each status value"
    >>>>>>>
    >>>>>>>For example, I've got a table named MyTable with three columns (A,B,C).
    >>>>>>I
    >>>>>>>set Column A as identity column. Then I code my disconnected rs as

    >follows:
    >>>>>>>
    >>>>>>>
    >>>>>>> Dim RS as ADODB.Recordset
    >>>>>>>
    >>>>>>> set RS = new Recordset
    >>>>>>> RS.cursorLocation = adUseClient
    >>>>>>> RS.open "SELECT A,B,C FROM MyTable",MyDSN,adOpenStatic,adLockOptimistic
    >>>>>>> set RS.ActiveConnection = nothing
    >>>>>>>
    >>>>>>> '** try to Add new
    >>>>>>> RS.AddNew
    >>>>>>> RS.Fields("A") = 123
    >>>>>>> RS.Fields("B") = "bla"
    >>>>>>> RS.Fields("C") = "bla"
    >>>>>>> RS.Update
    >>>>>>>
    >>>>>>>Either add new or just update would always fail. If I remove identity
    >>>>from
    >>>>>>>column A then it runs smoothly, What's wrong here??
    >>>>>>>
    >>>>>>>Is there any workaround for this? BTW, I'm using MDAC 2.7
    >>>>>>>
    >>>>>>>Thanks for any help
    >>>>>>>
    >>>>>>>Hendry
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center