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

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
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
    ... I'm now able to use a client-side cursor (which allows me to ... > always use SQLOLEDB for accessing SQL server rather than MSDASQL. ... > Try creating a UDL file and using this for your connection string. ... >> adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ...
    (microsoft.public.vb.database.ado)
  • Re: One Connection String for Multiple Users (SQL)
    ... Hitchhiker’s Guide to Visual Studio and SQL Server ... "William Vaughn" wrote: ... This uses the> same connection string for all instances of the application. ...
    (microsoft.public.sqlserver.connect)
  • RE: How do I configure Analysis Services for Excel 2003 users?
    ... they had had Office 2003 installed AFTER SQL Server. ... > I have been preparing a set of cubes in Analysis Services 2005 to be viewed ... > Excel reports have been designed as pivot tables based on AS cubes, ... > Surely it must be that the connection string is incorrect but I seem to have ...
    (microsoft.public.sqlserver.olap)
  • Re: Connecting to Sql Server using an IP address
    ... using IP address without port number (default port number ... of SQL Server is 1433) cannot connect to the SQL Server on a remote ... name in the connection string can connect the remote machine successfully. ... Microsoft Online Community Support ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem connecting to an SQL 2005 Express Instance in RANU mod
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... I'm using SQL2005 Express User Instance Feature. ... attaching an MDB file with the following connection string: ...
    (microsoft.public.sqlserver.connect)