Solved

Cannot open any more tables error

Posted on 2009-05-11
9
632 Views
Last Modified: 2013-11-26
Please HELP!! I am so frustrated using VB.NET accessing an MS Access database.  I have an application where I am trying to insert records into a table using an OledbDataReader.  When my application starts a class is created and a connection is opened to my database.  The application also uses datatable adapters.  I create and open the connection when the application starts so I can reuse it throughout the application.  The connection is closed when the application stops.  When I run the function I get the error as shown in the attached file.  It occurs randomly.  However if I debug the program and put a break point on the insert function it works fine.  If anyone can help me with this I would appreciate it.  If I could offer more than 500 points I would!  When the error occurs, the line number indicates it is at the oComm.ExecuteReader() statements (either one).
Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click

        Dim i As Integer

        Dim fc As New FeesClass

        Dim tdate As Date = Now()

        Dim smonth, sdays, syear, sdate As String

        Dim imonth, iyear, empProcessed As Integer
 

        Processing.Visible = True

        ChargeFees.Visible = False
 

        ' Setup Progress Bar

        ProgressBar1.Minimum = 1

        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount

        ProgressBar1.Step = 1

        ProgressBar1.Visible = True
 

        ' Parse the month selected

        tdate = Today()

        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)

        imonth = MonthInteger(smonth)

        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)

        iyear = CInt(syear)

        sdays = tdate.DaysInMonth(iyear, imonth)

        sdate = smonth & "/" & sdays & "/" & syear
 

        empProcessed = 0

        For i = 0 To Me.EmployeesDataGridView.RowCount - 1

            ' Add Admin Fee

            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then

                empProcessed += 1

            End If

            ProgressBar1.PerformStep()

        Next

        Completed.Visible = True

        MessageBox.Show("Employees Processed: " & empProcessed)

        ChargeFees.Visible = True

    End Sub
 

FeesClass.vb

=================================

    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean

        Dim oComm As OleDbCommand

        Dim oQuery As String

        Dim MonthStr As String

        Dim cnt As Integer = 0
 

        Try

            MonthStr = DetermineMonthFee(fdate)
 

            Try

                ' Format query

                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"

                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr
 

                Try

                    ' Perform the Query

                    drFeeTable = oComm.ExecuteReader()

                    Do While drFeeTable.Read

                        If cnt = 0 Then

                            ' Update the New Employee Fee

                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"

                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")

                            If oComm.ExecuteNonQuery() < 0 Then

                                InsertAdminFee = False

                            Else

                                InsertAdminFee = True

                            End If

                        End If

                        cnt += 1

                    Loop

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If
 

                    If cnt = 0 Then

                        ' Fee record does not exist - create it

                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"

                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr

                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                        If oComm.ExecuteNonQuery() < 0 Then

                            InsertAdminFee = False

                        Else

                            InsertAdminFee = True

                        End If

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                    InsertAdminFee = False
 

                End Try
 

                Try

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
 

                End Try

            Catch ex As Exception

                MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                InsertAdminFee = False
 

            End Try

        Catch ex As Exception

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            InsertAdminFee = False

        End Try
 

    End Function

Open in new window

toomanytablesopen.png
0
Comment
Question by:dyarosh
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Assisted Solution

by:JackOfPH
JackOfPH earned 100 total points
ID: 24360767
>>I create and open the connection when the application starts so I can reuse it throughout the application.  The connection is closed when the application stops.

Do not do the above:

The best practice is to open the connection only when you need it and close it when you no longer need it...


2:

3:

4:

5:

6:

7:

8:

9:

10:

11:

12:

13:

14:

15:

16:

17:

18:

19:

20:

21:

22:

23:

24:

25:

26:

27:

28:

29:

30:

31:

32:

33:

34:

35:

36:

37:

38:

39:

40:

41:

42:

43:

44:

45:

46:

47:

48:

49:

50:

51:

52:

53:

54:

55:

56:

57:

58:

59:

60:

61:

62:

63:

64:

65:

66:

67:

68:

69:

70:

71:

72:

73:

74:

75:

76:

77:

78:

79:

80:

81:

82:

83:

84:

85:

86:

87:

88:

89:

90:

91:

92:

93:

94:

95:

96:

97:

98:

99:

100:

101:

102:

103:

104:

105:

106:

107:

108:

109:

110:

111:

112:

113:

114:

115:

116:

117:

118:
 

	
 

Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click

        Dim i As Integer

        Dim fc As New FeesClass

        Dim tdate As Date = Now()

        Dim smonth, sdays, syear, sdate As String

        Dim imonth, iyear, empProcessed As Integer

 

        Processing.Visible = True

        ChargeFees.Visible = False

 

        ' Setup Progress Bar

        ProgressBar1.Minimum = 1

        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount

        ProgressBar1.Step = 1

        ProgressBar1.Visible = True

 

        ' Parse the month selected

        tdate = Today()

        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)

        imonth = MonthInteger(smonth)

        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)

        iyear = CInt(syear)

        sdays = tdate.DaysInMonth(iyear, imonth)

        sdate = smonth & "/" & sdays & "/" & syear

 

        empProcessed = 0

        For i = 0 To Me.EmployeesDataGridView.RowCount - 1

            ' Add Admin Fee

            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then

                empProcessed += 1

            End If

            ProgressBar1.PerformStep()

        Next

        Completed.Visible = True

        MessageBox.Show("Employees Processed: " & empProcessed)

        ChargeFees.Visible = True

    End Sub

 

FeesClass.vb

=================================

    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean

        Dim oComm As OleDbCommand

        Dim oQuery As String

        Dim MonthStr As String

        Dim cnt As Integer = 0

 

        Try

            MonthStr = DetermineMonthFee(fdate)

 

            Try

                ' Format query

                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"

                If MyForms.Startup.pfms.oConn.state = ConnectionState.Open then

MyForms.Startup.pfms.oConn.close

end if
 

MyForms.Startup.pfms.oConn.open
 

                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr

 

                Try

                    ' Perform the Query

                    drFeeTable = oComm.ExecuteReader()

                    Do While drFeeTable.Read

                        If cnt = 0 Then

                            ' Update the New Employee Fee

                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"

                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")

                            If oComm.ExecuteNonQuery() < 0 Then

                                InsertAdminFee = False

                            Else

                                InsertAdminFee = True

                            End If

                        End If

                        cnt += 1

                    Loop

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If

 

                    If cnt = 0 Then

                        ' Fee record does not exist - create it

                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"

                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr

                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                        If oComm.ExecuteNonQuery() < 0 Then

                            InsertAdminFee = False

                        Else

                            InsertAdminFee = True

                        End If

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                    InsertAdminFee = False

 

                End Try

 

                Try

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)

 

                End Try

            Catch ex As Exception

                MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                InsertAdminFee = False

 

            End Try

        Catch ex As Exception

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            InsertAdminFee = False

        End Try

 

    End Function

Open in new window

0
 
LVL 18

Accepted Solution

by:
philipjonathan earned 350 total points
ID: 24360769
I am suspecting this problem is because the database connection timed out.
Try to avoid keeping the connection open for too long, and instead open connection only when database operation needs to be performed, and close it as soon as the operation is done.

Don't worry about the performance at this point in time, the connection is pooled, means only the 1st connection takes longer to initialise, while subsequent ones are faster.

You can create the connection at the beginning of the application (assuming it's not multi-threaded). Then in function InsertAdminFee, just before you create command object, open the connection. Then add Finally clause to the corresponding try block, and close the connection.

Example:
Try
    My.Forms.Startup.pfms.oConn.Open()
    oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
    ...

Catch ...

Finally
    My.Forms.Startup.pfms.oConn.Close()

End Try
0
 
LVL 15

Assisted Solution

by:JackOfPH
JackOfPH earned 100 total points
ID: 24360775
try this code:
Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click

        Dim i As Integer

        Dim fc As New FeesClass

        Dim tdate As Date = Now()

        Dim smonth, sdays, syear, sdate As String

        Dim imonth, iyear, empProcessed As Integer

 

        Processing.Visible = True

        ChargeFees.Visible = False

 

        ' Setup Progress Bar

        ProgressBar1.Minimum = 1

        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount

        ProgressBar1.Step = 1

        ProgressBar1.Visible = True

 

        ' Parse the month selected

        tdate = Today()

        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)

        imonth = MonthInteger(smonth)

        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)

        iyear = CInt(syear)

        sdays = tdate.DaysInMonth(iyear, imonth)

        sdate = smonth & "/" & sdays & "/" & syear

 

        empProcessed = 0

        For i = 0 To Me.EmployeesDataGridView.RowCount - 1

            ' Add Admin Fee

            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then

                empProcessed += 1

            End If

            ProgressBar1.PerformStep()

        Next

        Completed.Visible = True

        MessageBox.Show("Employees Processed: " & empProcessed)

        ChargeFees.Visible = True

    End Sub

 

FeesClass.vb

=================================

    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean

        Dim oComm As OleDbCommand

        Dim oQuery As String

        Dim MonthStr As String

        Dim cnt As Integer = 0

 

        Try

            MonthStr = DetermineMonthFee(fdate)

 

            Try

                ' Format query

                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"

                If MyForms.Startup.pfms.oConn.state = ConnectionState.Open then

MyForms.Startup.pfms.oConn.close

end if

 

MyForms.Startup.pfms.oConn.open

 

                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr

 

                Try

                    ' Perform the Query

                    drFeeTable = oComm.ExecuteReader()

                    Do While drFeeTable.Read

                        If cnt = 0 Then

                            ' Update the New Employee Fee

                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"

                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")

                            If oComm.ExecuteNonQuery() < 0 Then

                                InsertAdminFee = False

                            Else

                                InsertAdminFee = True

                            End If

                        End If

                        cnt += 1

                    Loop

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If

 

                    If cnt = 0 Then

                        ' Fee record does not exist - create it

                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"

                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)

                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr

                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID

                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75

                        If oComm.ExecuteNonQuery() < 0 Then

                            InsertAdminFee = False

                        Else

                            InsertAdminFee = True

                        End If

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                    InsertAdminFee = False

 

                End Try

 

                Try

                    If (Not oComm Is Nothing) Then

                        oComm.Dispose()

                        oComm = Nothing

                    End If

                Catch ex As Exception

                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)

 

                End Try

            Catch ex As Exception

                MsgBox(ex.Message & vbCrLf & ex.StackTrace)

                InsertAdminFee = False

 

            End Try

        Catch ex As Exception

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            InsertAdminFee = False

        End Try

 

    End Function

Open in new window

0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 50 total points
ID: 24360816
Hi dyarosh;
   
From MSDN Library
Cannot open any more tables. (Error 3014)
 You have reached the limit on the number of tables that can be opened at one time. Close one or more tables, and then try the operation again.

There seems to be a limit on the number of open tables.

Fernando
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 15

Expert Comment

by:JackOfPH
ID: 24360893
As Fernando Sotto, mentioned in his above code, There is a limit on the number of open tables. Actually in one connection.

So, the best practice is to open the connection only when you need it and close it after you use it in your code.
0
 

Author Comment

by:dyarosh
ID: 24362910
The consensus seems to be that my connection is timing out and I should open and close the connection each time I need it.  I have some functions that write to multiple tables which exist in different classes.  Should I open a connection in each class or open the connection in the function that calls the multiple database queries?
0
 

Author Closing Comment

by:dyarosh
ID: 31580387
philipjonathan gave the most complete answer to my query be explaining why the error was occuring which is why I awarded him the most points.  Everyone else gave the same advice about only opening the connection when needed.  I made the change and it works fine now.  Thanks for your help.  It is much appreciated.
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24370780
Thanks for the accept.
As for your last question, I don't have hard fast rules.
But as a guideline, if you know the multiple write operations are going to be in rapid succession, then it's better to just open the connection once and get the classes to use that connection object. The advantage is if you want to use DB transaction, you can initiate the transaction once, and commit it after all the writes are done. You can't do so with multiple connections in each class.
0
 

Author Comment

by:dyarosh
ID: 24370913
Thanks for the info.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Icons and Colors for Terms 3 24
Need help on C# Linq query on object 6 51
Receiving a string from a WebService Push 21 31
Get Top Visible Row of Datagridview 6 26
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
A short film showing how OnPage and Connectwise integration works.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now