-3

Should I use using?

 Private Sub btntest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntest.Click
        If sqlConnection.State = ConnectionState.Closed Then
            sqlConnection.Open()
        End If
        Dim query = "Select * from tablebusiness"

        Dim cmd = New MySqlCommand(query, sqlConnection)

        Dim data = cmd.ExecuteReader()

        Do While data.Read
        Loop

        Dim cmd1 = New MySqlCommand(query, sqlConnection)
    Dim data1 = cmd1.ExecuteReader //Error. Already have data reader 
                                  //Error There is already an open DataReader associated with this Connection which must be closed first.

    Dim check = 1

    'sqlConnection.Close()
End Sub
Improve this question
4
  • 1
    What is the error description? Commented May 23, 2012 at 8:21
  • 2
    What error are you getting? Is it a compilation error or an exception? Commented May 23, 2012 at 8:21
  • Fixed. Is this just me or does questions about vb.net tend to get downvoted and questions about objective-c tend to get upvoted even though I am a far more advance vb.net programmer than objective-c programmer Commented May 23, 2012 at 9:52
  • 1
    Your question probably got downvoted because you didn't provide the error message at first. Commented May 23, 2012 at 14:38

3 Answers 3

Reset to default
1

Although you have not let us know what the error is (which makes solving any problem much harder), I expect the issue is arising because you are trying to re-use the SqlConnection object for 2 different commands. Especially since you are not disposing your first command before initialising the second.

Firstly, use 2 different SqlConnection objects to manage the connection to the database. You are not putting any more overhead on the database or the code if you do this. Let the .NET framework connection pooling do its job - don't try to do it yourself. You don't need to do anything specific to enable connection pooling (although you can disable it by setting Pooling=false in your connection string).

Secondly use the using statement to correctly dispose your SqlConnection, SqlCommand, and SqlDataReader objects. e.g.

    Using connection As New SqlConnection(connectionString)
        connection.Open()
        Using Command As New SqlCommand(query, connection)
            Using reader As SqlDataReader = Command.ExecuteReader()
                While reader.Read()
                    'Do Stuff'
                End While
            End Using
        End Using
        connection.Close()
    End Using
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Perfect. That's exactly what the error say. What do you mean by using 2 different SQL connections won't increase overhead. In PhP it does right?
See guys, no need to downvote my questions. It's good question with great answer. Now let's congratulate GarethD for his easy 10 points. +1 and checked as the answer.
I know absolutely nothing about PHP unfortunately, so can't fully answer your question, but I can tell you that even if you use 2 different SqlConnection objects .NET may reuse the first connection on the second object if it deems fit. The connection pool will do a better job of managing connections than you or I will so it is best to just let its do its just and use 1 SqlConnection object per command/adapter. msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.80%29.aspx
I believe the down votes were because you did not include the error message. (Fortunately) I have had similar problems when trying to reuse connections so was able to guess a possible source of the error (and got lucky in getting it right), but the down votes would not have come if the original question had included details of the error you were receiving.
I still think it's harsh. Hiks hiks. Now that I've updated it, do they change their mind. So mean.... So mean....
|
1

You have missed the parenthesis after cmd1.ExecuteReader. It should be cmd1.ExecuteReader().

Improve this answer

3 Comments

This is not it. VB is not as fussy as c#, this will compile and run without the parentheses.
As far as I know It also requires in vb. Please visit msdn.microsoft.com/en-us/library/9kcbe65k.aspx#Y853
Try it. It will work with any parameterless method (e.g. Console.WriteLine(("ToStringExample").ToString)). I agree that on the MSDN page they use it, and in my opinion it is best practise to use them so that they easily distinguished from Properties, but this does not mean it is required.
1

You Need another Conncetion if you want both the datareaders to work simultaneously, else close/ dispose the previous command before using cmd1.ExecuteReader()

Improve this answer

1 Comment

Correct. Garetd answer is more complete.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.