RE: Strange behavior from ADODB and SQL Server

From: mklapp (mklapp_at_discussions.microsoft.com)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 08:43:01 -0700

I have located what seems to be the cause of the problem. I have been using
a single connection to open all recordsets. KB article 235282 suggests
stting cursor location to the client to avoid a problem when opening more
than one recordset on the same connection.

My error has gone but the confusion has not.

The inescapable fact is that the same code ran against the same data,
succeeding once and failing all the rest. Actually, while the program failed
many times before it succeeded, this problem never showed up until the
finished program was demonstrated in a fresh session of the IDE (VS.NET).

I suspect some 'residue' from repeated tests and changes allowed the
statement to succeed until a 'new' IDE 'session' was used.

"mklapp" wrote:

> Hello,
>
> This is a difficult question to ask because most of the circumstances are
> not internally consistent.
>
> This function (not a canonical function, just a collection of subs that
> perform a job) Connects to SQL Server 2000 through an ADODB dataset (MDAC
> 2.8).
>
> All ran well. In order to improve performance and remove connection
> timeouts, I have all my opens use the same ADO connection.
>
> Now one open consistently returnrs either a 'Connection failure' or 'Thread
> has been stopped'.
>
> The statement:
>
> sql = "SELECT AdditionalContacts.ContactDate, '53' AS visitcode, "
> sql = sql & "'" & "BOTH" & "'" & " AS session,
> AdditionalContacts.DateStamp, "
> sql = sql & " AdditionalContacts.StudentID,
> StudentVisits.StudentVisitsId "
> sql = sql & " FROM (AdditionalContacts LEFT JOIN TransData ON
> (AdditionalContacts.ContactDate = "
> sql = sql & " TransData.VisitDate) AND
> (AdditionalContacts.StudentID = TransData.StudentId)) "
> sql = sql & " INNER JOIN StudentVisits ON
> (AdditionalContacts.ProviderNumber = StudentVisits.ProviderNumber) "
> sql = sql & " AND (AdditionalContacts.StudentID =
> StudentVisits.StudentId) "
> sql = sql & " WHERE (((TransData.VisitDate) Is Null)) "
>
> has run for months. Now I cannot get it to execute at all. The open
> completes without apparent error, but none of the recordset properties can be
> displayed in the command window (error: cannot obtain value).
>
> I am going to spend some time evaluating the above SQL Server statement, but
> as I said, this program has run successfully with the existing source code
> for a time. Now, it consistently breaks.
>
> Ideas?
>
> Mklapp
>

Relevant Pages

  • Re: ActiveConnection getting lost
    ... Recordset or Execute method of the Command, but set of the settings will be ... CursorLocation property of the Connection, not recordset, to open cursor. ... >> after changes done to the recordset, pass it back to the ActiveX, set its ... >>> opens and closes connections and allows programs to share them. ...
    (microsoft.public.vb.database.ado)
  • Re: not in list problem
    ... the above code is part of what it runs when the application opens. ... how can i use a global connection object. ... > connection string to open a recordset, ...
    (microsoft.public.access.formscoding)
  • Re: Old topic scratched again -- "already an open DataReader"
    ... that only 1 datareader can be opened per connection. ... opens another connection and another attached READER, ... Is there a better way than above to seek data from another recordset ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VB and connection to SQL Server
    ... I made a mistake in designing the connection to the SQL Server. ... > RecordSetName.open query, SQLServer ... You do realize that merely setting the recordset to Nothing does not ... > routine opens a connection and the Timer closes it ...
    (microsoft.public.vb.general.discussion)
  • RE: open arguments.
    ... Can you add a value to the Reference column? ... Dim rst As Recordset ... This opens the form specified by stDocName in data entry mode and passes the ...
    (microsoft.public.access.modulesdaovba)