Solved

Cannot open any more tables error

Posted on 2009-05-11
9
633 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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