Re: Cannot Open Any More Tables by Stephany
Stephany
Tue Apr 05 13:55:31 CDT 2005
You have perfectly described why in you 'car' example. Your base class
provides a 'framwork' for you to expand on on your derived class(es).
Obviously, in a (derived) SQLConnection class, et al, there is additional
functional over and above what is defined in the base class (IDBConncetion).
What that might be I do not know and have no interest in finding out, but it
is clear that it handles your "Cannot Open Any More Tables." condition.
By the way: Your original post showed you instantiating an IDBConnection
connection object directly. It did NOT show you deriving a class from it and
using the derived class.
"Matthew Holton" <MatthewHolton@discussions.microsoft.com> wrote in message
news:40A35FEA-D7BB-4AE5-941B-EEEEA3C4311D@microsoft.com...
> Stephany,
>
> You are correct in that you can not instantiate an interface, you can only
> create objects that implement interfaces. Therefore, IDBConnection is the
> base object for data connection objects, and I should be able to use that
> to
> create SQLConnection, OLEDBConnection and ODBCConnection. In my base
> class I
> should be able to create a place holder for my object so that objects can
> refer to a common object while the mechanics will still be different.
>
> My question is: why doesn't the OLEDBConnection behave the same when first
> declared as IDBConnection when it behaves as expected when declared as
> OLEDBConnection? For the most part, every method that I call on a
> CommandObject and Adapter seems to behave properly, with the exception of
> ExecuteNonQueuery. After a certain number of calls, an OLEDBException is
> thrown with a description "Cannot Open Any More Tables.".
>
> 'my basedataops class
> Protected objConnection as IDBConnection
>
> 'SQL Class Inherits basedataopclass
> sub new
> objConnection = new SQLClient.SQLConnection
> end sub
>
> 'OLEDB Class Inherits basedataopclass
> sub new
> objConnection = new OLEDB.OLEDBConnection
> end sub
>
> 'ODBC Class Inherits basedataopclass
> sub new
> objConnection = new ODBCClient.ODBCConnection
> end sub
>
> Example
>
> Module StartupModule
>
> Sub Main()
> Dim objRace As Race
> objRace = New Race
> System.Threading.Thread.Sleep(5000)
> End Sub
>
> End Module
>
> Public Class Race
>
> Private pCar1 As ICar
> Private pCar2 As ICar
>
> Public Sub New()
>
> Console.WriteLine("Console Started")
> pCar1 = New Pinto
> AddHandler pCar1.Accelerating, AddressOf Car_Accelerating
> AddHandler pCar1.Breaking, AddressOf Car_Breaking
>
> pCar2 = New Camero
> AddHandler pCar2.Accelerating, AddressOf Car_Accelerating
> AddHandler pCar2.Breaking, AddressOf Car_Breaking
>
> pCar1.Accelerate()
> pCar2.Accelerate()
> pCar2.Brake()
> pCar1.Brake()
>
> Console.WriteLine("Console Terminating")
>
> End Sub
>
> Private Sub Car_Accelerating(ByVal Car As ICar)
> Console.WriteLine(TypeName(Car) & ": has a speed of " & Car.Speed)
> End Sub
>
> Private Sub Car_Breaking(ByVal Car As ICar)
> Console.WriteLine(TypeName(Car) & ": has a speed of " & Car.Speed)
> End Sub
>
> Protected Overrides Sub Finalize()
> MyBase.Finalize()
>
> RemoveHandler pCar1.Accelerating, AddressOf Car_Accelerating
> RemoveHandler pCar1.Breaking, AddressOf Car_Breaking
>
> RemoveHandler pCar2.Accelerating, AddressOf Car_Accelerating
> RemoveHandler pCar2.Breaking, AddressOf Car_Breaking
>
> End Sub
>
> End Class
>
> Public Interface ICar
> Event Accelerating(ByVal Car As ICar)
> Event Breaking(ByVal Car As ICar)
> ReadOnly Property Speed() As Decimal
> Sub Accelerate()
> Sub Brake()
> End Interface
>
> Public Class Pinto
> Implements ICar
>
> Public Event Accelerating(ByVal Car As ICar) Implements ICar.Accelerating
> Public Event Breaking(ByVal Car As ICar) Implements ICar.Breaking
>
> Private pintSpeed As Decimal
>
> Public Sub New()
> pintSpeed = 0D
> End Sub
>
> Public Sub Accelerate() Implements ICar.Accelerate
> pintSpeed = pintSpeed + 1.5D
> RaiseEvent Accelerating(Me)
> End Sub
>
> Public Sub Brake() Implements ICar.Brake
> pintSpeed = pintSpeed - 0.36D
> RaiseEvent Breaking(Me)
> End Sub
>
> Public ReadOnly Property Speed() As Decimal Implements ICar.Speed
> Get
> Return pintSpeed
> End Get
> End Property
>
>
> End Class
>
> Public Class Camero
> Implements ICar
>
> Public Event Accelerating(ByVal Car As ICar) Implements ICar.Accelerating
> Public Event Breaking(ByVal Car As ICar) Implements ICar.Breaking
>
> Private pintSpeed As Decimal
>
> Public Sub New()
> pintSpeed = 0D
> End Sub
>
> Public Sub Accelerate() Implements ICar.Accelerate
> pintSpeed = pintSpeed + 3.75D
> RaiseEvent Accelerating(Me)
> End Sub
>
> Public Sub Brake() Implements ICar.Brake
> pintSpeed = pintSpeed - 0.52D
> RaiseEvent Breaking(Me)
> End Sub
>
> Public ReadOnly Property Speed() As Decimal Implements ICar.Speed
> Get
> Return pintSpeed
> End Get
> End Property
>
> End Class
>
>
> "Stephany Young" wrote:
>
>> Have you read the documentation for the IDBConnection interface?
>>
>> <quote>
>> An application does not create an instance of the IDbConnection
>> interface
>> directly, but creates an instance of a class that inherits IDbConnection.
>> <unquote>
>>
>> Also, not that IDBConnection is an interface, as opposed to
>> OleDbConnection
>> which is a class which inherits the IDBConnection interface.
>>
>> So the answer is, use OleDbConnection.
>>
>>
>> "Matthew Holton" <MatthewHolton@discussions.microsoft.com> wrote in
>> message
>> news:030DF5BD-DC65-4084-827F-C3F92FAA4687@microsoft.com...
>> >I was working on creating a data library to use throughout my
>> >applications,
>> > when I came across an odd behavior. I encounter "Cannot Open Any More
>> > Tables." After using the OleDBCommand.ExecuteNonQuery method. I have
>> > included code that recreates the exception. I cannot recreate this
>> > error
>> > with the SQLCommand.ExecuteNonQuery method.
>> >
>> > The problem only occurs when I first declare my connection object as
>> > the
>> > base object IDBConnection. If I use OLEDBConnection from the start,
>> > then
>> > I
>> > am fine.
>> >
>> >
>> > Any Ideas?
>> >
>> >
>> > Thanks
>> >
>> >
>> > Private Structure Record
>> > Dim Rank As Long
>> > Dim BoyName As String
>> > Dim BoyChosen As Long
>> > Dim GirlName As String
>> > Dim GirlChosen As Long
>> > End Structure
>> >
>> > Private aNames As ArrayList
>> >
>> > Private Sub LoadData()
>> >
>> > 'Dim pobjTest As ATG.DataLayer.BaseConnection
>> > Dim pobjConnection As System.Data.IDbConnection
>> > pobjConnection = New System.Data.OleDb.OleDbConnection
>> >
>> > ReadNames()
>> >
>> > 'pobjTest = New
>> > ATG.DataLayer.OLEData("Provider=Microsoft.Jet.OLEDB.4.0;User
>> > ID=Admin;Data
>> > Source=C:\Documents and Settings\matt\My
>> > Documents\TimeKeeper.mdb;Mode=Share
>> > Deny None;")
>> > 'pobjTest.Connect()
>> >
>> > pobjConnection.ConnectionString =
>> > "Provider=Microsoft.Jet.OLEDB.4.0;User
>> > ID=Admin;Data Source=C:\Documents and Settings\matt\My
>> > Documents\TimeKeeper.mdb;Mode=Share Deny None;"
>> > pobjConnection.Open()
>> >
>> > 'pobjTest.BeginTrans()
>> >
>> > For Each oRec As Record In aNames
>> > ExecuteSQL("INSERT INTO [NAMES] (Rank, BoyName, BoyChosen,
>> > GirlName,
>> > GirlChosen) VALUES('" & oRec.Rank.ToString.Replace("'", "''") & "','" &
>> > oRec.BoyName.Replace("'", "''") & "'," &
>> > oRec.BoyChosen.ToString.Replace("'",
>> > "''") & ",'" & oRec.GirlName.Replace("'", "''") & "'," &
>> > oRec.GirlChosen.ToString.Replace("'", "''") & ")", pobjConnection)
>> > Next
>> >
>> > 'pobjTest.CommitTrans()
>> >
>> > pobjConnection.Close()
>> > pobjConnection = Nothing
>> >
>> > End Sub
>> > Private Function ExecuteSQL(ByVal SQL As String, ByVal pobjconnection
>> > As
>> > System.Data.IDbConnection) As Long
>> > ' Executes a SQL Statement
>> > ' Checks for an active
>> > ' transaction and uses
>> > ' it.
>> > ' Returns the number of
>> > ' rows affected
>> > '
>> >
>> > Const Routine As String = "ExecuteSQL()"
>> > Const pstrExceptionDescription As String = "There was an error
>> > executing
>> > a SQL statement."
>> > Dim cmd As OleDb.OleDbCommand
>> > Dim iRet As Integer = -1
>> >
>> > Try
>> >
>> > 'If Me.State = ConnectionState.dbOpenConnection Or Me.State =
>> > ConnectionState.dbInTransaction Then
>> >
>> > 'we have an active connection
>> > 'Check to see if we are in a transaction
>> > 'If Me.plngState = ConnectionState.dbInTransaction Then
>> > 'cmd = New OleDb.OleDbCommand(SQL, pobjConnection,
>> > pobjTransaction)
>> > 'Else
>> > cmd = New OleDb.OleDbCommand(SQL, pobjconnection)
>> > 'End If
>> >
>> > 'execute the statement
>> > cmd.Prepare()
>> > iRet = cmd.ExecuteNonQuery()
>> >
>> > cmd = Nothing
>> >
>> > 'Else
>> >
>> > 'ThrowCloseStateError(Routine)
>> >
>> > 'End If
>> >
>> > Catch ex As OleDbException
>> >
>> > 'There was a problem with the sql connection
>> > 'Set our state
>> > 'Me.plngState = ConnectionState.dbError
>> > Debug.WriteLine(SQL)
>> > MsgBox(ex.ToString)
>> > 'Throw New DataLayer.DataException(pstrExceptionDescription, ex,
>> > SQL)
>> >
>> > Catch ex As Exception
>> >
>> > 'Some other error occured
>> > Debug.WriteLine(SQL)
>> > 'Throw New DataLayer.DataException(pstrExceptionDescription, ex,
>> > SQL)
>> >
>> > Finally
>> >
>> > cmd = Nothing
>> >
>> > End Try
>> >
>> > Return iRet
>> > End Function
>> >
>> > Private Sub ReadNames()
>> >
>> > Dim oRec As Record, Line As String
>> > Dim aRec() As String
>> > aNames = New ArrayList
>> >
>> > Dim oFile As System.IO.StreamReader = New
>> > System.IO.StreamReader("c:\2003 popular names.txt.csv")
>> >
>> > Do
>> >
>> > Line = oFile.ReadLine()
>> >
>> > If Line Is Nothing Then Exit Do
>> >
>> > aRec = Line.Split(",")
>> > 'Debug.WriteLine("Read:" & Line)
>> >
>> > With oRec
>> > .Rank = aRec(0)
>> > .BoyName = aRec(1)
>> > .BoyChosen = aRec(2)
>> > .GirlName = aRec(3)
>> > .GirlChosen = aRec(4)
>> > End With
>> >
>> > aNames.Add(oRec)
>> >
>> > Loop Until Line Is Nothing
>> >
>> > End Sub
>> >
>> >
>> >
>>
>>
>>