VB6 - SQL 2000 timeout

[RupertGiles]

Hi guys,
We have an in-house system (VB6 connecting to SQL 2k) that I must take at least half the blame for developing and designing. The user-base has nearly doubled and unfortunately I seem now to be getting an increasing number of time-outs at another domain that I cannot replicate on my domain.

I believe the error is the result of a SQL transaction but cannot be sure…

Below is a rough example of the code that I personally hold to blame…

On Error GoTo ErrorHandle

'Now Connect to the DB
Dim Insert_Command As New ADODB.Command
Dim Insert_Connection As New ADODB.connection
Dim ByTable As Byte
Dim I As Integer

With Update_Connection
' Establish DSN-less connection
.ConnectionString = PLCYConnectionString
.ConnectionTimeout = 10
.CursorLocation = adUseServer
.Open
End With

Set Update_Command = New ADODB.Command
Update_Command.ActiveConnection = Update_Connection

'Allow us to try and conquer our time-out error
On Error GoTo Err_Looper
Err_Looper:

If Err.Number > 0 Then
If Err.Number <> "-2147217871" Then GoTo ErrorHandle
End If
'Gives us 3 goes before errors
If I = 3 Then GoTo ErrorHandle

ByTable = 0
I = I + 1
Insert_Connection.BeginTrans
ByTable = 0
Insert_Command.CommandText = Str_Customer
Insert_Command.Execute
ByTable = 1
Insert_Command.CommandText = Str_MoreDetails
Insert_Command.Execute
ByTable = 2
Insert_Command.CommandText = Str_MoreDetails2
Insert_Command.Execute
ByTable = 3
If StrProcess <> "New Business" Then
Call NestedTransaction
End If
ByTable = 4
If Endorsements_Standard(frmHHold) = False Then Call NestedTransaction3
ByTable = 5
If Endorsements_Non_Standard(frmHHold) = False Then Call NestedTransaction4
Insert_Connection.CommitTrans
Insert_Connection.Close

On Error GoTo 0

Exit Sub



TIA - you could save my hairline!

 

[jebenson]

I don't know if its just typos, but you Dim Insert_Command and Insert_Connection, but you then configure Update_Command and Update_Connection, and open Update_Connection. Then later in the code you use Insert_Connection and Insert_Command.

Also, if you are executing stored procedures with your Command objects, you should set the Command objects' CommandType to adCmdStoredProc.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[RupertGiles]

Thanks Jebenson,
They are typo's unfortunately, have been doing a bit of messing around with the code, but hadn't yet compiled to pick up on my mistakes!! Where it says update it should be insert...

Unfortunately the inserts are not done via stored procedures yet, I understand I should be able to gain performance with this method.

I don't think the issue is down to cabling, switches etc as have other products (that go into other tables) that have smaller, quicker transactions and are not experiencing the time-out issue.

Appreciate the assistance