A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access ADO Form Insert Bug



 
 
Thread Tools Display Modes
  #1  
Old October 14th, 2005, 02:20 AM
Pete Nurse
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

....If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?

  #2  
Old October 14th, 2005, 02:43 AM
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

i'll try to look into this later

i'd start with the basics:

a) do you have triggers
b) have you run SQL profiler or sp_who2 to see what else is hitting
this table

  #3  
Old October 14th, 2005, 03:38 AM
Pete Nurse
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

Thanks Aaron,

In answer:-

a) relevant triggers are disabled.
b) I'm the only user. There are no background routines running.

  #4  
Old October 14th, 2005, 05:25 AM
Sylvain Lafontaine
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

1- Make sure that you have a primary key. It's not a bad idea to have
primary keys constitued of single field as I had trouble in the past with
composite primary keys.

2- Create a Resync command. This is probably the best way for solving your
problem.

In some case, a dummy Resync command (which is simply the name of a SP
without the right number of parameters) might be a good choice; as this will
force Access to use the default behavior of ADO, which is simply to return
the same values without requiring SQL-Server.

3- As suggested in the other post, take a look at what Access is doing by
using the SQL-Server Profiler; as this is the only practical way of
determining what Access is trying to do and finding how to compensate for
its limitations.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Pete Nurse" wrote in message
oups.com...
I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

...If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?



  #5  
Old October 14th, 2005, 08:00 AM
Pete Nurse
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

Sylvain, thanks for the response.

1/ Yes, I use a single field primary key of type INT with identity set
(ie autonumber).

2/ As you suggest, I've tried entering a dummy Resynch command and it
seemed to overcome the reported problem, though I don't see how I can
enter "a SP
without the right number of parameters" without receiving an error. In
fact, it seems that entering a non-existent, nonsense SP name works
just as well (ie you get an error message). Could you please explain
how I can overcome that.

I tried entering the Resynch command as recommended at . . .

http://msdn.microsoft.com/library/de...ynccommand.asp

It seemed to work sometimes but not always. Certainly not as well
(apart from the error message) as the dummy SP idea.

3/ I had a look at Profiler, but it's pretty arcane - I have no idea
what I'm looking at.

Thanks for your response - maybe we're homing in on the solution.

  #6  
Old October 14th, 2005, 04:10 PM
Malcolm Cook
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

Peter,

Some questions that may generate more heat:

1 How are you passing the company id to the subform's sp?
2 What is the param list to the sp?
3 What is the complete text of the sp, both the one that gives errors and the one that does not?
4 Do you have 'Link Child Fields' and 'Link master Fields' set on the parent's subform object?
5 Do you have 'Input Parameters' set on the subform.Form object?
6 What were the exact values you tried as the 'Resync Command' and what was the exact behavior that resulted (per Syvain's line
of thought


--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


"Pete Nurse" wrote in message oups.com...
I have a bound subform (employee) which linked to the parent form
(company) by the argument to the subform's SQL Server stored procedure
(SP). The UniqueTable property is set to "tblEmployee".

Depending on the complexity of the SP (more of which later), after I
insert a record I get the following message...

"Key value for this row was changed or deleted at the data store. The
local row is now deleted" and then Still on the new record - all the
fields change to #Deleted. Then the message "Multiple-step operation
generated errors. Check each status value" appears.

I can only release myself from this situation by closing the form, the
"Multiple-step..." error appear many times followed by "Another user or
application has deleted this record or changed the value of its primary
key".

On return to the form the new employee record is there in all its
beauty. I therefore conclude that there was actually nothing wrong
with the new record.

As I hinted, the problem seems to be related to the complexity of the
SP eg "SELECT Coy.*, Emp.*..." causes the above errors but "SELECT *
FROM ..." doesn't(!). I don't want to chase that particular art down
the drainpipe, though, because I'm looking for a solution in which I
can any valid SP code.

That's not all though...

...If I dumb down the design of the SP enough to enable me to insert a
new employee record, an entirely unrelated employee record (ie an
employee from another company) appears in the subform (!!). After I
refresh the subform, the rogue record disappears and the newly added
record appears.

Does anyone know what's going on here?



  #7  
Old October 14th, 2005, 06:19 PM
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

you know that if your sproc is looking for a parameter @txtPLU if you
have a control named 'txtPLU' it will automatically fill it in?

i think that this is undocumented; but i love this feature enough that
I'm still madly in love with ADP 5 years after it came out.

  #8  
Old October 14th, 2005, 06:46 PM
Malcolm Cook
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

i do know having stumled upon it some time ago - thanks - yeah, it is a great feature
wrote in message oups.com...
you know that if your sproc is looking for a parameter @txtPLU if you
have a control named 'txtPLU' it will automatically fill it in?

i think that this is undocumented; but i love this feature enough that
I'm still madly in love with ADP 5 years after it came out.



  #9  
Old October 14th, 2005, 09:48 PM
Pete Nurse
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

Thanks Malcolm.

1 How are you passing the company id to the subform's sp?

Generally by setting the recordsource eg "EXEC spEmployee 23554" but I
have also tried setting the InputParameters property. This problem
seems to show up in both instances.

2 What is the param list to the sp?

I work in two different ways. Where I had an SP which I wanted to use
for several different situations, I pass a string like 'empID=2435' or
'coyID=436'. The SP then decodes the prefix and acts accordingly.
Otherwise, I just pass the ID as shown above.

3 What is the complete text of the sp, both the one that gives errors and the one that does not?

These two seem to work...
SELECT *
FROM dbo.tblBenefitType BT RIGHT OUTER JOIN
dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN
dbo.tblCompany E ON E.emp_coyID = C.coyID
WHERE (emp_coyID = @coyID)

SELECT E.*, BT.*
FROM dbo.tblBenefitType BT RIGHT OUTER JOIN
dbo.tblEmployee E ON BT.btID = E.emp_btID
WHERE (emp_coyID = @coyID)

However either of the following creates problems:-
SELECT C.*, BT.*, E.*
FROM dbo.tblBenefitType BT RIGHT OUTER JOIN
dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN
dbo.tblCompany E ON E.emp_coyID = C.coyID
WHERE (emp_coyID = @coyID)

SELECT *
FROM dbo.tblBenefitType BT RIGHT OUTER JOIN
dbo.tblEmployee E ON BT.btID = E.emp_btID LEFT OUTER JOIN
dbo.tblCompany E ON E.emp_coyID = C.coyID LEFT OUTER JOIN
dbo.tblCorporation Crp ON E.emp_coyID = Crp.crpID
WHERE (emp_coyID = @coyID)

4 Do you have 'Link Child Fields' and 'Link master Fields' set on the parent's subform object?

No. I've never found that that works with SPs.

5 Do you have 'Input Parameters' set on the subform.Form object?

I've tried it. See 1.

6 What were the exact values you tried as the 'Resync Command' and what was the exact behavior that resulted (per Syvain's line)

I tried "spDummy" with no parameter. Code below.

ALTER PROCEDURE spDummy @Prm1 VARCHAR(50)
As
PRINT @Prm1

Thanks again.

  #10  
Old October 14th, 2005, 09:55 PM
Pete Nurse
external usenet poster
 
Posts: n/a
Default Access ADO Form Insert Bug

You know that if your sproc is looking for a parameter @txtPLU if you
have a control named 'txtPLU' it will automatically fill it in?


No I didn't know that. I can see that would great for combo boxes that
need to change depending on the employee or company or whatever, but
what about subforms?

In my case I have a company form based on tblCompany (between us for
the moment, let's forget about the SP) with primary key coyID with an
employee subform based on tblEmployee with PK empID and foreign key
emp_coyID which links the tblCompany.

Can it do that?

Thanks for the info - I'm not sure it solves my problem but . . .

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
One user cannot access custom form????? Jenai Contacts 0 September 27th, 2005 09:35 PM
Why is a French Spellchecker a "required" update for English speak French Spellcheck Required? General Discussion 23 April 26th, 2005 01:17 AM
Adding staff photographs to my database KK New Users 2 September 3rd, 2004 07:41 AM
Automatically insert in an Access Form Peter L Using Forms 3 September 1st, 2004 12:36 PM
Upload Image Jason MacKenzie General Discussion 1 September 1st, 2004 04:38 AM


All times are GMT +1. The time now is 04:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.