Hello, here is the code for the test program, just copy it in a new form.
Make sure you have a Access database (C:\Database.mdb) with a table named
Sessions with the fields [Session ID] Varchar(20), [Active] YESNO.
At first everything seems to work fine. But when it takes longer for the
dataset to fill, the error occures (in my case when there where around 40
records in the sessions table).
Private Shared DbConn_Thread_Form As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Password="""";User
ID=Admin;Data Source=C:\Database.mdb;Mode=Share Deny None;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False")
Private Shared DbConn_Thread_Timer As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Password="""";User
ID=Admin;Data Source=C:\Database.mdb;Mode=Share Deny None;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False")
Public TimerDelegate As New Threading.TimerCallback(AddressOf TimerTask)
Public TimerItem As Object
Dim Button1 As New System.Windows.Forms.Button
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Button1.Text = "Start"
AddHandler Button1.Click, AddressOf Button1_Click
Me.Controls.Add(Button1)
TimerItem = New System.Threading.Timer(TimerDelegate, Nothing, 1000,
3000)
End Sub
Sub TimerTask(ByVal StateObj As Object)
Beep()
Dim DbComm_Session As New OleDb.OleDbCommand("INSERT INTO [Sessions]
([Session ID], [Active]) VALUES ('" & Now() & "', -1)", DbConn_Thread_Timer)
DbConn_Thread_Timer.Open()
DbComm_Session.ExecuteNonQuery()
DbConn_Thread_Timer.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Button1.Enabled = False
Dim DS_Result As New DataSet
Dim DbComm_Result As New OleDb.OleDbDataAdapter("SELECT
Sessions.[Session ID], Sessions.Active FROM (((Sessions INNER JOIN Sessions
AS Sessions_1 ON Sessions.Active = Sessions_1.Active) INNER JOIN Sessions AS
Sessions_2 ON Sessions_1.Active = Sessions_2.Active) INNER JOIN Sessions AS
Sessions_3 ON Sessions_2.Active = Sessions_3.Active) INNER JOIN Sessions AS
Sessions_4 ON Sessions_3.Active = Sessions_4.Active", DbConn_Thread_Form)
DbComm_Result.Fill(DS_Result, "QueryData")
Button1.Enabled = True
End Sub
"Sankalp" wrote:
Let me have a look at the code you have written in the thread event-the
event handler which gets fired by your threads, the way you have
declared your connection object, they way you open database
connections.
As you said, each thread will be starting its own connection. Think of
them as separate pieces of code-when you create a NEW connection
object, and the other thread also creates a NEW connection object, the
previous thread has got nothing to do with it. They are separate
objects created in the managed pool and its your softwares
responsibility to return them back to the connection pool when you dont
need them. This is where you HAVE to close connections in a FINALLY
block after checking whether your application was able to open them in
the first place or not.
The ideal way to open connections is to use connection pooling as that
will increase the performance of your application greatly and more so
when you have a multithreaded application. The .NET framework
automatically creates a connection pool for you for each unique
connection string. But this is not the point being discussed here.
Sankalp