Timeout expired on delete.. (1 Viewer)

[neoklis]

Hi guys,


In a datasheet, on the delete event procedure I have the code below:

Static StrConn As String
Static rec As New ADODB.Recordset
Static cmd As New ADODB.Command

StrConn = "Driver={SQL Server};Server=xxx;Database=db;Trusted_Connection=Yes;"
cmd.ActiveConnection = StrConn
cmd.CommandText = "InsertIntoDailyProgramArchive " + CStr(Me.AutoAA) + "," + GetCurUser()
Set rec = cmd.Execute

The problem is that when I delete one record the store procedure is running great. When I choose to delete more than one row becomes the error below

Run-time error ‘-2147217871 (80040e31)’
[Microsoft][ODBC SQL Server Driver]Timeout expired

A message box appears which says debug – end. I click on debug and the error is appearing on Set rec = cmd.Execute. When I click to end it is deleting only the first row. I cannot figure out the cause of the problem…

 

[neoklis]

What do you think of adding the commit statement in my stored procedure..?

 

[SQL_Hell]

Try setting the timeout to zero. If the syntax isn't quite right here the concept is correct, so you should be able to figure it out from whats below.





Set cn = Server.CreateObject("ADODB.Connection")
Static StrConn As String
Static rec As New ADODB.Recordset
Static cmd As New ADODB.Command

StrConn = "Driver={SQL Server};Server=xxx;Database=db;Trusted_Connection= Yes;"
cn.Open StrConn
cn.CommandTimeout = 0

cmd.ActiveConnection = cn
cmd.CommandText = "InsertIntoDailyProgramArchive " + CStr(Me.AutoAA) + "," + GetCurUser()
Set rec = cmd.Execute

 

[neoklis]

Thanks for your reply..

I copied the code you posted in my mdb. I debug it and there is an error found on Server.CreateObject("ADODB.Connection") and say that variable not found.

 

[SQL_Hell]

Dim cn As New ADODB.Connection

 

[neoklis]

I've already add that line but the error remains..

 

[SQL_Hell]

where is the error? post the code....

 

[neoklis]

Static cn As New ADODB.Connection
Static StrConn As String
Static rec As New ADODB.Recordset
Static cmd As New ADODB.Command

Set cn = Server.CreateObject("ADODB.Connection")
StrConn = "Driver={SQL Server};Server=xxx;Database=db;Trusted_Connection= Yes;"
cn.Open StrConn
cn.CommandTimeout = 0

cmd.ActiveConnection = cn
cmd.CommandText = "InsertIntoDailyProgramArchive " + CStr(Me.AutoAA) + "," + GetCurUser()
Set rec = cmd.Execute


The debugger marks the area with the red color and says variable not defined

 

[SQL_Hell]

You're supposed to take the old line out...

Static cn As New ADODB.Connection
Static StrConn As String
Static rec As New ADODB.Recordset
Static cmd As New ADODB.Command


StrConn = "Driver={SQL Server};Server=xxx;Database=db;Trusted_Connection= Yes;"
cn.Open StrConn
cn.CommandTimeout = 0

cmd.ActiveConnection = cn
cmd.CommandText = "InsertIntoDailyProgramArchive " + CStr(Me.AutoAA) + "," + GetCurUser()
Set rec = cmd.Execute

 

[neoklis]

ok.. i execute the code but the 'Timeout expires' error message appears again.

The on delete event procedure is executing, for the number of rows i've selected to delete. If the number of selected rows is one, then the code is executing with no problem. If the number of selected rows >1, then the second time that is executing the onDelete procedure, the error below comes.

-2147217871 [Microsoft][ODBC SQL Server Driver]Timeout expired.

Maybe i should try to approach the action i want in a different way..

 

[SQL_Hell]

How is it deleting more than one row? cant see anything in the code that makes the procedure run x amount of times when there is more than one record to delete

 

[neoklis]

(Sorry for my English i'll try the best i can)

ok imagine this..

Let's say that i have a datasheet. In the datasheet the user can choose from recordselectors as many rows as he wants. He selects one row and hit the delete key from his keyboard. The ondelete procedure is executing and the stored procedure is succesfully executing. The user now selects 3 rows and hit the delete key. The ondelete procedure is succesfully executing for the first row and exits,then comes in back for the 2 and the error comes. If it was executing great for the second row it would continue for the x number of records the user selected.

So the ondelete event procedure is being executed for x number of rows thar user gonna select from datasheet. This is something i did n't know and i found it because of the problem i face.

 

[neoklis]

I finally make it to work ..!!!! Don’t ask me what cause the problem but I had a bad feeling about executing an sp from the on delete event procedure for x>1 rows. I’m really curious about the results if someone would like to reproduce my problem.. This is what I did..

Evp=event procedure

• In the properties of my datasheet form and in on click evp I am searching for the selected rows from the user with seltop and selheight
• I store the value (AutoAA) for the x selected from user rows into a public array
• In the on delete evp I read the values of the array and I execute the sp for each value into a for next. After the first row re-enters into the on delete evp and generate error. So when It first get into the ondelete and after executing the sp for all the array, I redim the array with a value ‘false’. With if endif, I am examining the value. If not false then execute, if false this is the second row, I’ve execute everything I want, so don’t execute.

Dim i As Long

For i = 1 To UBound(Arr_AA)

If Arr_AA(i) <> "False" Then

Static cn As New ADODB.Connection
Static StrConn As String
Static rec As New ADODB.Recordset
Static cmd As New ADODB.Command

StrConn = "Driver={SQL Server};Server=xxx;Database=db;Trusted_Connection=Yes;"
cn.Open StrConn
cn.CommandTimeout = 0

cmd.ActiveConnection = cn
cmd.CommandText = "InsertIntoDailyProgramArchive " + CStr(Arr_AA(i)) + "," + GetCurUser()
Set rec = cmd.Execute

cn.Close
​

End If
​

Next i

ReDim Arr_AA(1 To 1) As String
Arr_AA(1) = "False"


Thanks for being here for me..

 

[SQL_Hell]

Ok good glad you got it working.


Your method of iterating through the array until the value 'false' is found seems to be a good way of getting the result you wanted.