Cannot Open Any More Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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.


message
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top