Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?

Have you tried checking the goConn.Errors collection?

You should get a goConn.Errors.Count in excess of 0 if there is an error,
you can then step through each error to check the result.


--
Terry Kreft



"William Hildebrand" <wahilde@xxxxxxxxxxxx> wrote in message
news:11nicfqkmlum68a@xxxxxxxxxxxxxxxxxxxxx
> Hi Ralph,
>
> I tried using WithEvents and still no luck. What I did discover though is
> that I CAN trap the error if I set the ADODB connection object's
> CursorLocation property to adUseServer instead of adUseClient. If I set
> it to adUseClient (which is what I was doing), then I can't trap the
> error.
>
> Shouldn't I be able to trap the error even though I'm using the
> client-side cursor location property? It would be kind of a pain if I
> have to use the server-side cursor location property because when I use
> that then there are some methods that don't work such as the recordset's
> RecordCount property and a few others that I can't recall at the moment.
>
> William Hildebrand
> wahilde@xxxxxxxxxxxx
>
>
>
> "Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
> news:_OSdnXpWqtz7n2DfRVn-hQ@xxxxxxxxxxxxxxx
>>
>> "William Hildebrand" <wahilde@xxxxxxxxxxxx> wrote in message
>> news:11fpoa5gn4reg4d@xxxxxxxxxxxxxxxxxxxxx
>>> Hello,
>>>
>>> I have a VB6 executable that uses SQL Server 2000 as it's database. I
>> have
>>> a function similar to this (note that goConn is a
>>> global ADO connection object that is already open):
>>>
>>> Private Function WriteTrans() As Long
>>> 'Write the tblSalesTransHdr record.
>>> Dim oRSHdr As New ADODB.Recordset
>>>
>>> On Error GoTo TransError
>>> Err.Clear
>>>
>>> goConn.BeginTrans
>>>
>>> 'This will always return an empty recordset.
>>> oRSHdr.Open ("select * from tblSalesTransHdr where DocID = 0"),
>> goConn,
>>> adOpenDynamic, adLockPessimistic
>>>
>>> oRSHdr.AddNew
>>> oRSHdr.Fields("DocType").value = "S"
>>> oRSHdr.Fields("SourceDocID").value = 0
>>> oRSHdr.Fields("DocSource").value = "MTI_CM4"
>>> oRSHdr.Fields("DocStatus").value = 0
>>> oRSHdr.Fields("Reference").value = ""
>>> oRSHdr.Fields("Memo").value = TransInfo.sMemo
>>> oRSHdr.Update
>>> oRSHdr.Close
>>>
>>> MsgBox "About to commit transaction."
>>>
>>> goConn.CommitTrans
>>> Set oRSHdr = Nothing
>>> WriteTrans = 0
>>> Exit Function
>>>
>>> TransError:
>>> On Error Resume Next
>>> goConn.RollbackTrans
>>> Set oRSHdr = Nothing
>>> WriteTrans = -1
>>> Exit Function
>>> End Function
>>>
>>> When the MsgBox dialog box appears I disconnect the ethernet cable that
>>> connects the client machine and the server machine then I click OK on
>>> the
>>> MsgBox dialog box. It takes quite a while before the function returns,
>>> so
>>> obviosly the CommitTrans statement is timing out, but the function
>>> always
>>> returns 0, so it didn't pass control to the TransError error handling
>>> routine.
>>>
>>> What's going on here? How can I know if the CommitTrans statement
>>> failed?
>>>
>>> Thanks,
>>> William Hildebrand
>>> wahilde@xxxxxxxxxxxx
>>>
>>>
>>
>> Have you tried ADO Events?
>>
>> Dim Withevents goConn As ADOB.Connection
>>
>> Private Sub goConn_CommitTransComplete(ByVal pError As ADODB.Error,
>> adStatus
>> As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
>> End Sub
>> Private Sub goConn_InfoMessage(ByVal pError As ADODB.Error, adStatus As
>> ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
>> End Sub
>> Private Sub goConn_RollbackTransComplete(ByVal pError As ADODB.Error,
>> adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
>> End Sub
>>
>>
>> Note: there can be a strange joint psychosis between providers and
>> supported
>> events.
>>
>> hth
>> -ralph
>>
>>
>
>


.

Relevant Pages

  • Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
    ... > I have a VB6 executable that uses SQL Server 2000 as it's database. ... As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ... Private Sub goConn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ...
    (microsoft.public.vb.database.ado)
  • ADO Asychronous Execution
    ... Private Sub Command1_Click ... Private Sub mrConn_CommitTransComplete(ByVal pError As ADODB.Error, adStatus ... As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ...
    (microsoft.public.data.ado)
  • Event Disconnect
    ... Private Sub Connect_WillConnect(ConnectionString As String, ... String, Password As String, Options As Long, adStatus As ... ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ...
    (microsoft.public.vb.database.ado)
  • Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
    ... that I CAN trap the error if I set the ADODB connection object's ... to adUseClient, then I can't trap the error. ... server-side cursor location property because when I use that then there are ... > Private Sub goConn_InfoMessage(ByVal pError As ADODB.Error, ...
    (microsoft.public.vb.database.ado)
  • Re: ADO events under Access 97
    ... Private WithEvents objcon As ADODB.Connection ... Private Sub objcon_ConnectComplete(ByVal pError As ADODB.Error, ... Long, ByVal pConnection As ADODB._Connection) ... > "Alex Dybenko" a écrit dans le message ...
    (microsoft.public.access.modulesdaovba)
Loading