Why do I got error when I create 2 commands on SQL in vb.net

Question

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

What error are you getting? Is it a compilation error or an exception? — Daniel Hilgarth, May 23 '12 at 08: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 — user4951, May 23 '12 at 09:52
Your question probably got downvoted because you didn't provide the error message at first. — Chris Dunaway, May 23 '12 at 14:38

score 1 · Answer 1 · answered May 23 '12 at 08:34

1

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

answered May 23 '12 at 08:34

Krishanu Dey

6,326
7
51
69

This is not it. VB is not as fussy as c#, this will compile and run without the parentheses. – GarethD May 23 '12 at 08:40
As far as I know It also requires in vb. Please visit http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx#Y853 – Krishanu Dey May 23 '12 at 08:44
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. – GarethD May 23 '12 at 09:08

score 1 · Accepted Answer · answered May 23 '12 at 08:54

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

answered May 23 '12 at 08:54

GarethD

68,045
10
83
123

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? – user4951 May 23 '12 at 10:44
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. – user4951 May 23 '12 at 10:45
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. http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.80%29.aspx – GarethD May 23 '12 at 10:55
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. – GarethD May 23 '12 at 10:58
I still think it's harsh. Hiks hiks. Now that I've updated it, do they change their mind. So mean.... So mean.... – user4951 May 23 '12 at 10:59
@GarethD As far as I know using multiple connections do put an overhead to the application because opening a connection is quite expensive. If pooling is available it's cheaper but still more expensive than just reusing the same connection. – Nicholas May 23 '12 at 11:55
@Nicholas An SqlConnection object is not the same as a phyical connection to the database, so just because there are 2 objects, does not mean 2 physical connections. In this example ADO.NET opens 1 connection to the database when the first connection is opened, then when the second is opened the same connection is resused (because the connection string is the same), so the additional overhead of opening the connection is not actually encountered. The link I provided before explains it quite nicely. – GarethD May 23 '12 at 12:29
@GarethD if you don't close the connection before creating a new one then you'll get a new connection from the pool. so this is not the same connection. at one point when you open lots of connections in a short amount of time, you'll run out of connections - blocking every application from accessing the database. (tested this once). if you're staying in the same method using the same connection object should still have better performance than getting two different connections from the connection pool. – Nicholas May 23 '12 at 14:04
I see what you are saying, however if you don't close the connection you can't create the second data reader, so it is a given that the first one is closed before the second is opened. Test results [here](http://stackoverflow.com/questions/5981376) show the difference is negligable between one object and many objects. In addition, if [Jon Skeet](http://stackoverflow.com/questions/1058591/#answer-1058601) says I should be using multiple SQL connection objects then that is good enough for me! – GarethD May 23 '12 at 15:29

Ankit · Answer 3 · 2012-05-23T11:50:27.337

1

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

edited May 23 '12 at 11:50

answered May 23 '12 at 10:11

Ankit

680
4
17

Correct. Garetd answer is more complete. – user4951 May 23 '12 at 10:46

Why do I got error when I create 2 commands on SQL in vb.net

3 Answers3