SQL Delete Timeout

[Brian J. Ackermann]

Hi all,

I'm getting an error from something that seems too easy to cause
trouble. Here's the scenario. User fills out a form. User saves. On
submit, an asp script attempts to save the data. it deletes all of
existing child records, and then re-inserts new child records.

Heres the chunk of code thats bombing out with a 'Microsoft OLE DB
Provider for SQL Server error '80040e31' ':

'delete all children for this Parent record
strSQL = "DELETE FROM qcspec_is_tasks WHERE parentID = " & nRecID
debugPrint strSQL
cn.Execute strSQL

The print statement yeilds the following, before the Execute statement
dies:

DELETE FROM qcspec_is_tasks WHERE parentID = 20

If I run the above in query analyzer, it executes instantly.

Incidentally, at this point in time, the table is tiny. Its only 50
rows or so, at the moment.

I've looked into google, and the group archives, but I never saw any
solution to the problem.

[Brian J. Ackermann]

Here's an added confusion:

If I change

'delete all children for this Parent record
strSQL = "DELETE FROM qcspec_is_tasks WHERE parentID = " & nRecID
debugPrint strSQL
cn.Execute strSQL

to this:
'delete all children for this Parent record
strSQL = "SELECT * FROM qcspec_is_tasks WHERE parentID = " & nRecID
debugPrint strSQL
cn.Execute strSQL

Then my program continues to execute. It adds duplicate items into the
table every time the item saves, but it shows that the connection is
working.

Any ideas?

Thanks,

Brian Ackermann

[Brian J. Ackermann]

Brian J. Ackermann wrote:

Any ideas?

Looks like I solved my own question.

I added an index to the 'parentID' field, and the Delete statement works
instantly.

Thanks for reading!

Brian Ackermann

[Bob Barrows [MVP]]

Brian J. Ackermann wrote:

Here's an added confusion:

If I change

'delete all children for this Parent record
strSQL = "DELETE FROM qcspec_is_tasks WHERE parentID = " & nRecID
debugPrint strSQL
cn.Execute strSQL

to this:
'delete all children for this Parent record
strSQL = "SELECT * FROM qcspec_is_tasks WHERE parentID = " & nRecID
debugPrint strSQL
cn.Execute strSQL

Then my program continues to execute. It adds duplicate items into
the table every time the item saves,

Really? No primary key on the table? No unique index or constraint?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Brian J. Ackermann wrote:

Hi all,

I'm getting an error from something that seems too easy to cause
trouble. Here's the scenario. User fills out a form. User saves.
On submit, an asp script attempts to save the data. it deletes all of
existing child records, and then re-inserts new child records.
Well, I see signs of trouble already...why not just update in place?

Heres the chunk of code thats bombing out with a 'Microsoft OLE DB
Provider for SQL Server error '80040e31' ':

Jeez, I don't have all these error codes memorized - please provide the text
of the message.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Brian J. Ackermann wrote:

Brian J. Ackermann wrote:

> Any ideas?

Looks like I solved my own question.

I added an index to the 'parentID' field, and the Delete statement
works instantly.

Still ... why delete and insert? Why not update in place?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

Bob Barrows [MVP] wrote:

Still ... why delete and insert? Why not update in place?

I'll give you an example. We have an application that asks users to
inventory certain types of resources they use within our company. The lists
of available resources are quite long (numbering in the hundreds), but few
individuals use many items, so it makes sense to only store their
selections, and not items that they do not select.

Like the OP, we have a parent-child relationship in which we need to keep a
list of children.

Suppose a user makes his selections, then comes back to make changes. At
form submission, we have several choices for dealing with potential
deselections, namely (1) delete them all and store only what is submitted,
(2) report to the DB the selection status for every single item, (3) retain
the PREVIOUSLY SELECTED values in hidden form fields and use the form
submission to determine which selections to delete, or (4) compare every
single PREVIOUSLY SELECTED item to the current selection list.

It seems to me that (1) and (3) are the only reasonable choices. We made the
same choice as the OP, and probably for the same reason -- it is
considerably easier to implement and maintain and there is a reasonable
expectation of performance equivalence.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Dave Anderson wrote:

Bob Barrows [MVP] wrote:

Still ... why delete and insert? Why not update in place?

I'll give you an example.

<snip>
That's a certainly valid design choice. And it does seem to fit the OP's
situation, given the "WHERE ParentID = ..." (whose significance I initially
missed), so I will consider myself corrected. Thanks
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"