Connection is busy with results for another hstmt [ODBC Error]

[gamcall]

Hello, I get the above message as a pop-up when I try & do
the following in an update trigger. Basically, before
inserting a detail line record, I check for the existence
of it's 2 parent header records, & if they don't exist,
create them in the appropriate tables. I think I have
re-worked the code to do as few sql statements as possible.

select <needed values> from detail,inserted, parent1_equiv, parent2_equiv
-- NOTE these tables are NOT actually parent1 & parent2
if (<trigger condition>)
if not exists(select * from parent1 where ...)
insert into parent1 (<columns>) values(<values>)

if not exists(select * from parent2 where ...)
insert into parent2 (<columns>) values(<values>)

insert into detail(<columns>) (<values>)
...

I don't see why this should cause a problem to any self-
respecting database server (and/or ODBC implementation).Can
anyone suggest a workaround or another way of doing this?
Surely this would be a common thing to do in applications,
(ie ensure referential integrity)?

T.I.A,
GAM

 

[Guest_imported]

I had a problem with the same error message in another situation. Is it possible that the testing for existance of the parent record returns an error when none is found? And if so, could you set the err back to 0 or clear it somehow?
As far as I can tell, the connection is looking for some command to release it from the busy condition.

John [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[gamcall]

Well, I tried to test for the existence of the parent records another way by selecting count(*) into count variables & then having the if statement branches just use these in straight predicates. Still got the same error. I find the fact that I cant do this a _joke_. Can _someone_ pls prove my assessment wrong that this is just more evidence that MS has no business in the server market? [sig][/sig]

 

[gamcall]

Well, the problem has been solved by a helpful developer/support person for our Financials Package. He said those 3 little words; &quot;SET NOCOUNT ON&quot;. Context is:

CREATE TRIGGER
<trig name> ON <table>
FOR <UPDATE,INSERT,DELETE> AS
SET NOCOUNT ON
blah, blah, blah

Hope this helps some other poor soul who can't understand why something so logical & simple isn't _working_. It's just one of those things you gotta know... [sig][/sig]