Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- From: "William Hildebrand" <wahilde@xxxxxxxxxxxx>
- Date: Thu, 17 Nov 2005 15:43:28 -0700
Hey Terry,
That works! I'm now able to use a client-side cursor (which allows me to
use the RecordCount property of recordsets and hopefully all the other
properties and methods that went away when I tried using a server-side
cursor) AND the CommitTrans method now correctly returns an error when the
connection between the machines is down. I didn't have to use WithEvents.
Just plain old On Error Goto or On Error Resume Next will trap the error
now. Thank you VERY much!
William Hildebrand
wahilde@xxxxxxxxxxxx
"Terry Kreft" <terry.kreft@xxxxxxxxx> wrote in message
news:Uo2dnR8mMfVkgObeSa8jmw@xxxxxxxxxxxxxx
> Having seen your code I'm now wondering if it's a provider problem. I
> always use SQLOLEDB for accessing SQL server rather than MSDASQL.
>
> Try creating a UDL file and using this for your connection string. I
> don't know your level of expertise so please excuse the following if it
> appears that I'm teaching you how to suck eggs
>
> 1) create a text file and rename it to SomeName.udl
> (lets say it's now c:\SomeName.udl)
> 2)double click on the file a dialog will open asking for connection
> details
> a) make sure you change the provider on the provider tab to
> "Microsoft OLE DB Provider for SQL Server"
> b) then complete the Connection tab
> c) test the connection and correct as necessary.
> d) click OK
> 3) in your code change your connection string to
> "File Name = c:\SomeName.udl"
>
> This will then mean you are using the SQLOLEDB provider (if you want to
> see the full connection string then check the goConn.ConnectionString
> property after you use the goConn.Open method).
>
> Try your test again.
>
>
> --
> Terry Kreft
>
>
>
> "William Hildebrand" <wahilde@xxxxxxxxxxxx> wrote in message
> news:11nlcdek9rr7mcf@xxxxxxxxxxxxxxxxxxxxx
>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
.
- References:
- Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- From: William Hildebrand
- Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- From: Terry Kreft
- Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- From: William Hildebrand
- Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- From: Terry Kreft
- Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- Prev by Date: Re: Retrieving Data From Web Database
- Next by Date: Recordset.sort problem
- Previous by thread: Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
- Next by thread: DataType Problem reading CSV File with ADO
- Index(es):
Relevant Pages
|
