AddNew works, but then it doesn't (ADO) 
Author Message
 AddNew works, but then it doesn't (ADO)

Jeremy,

I think I am having the same problem you are, let me try and explain...

You wrote -
I'm experiencing a weird problem with ADO.  I have a form
with an ADODC, and several DataCombo's and a Textbox bound to an Access97
database.  The problem occurs when the user (me, in this case) tries to add
a new record.  That part works.  You can enter data fine, and I've verified
that it is added correctly to the database.

I'm doing this exact same thing, I have also verified that the data is being
added to the database.  I am calling the UpdateBatch method (to get the data
into the database), the LockType is BatchOptimistic, CursorType is Keyset,
and I'm using server side cursors.

You wrote -
The weird part starts happening after the user has finished
adding the new record, and moves back to the previous record.  Okay,
that's not the weird part yet.  The weird part happens when they
then move -back- to the newly added record (which at this point still
looks fine).  Should they happen to try to move off of it again,
it pops up the error message "The specified row could not be located
for updating: Some values may have been changed since it was last
read."  This happens even when you don't make a single change.

I get this same error, but it does not occur when I move from an old record
back to a new one, I can do that just fine, it happens when I make changes
to the new record.  I've gotten around the AutoNumber issue where it equals
"" and 0.  This is the code I used for that.

Private Sub DataGridModelSimulations_Click()
Value =
DataGridModelSimulations.Columns("ModelID").CellValue(DataGridModelSimulatio
ns.Bookmark)
If Value = 0 Then
Set rsModelID = DataEnvironment1.rsMaxModelID
rsModelID.Open
Value = rsModelID!MaxModelID
rsModelID.Close
End If
MyModel.Model = Value
frmModel.Show
End Sub

I'm not sure if your using a datagrid, but if you are this is what I did.
You said you had a form you were inputing data into, which is what I have.
The recordset behind this form is the same recordset that is behind my grid,
so I add the new record and it appears in the Grid, when I click on the
grid, I'm grabbing the value(which for the new record is zero not "" because
I called the UpdateBatch, without calling the UpdateBatch it would be "")
and if the value is 0 I am opening a new recordset and then grabbing out the
new AutoNumber, in this case the ModelID.
Now my problem occers when I want to make changes to the recordset I just
added.  After I make the changes I call UpdatBatch again, but I get the
error you mentioned.  It's as if I can't call UpdateBatch twice.

You wrote -
I'm pretty sure the problem is an AutoNumber issue, which the
table being inserted into contains.  When you're in the process of
adding the new record, the autonumber value shows up as null.  When you
move off the new record, and then back to it, it then shows as 0
(which is, obviously, incorrect).  I know there's no good way to get
the value of an autonumber field with a client-side cursor, but this
behavior seems pretty odd all the same.  Seems like something that
people would want to do all the time.

I know the way I got the AutoNumber is probably not the greatest, but it
does seem to work.

I've been looking into the EditMode Property, because I thought my problem
had something to do with that.  I found that when I select the new record
that I just added from the DataGrid, my new form opens and I am in the
adEditInProcesss mode, which I would think would then allow me to call
UpdateBatch again, but to no avail.  I have also tried doing a Resync, but
that erases all changes the that are added when you do a BatchUpdate, I've
tried requery, and refreshing the DataGrid but nothing seems to work.  It's
as if there is no way to make changes to a record that has just been added,
which seems ridiculous.

Sorry I haven't been much help, but if I come across a solution to the error
you were getting "The specified row could not be located
for updating: Some values may have been changed since it was last
read."  I'll be sure to email you back.  If you find out anything please
email me.  It seems were running into the same problem.

Do I get some Peeps for trying?

Good Luck!
Rhonda



Sun, 02 Sep 2001 03:00:00 GMT  
 AddNew works, but then it doesn't (ADO)
I had this very same problem too. I replaced all the autonumber fields by
long integers (which is actually the same type). This may give some problems
in acces, which you can avoid by removing the relations to the autonumber
field first and then change them to longint. After that you can reestablish
the relation. Further i introduced a new table called autonum, which
contains a record for each autonumberfield in your original database.
I then wrote a function in VB called GetAutoNum.  Which reads the
appropriate record in the autonum table, increases it and updates it. Now
when another user tries to retrieve the same autonum at the same time he
will recieve an error on update. You have to catch these errors and loop
until no error occurs (which will be true after some time).
Now, when i add a record (by the .addnew method) i let the user fill all the
fields, except the autonumbers. Then the user has to click on the save
button to update the recordset, but before updating i call the GetAutoNum
function to retrieve the next free number. (of course you can also add the
call to the GetAutoNum to the willmove event)
Another advantage of this approach is that the numbers will be increasing
and (as long as you don't remove records) will also be closed (so there are
no holes in the numbering).

I hope this will help you.

--
B.J.M. van den Brand
QuadriX? System Design
Eindhoven
The Netherlands

jeremy h todd heeft geschreven in bericht

Quote:
> I'm experiencing a weird problem with ADO.  I have a form
>with an ADODC, and several DataCombo's and a Textbox bound to an
>Access97 database.  The problem occurs when the user (me, in this
>case) tries to add a new record.  That part works.  You can enter
>data fine, and I've verified that it is added correctly to the
>database.

> The weird part starts happening after the user has finished
>adding the new record, and moves back to the previous record.  Okay,
>that's not the weird part yet.  The weird part happens when they
>then move -back- to the newly added record (which at this point still
>looks fine).  Should they happen to try to move off of it again,
>it pops up the error message "The specified row could not be located
>for updating: Some values may have been changed since it was last
>read."  This happens even when you don't make a single change.

> I'm pretty sure the problem is an AutoNumber issue, which the
>table being inserted into contains.  When you're in the process of
>adding the new record, the autonumber value shows up as null.  When you
>move off the new record, and then back to it, it then shows as 0
>(which is, obviously, incorrect).  I know there's no good way to get
>the value of an autonumber field with a client-side cursor, but this
>behavior seems pretty odd all the same.  Seems like something that
>people would want to do all the time.

> I'm working around this right now by using a server-side
>cursor, but that has other difficulties I'd just as soon avoid.  Is
>there a way to get around this problem with a client-side cursor?
>Doing a Requery after adding a new record would be great, but how?
>It's not allowed in the WillMove event, and there seems to be a bug
>preventing you from running it in the MoveComplete event either, at
>least when moving backwards.  I can't do a Resync either, since the
>autonumber field is the primary key, and Resync needs to know its
>value :P  Seems like a pretty major design flaw in ADO...

> Can anybody help me?  I can give you Cadbury Cream Eggs or
>Peeps, your choice!
> -jht
>--
>Jeremy Todd                     Database Programmer        _,/

>http://www.ag.uiuc.edu/~toddjh/ College of ACES, UIUC       \_  /   \
>Zupfe Boy and Night Owl         (And Kangaroo Aficianado)     \)\ /\.\
>=========================================================       //   \\
>"M-O-O-N, that spells moon" - Tom Cullen                      ,/'     `\_,



Sun, 02 Sep 2001 03:00:00 GMT  
 AddNew works, but then it doesn't (ADO)
        I'm experiencing a weird problem with ADO.  I have a form
with an ADODC, and several DataCombo's and a Textbox bound to an
Access97 database.  The problem occurs when the user (me, in this
case) tries to add a new record.  That part works.  You can enter
data fine, and I've verified that it is added correctly to the
database.

        The weird part starts happening after the user has finished
adding the new record, and moves back to the previous record.  Okay,
that's not the weird part yet.  The weird part happens when they
then move -back- to the newly added record (which at this point still
looks fine).  Should they happen to try to move off of it again,
it pops up the error message "The specified row could not be located
for updating: Some values may have been changed since it was last
read."  This happens even when you don't make a single change.

        I'm pretty sure the problem is an AutoNumber issue, which the
table being inserted into contains.  When you're in the process of
adding the new record, the autonumber value shows up as null.  When you
move off the new record, and then back to it, it then shows as 0
(which is, obviously, incorrect).  I know there's no good way to get
the value of an autonumber field with a client-side cursor, but this
behavior seems pretty odd all the same.  Seems like something that
people would want to do all the time.

        I'm working around this right now by using a server-side
cursor, but that has other difficulties I'd just as soon avoid.  Is
there a way to get around this problem with a client-side cursor?
Doing a Requery after adding a new record would be great, but how?
It's not allowed in the WillMove event, and there seems to be a bug
preventing you from running it in the MoveComplete event either, at
least when moving backwards.  I can't do a Resync either, since the
autonumber field is the primary key, and Resync needs to know its
value :P  Seems like a pretty major design flaw in ADO...

        Can anybody help me?  I can give you Cadbury Cream Eggs or
Peeps, your choice!
        -jht
--
Jeremy Todd                     Database Programmer        _,/

http://www.ag.uiuc.edu/~toddjh/ College of ACES, UIUC       \_  /   \
Zupfe Boy and Night Owl         (And Kangaroo Aficianado)     \)\ /\.\
=========================================================       //   \\
"M-O-O-N, that spells moon" - Tom Cullen                      ,/'     `\_,



Sun, 02 Sep 2001 03:00:00 GMT  
 AddNew works, but then it doesn't (ADO)
Hey, Jeremy, I'm learning here -- what are the diffulties you are referring
to with regard to server-side cursors?  What are the pitfalls?

Thanks.

Quote:
>jeremy h todd heeft geschreven in bericht

>> I'm working around this right now by using a server-side
>>cursor, but that has other difficulties I'd just as soon avoid.  Is



Sun, 02 Sep 2001 03:00:00 GMT  
 AddNew works, but then it doesn't (ADO)

Quote:

>Hey, Jeremy, I'm learning here -- what are the diffulties you are referring
>to with regard to server-side cursors?  What are the pitfalls?

        The biggest (i.e. most important) one for me is performance;
the server-side cursor appears to be a bit slower than client-side.
Only a little bit, but that's with only one user.  I imagine the
overhead would go up dramatically when I'm done with the thing and
lots of people are using it.  It'd be nicer if the client machines
could pick up a little of the slack.

        The most annoying from a programming point of view is that
a server-side Recordset doesn't support the AbsolutePosition
property that tells you which record you're in.  It probably isn't
a big deal for a lot of apps, but for mine it is, and I'm having to
jump through a lot of hoops to keep track of it (i.e. assuming it
starts at 1 when they open the form, increment or decrement as they
move/add/delete, etc.)  Also, when you add or delete a record, the
RecordCount property doesn't seem to be updated (not always, anyway),
so you have to make sure that's taken care of too.  It's not a
fatal flaw, but it's a pain.

        Good luck! (:
        -jht
--
Jeremy Todd                     Database Programmer        _,/

http://www.ag.uiuc.edu/~toddjh/ College of ACES, UIUC       \_  /   \
Zupfe Boy and Night Owl         (And Kangaroo Aficianado)     \)\ /\.\
=========================================================       //   \\
"M-O-O-N, that spells moon" - Tom Cullen                      ,/'     `\_,



Mon, 03 Sep 2001 03:00:00 GMT  
 AddNew works, but then it doesn't (ADO)

Quote:
> I had this very same problem too. I replaced all the autonumber fields by
> long integers (which is actually the same type). This may give some problems
> in acces, which you can avoid by removing the relations to the autonumber
> field first and then change them to longint. After that you can reestablish
> the relation. Further i introduced a new table called autonum, which
> contains a record for each autonumberfield in your original database.
> I then wrote a function in VB called GetAutoNum.  Which reads the
> appropriate record in the autonum table, increases it and updates it. Now
> when another user tries to retrieve the same autonum at the same time he
> will recieve an error on update. You have to catch these errors and loop
> until no error occurs (which will be true after some time).
> Now, when i add a record (by the .addnew method) i let the user fill all the
> fields, except the autonumbers. Then the user has to click on the save
> button to update the recordset, but before updating i call the GetAutoNum
> function to retrieve the next free number. (of course you can also add the
> call to the GetAutoNum to the willmove event)
> Another advantage of this approach is that the numbers will be increasing
> and (as long as you don't remove records) will also be closed (so there are
> no holes in the numbering).

> I hope this will help you.

> --
> B.J.M. van den Brand
> QuadriX? System Design
> Eindhoven
> The Netherlands

> jeremy h todd heeft geschreven in bericht

> > I'm experiencing a weird problem with ADO.  I have a form
> >with an ADODC, and several DataCombo's and a Textbox bound to an
> >Access97 database.  The problem occurs when the user (me, in this
> >case) tries to add a new record.  That part works.  You can enter
> >data fine, and I've verified that it is added correctly to the
> >database.

> > The weird part starts happening after the user has finished
> >adding the new record, and moves back to the previous record.  Okay,
> >that's not the weird part yet.  The weird part happens when they
> >then move -back- to the newly added record (which at this point still
> >looks fine).  Should they happen to try to move off of it again,
> >it pops up the error message "The specified row could not be located
> >for updating: Some values may have been changed since it was last
> >read."  This happens even when you don't make a single change.

> > I'm pretty sure the problem is an AutoNumber issue, which the
> >table being inserted into contains.  When you're in the process of
> >adding the new record, the autonumber value shows up as null.  When you
> >move off the new record, and then back to it, it then shows as 0

Isn't VB supposed to make database programming easier?  Seems like a LOT
of work for some fundamental functionality....

Jay



Mon, 03 Sep 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. AddNew works, but then it doesn't (ADO)

2. Databound Checkbox doesn't work BindingContext(...).AddNew

3. Addnew works but doesn't...

4. AddNew method doesn't work right!!

5. Addnew Doesn't Addnew!

6. fRefreshLinks Doesn't work if path doesn't exist

7. ADO Help doesn't work from within Access 2000

8. ADO Doesn't work

9. Why doesn't Recordcount work with ADO ?

10. Compacting with ADO and DAO doesn't work

11. ADO and COUNT(*) doesn't work

12. ADO RecordCount Doesn't work???

 

 
Powered by phpBB® Forum Software