CODEWITHSUNDEEP
c#asp.netsql-servervb.net
Jordan Harvey
Jordan Harvey

Reputation: 23

Allow multiple active connections to MS SQL database using VS datasets

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

Answers (1)

Hobbes
Hobbes

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

Related Questions