Reputation: 23
So I use a Visual Basic DataSet on an ASP.Net website, and when multiple users access the website I get something along the lines of:
ExecuteReader requires an open and available connection. The connection's current state is Open.
Can I create multiple connections to the SQL database?
I know the datasets have normal code behind them so I'm wondering if I could modify something in those files to create a new connection for each user session.
I've tagged c# because if an answer pops up in c# I'll convert it...
Upvotes: 0
Views: 1909
Reputation: 147
In a multi-threaded, multi-user environment like ASP.NET, you have to stay away from shared connections. You're better off creating the connection as needed and then disposing of it. Behind the scenes .NET will use connection pools, and SQL Server won't have any problems handling lots of multiple connections. Pseudo-code:
' SQLCommand to fill DataTable
Dim dt As New DataTable
Using cnn As New SqlConnection("someconnectionstring")
Dim cmd As New SqlCommand("SELECT * FROM SomeTable", cnn)
cnn.Open()
dt.Load(cmd.ExecuteReader)
cnn.Close()
End Using
' TableAdapter to fill DataSet
Dim ds As New DataSet
Dim ta As New SqlDataAdapter
ta.Fill(ds)
The [Using statement][1] lets .NET handle the disposal of the connection for you.
Upvotes: 2