Solved

Cannot open any more tables error

Posted on 2009-05-11
9
631 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

18 Experts available now in Live!

Get 1:1 Help Now