How to Update Identity Col on Disconnected Recordset

[hendry]

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

[Chris Hylton]

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

[michael]

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

[Hendry]

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

[Chris Hylton]

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

[hendry]

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

[michael]

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

[michael]

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

[hendry]

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