Posted by Erland Sommarskog on 06/01/07 21:20

Tonio Tanzi (t.tanzi@alice.it) writes:
> In certain situations, in my asp site, I have to make a copy of a record
> of "master" with all the linked record of the "detail" table.
> The code I've written to realize this task has been tested in many
> similar situations and has always worked fine (it is reported on the end
> of mail).
> With the two table above I have this strange behavior: when I attempt to
> do the copy of linked records in the "detail" table (using an "Insert"
> query), some records are correctly inserted, whereas for few other
> records the Conn.Execute of the "Insert" query don't go and I receive
> the message:
>
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
>...
>
> *** Code of the copy procedure ***
> ...
> old_id_master= 'the id of the master record to copy
> new_id_master= 'the id of new master record (copy of the above)
>
> strsql="Select * From detail where id='" & old_id_master & "'"
> set rs=Conn.Execute(strsql)
> do while not rs.Eof
> strsql="Insert Into detail (id, data, sede, esecutori, brani_autori)"
> & " Values ('" & new_id_master & "','" & data & "','" & _
> sede "','" & esecutori & "','" & brani_autori & "')"
> Conn.Execute(strsql)
> rs.movenext
> loop
> rs.close

The most likely reason for your problem is that you are not running with
SET NOCOUNT ON, and when you fail to pick up the rowcount, ADO opens a
second connection behind your back, and then you block yourself.

However, the code you have is not very good. There is no reason to run
a loop to get all data up to the client just to shove it back again.
You can copy all in one statement. Furthermore you should learn to
use parameterised commands and stop interpolating parameters directly
into your SQL strings.

cmd.txt = "INSERT detail(id, data, sede, esecutori, brani_autori) " & _
"SELECT ?, data, sede, esecutori, brani_autori " & _
"FROM detail " & _
"WHERE id = ?"
cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, new_id_master
cmd.CreateParameter "@new_master_id", adGUID, adParamInput,, old_id_master
cmd.Execute


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]