?
Solved

Cannot open any more tables error

Posted on 2009-05-11
9
Medium Priority
?
638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Assisted Solution

by:JackOfPH
JackOfPH earned 400 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 1400 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 200 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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 …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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