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

Hi Terry,

Yes, I checked the errors collection and it was empty. I also tried using
WithEvents and the following sub from that experiment always got executed,
but as the comment says -- 'pError is never set and adStatus is always
adStatusOK.

Private Sub Connection_CommitTransComplete(ByVal pError As ADODB.Error,
adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
If adStatus = adStatusErrorsOccurred Then
CommitTransError = pError.Number 'pError is never set and adStatus
is always adStatusOK
End If
End Sub


By the way, here is how I initialize my connection object:

Set goConn = New ADODB.Connection
goConn.ConnectionString = "FileDSN=CMServer.dsn;"
goConn.Mode = adModeReadWrite
goConn.CursorLocation = adUseClient
goConn.Open

And my connection string is:

Provider=MSDASQL.1;Mode=ReadWrite;Extended Properties="DRIVER=SQL
Server;SERVER=SYS-MWS;UID=Andy;APP=Microsoft Data Access
Components;WSID=SYS-WAHILDE;DATABASE=CMServer;Network=DBMSSOCN;Trusted_Connection=Yes"

My SQL Server driver (SQLSRV32.DLL) version is 2000.85.1117.00. This is (or
should be) the latest version of MSDE. Oh yeah, I guess I should have
mentioned that I'm using the latest version of MSDE rather than the retail
version of SQL Server 2000. From everything I've read, it shouldn't make
any difference except that there is a database size limit and Microsoft
purposely chokes down the performance, but I've read nothing that would
indicate that I would not be able to trap a CommitTrans error when you pull
the ethernet plug just before executing the CommitTrans statement when using
client-side cursors. Like I said, it works just fine if I use a server-side
cursor, but my boss says "That is not an exceptable solution to the
problem.". He is convinced that we should be able to use a client-side
cursor and still be able to trap such a critical error.

Well, please let me know if you have any other ideas about this or any
suggestions as to where I might look to find an answer. If I could just
find one official statement from somewhere that says I MUST use a
server-side cursor, then that's what I'll do, but I haven't read anything
that even remotely implies that this should be the case.

My sincere thanks goes to you for your input.

William Hildebrand
wahilde@xxxxxxxxxxxx


"Terry Kreft" <terry.kreft@xxxxxxxxx> wrote in message
news:G22dnZgeStrNceTeSa8jmw@xxxxxxxxxxxxxx
> 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 to change Font color and force the user to use it
    ... program enables track changes IF and ONLY IF the cursor is placed in a line ... Private Sub RadBtnTMS1_Click ... 'If cursor is placed in an empty line within a cell then ... Dim oChg As Revision ...
    (microsoft.public.word.vba.general)
  • Re: ADO CursorType Problem
    ... maintain server state such as a server-side cursor. ... > connection from the command object now. ... >>> Sub Application_OnStart ...
    (microsoft.public.data.ado)
  • Re: ADO CursorType Problem
    ... maintain server state such as a server-side cursor. ... > connection from the command object now. ... >>> Sub Application_OnStart ...
    (microsoft.public.data.oledb)
  • Re: Restarting a macro
    ... My goal is to have the cursor reset all the way back to ... cell that, when the macro moves the cursor to that cell, it would tell ... >> Sub NextCookie() ... >> ' Does not HideZeroUsage if called by ReportAll. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: ADO CursorType Problem
    ... maintain server state such as a server-side cursor. ... > connection from the command object now. ... >>> Sub Application_OnStart ...
    (microsoft.public.frontpage)
Loading