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

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?
    ... > to adUseClient, then I can't trap the error. ... >> Private Sub goConn_InfoMessage(ByVal pError As ADODB.Error, ... The fact that this only works with a server-side ... Personally I have found RecordCount to be seldom 'needed'. ...
    (microsoft.public.vb.database.ado)
  • RE: Page change (switch) event ?
    ... Private Sub Document_PageChanged ... I wanted this event to fire every time I changed the active window using the ... while one would think this is a safe event to trap, ... > change stuff which causes other events, if it's before visio flushes some ...
    (microsoft.public.visio.developer.vba)
  • Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
    ... > have to use the server-side cursor location property because when I use ... >> As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ... >> Private Sub goConn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ...
    (microsoft.public.vb.database.ado)
  • RE: How to Capture right click Delete/Insert on Excel
    ... If you want to trap the right-click, then use this event macro in the ... Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, ... I am able to capture that event. ...
    (microsoft.public.excel.programming)
  • RE: C# Development in Visio 2007
    ... I'm not sure how to trap the events at the stencil from within an AddIn. ... Private Sub Document_DocumentOpened ... Private Sub myDoc_MasterAdded(ByVal Master As IVMaster) ...
    (microsoft.public.visio.developer)
Loading