vb net SQl query problem

Question

im trying to retrieve some data using a reader in vb.net. I dont have any issue retrieving certain columns of the data for each row but i want to retrieve all data in each row. I've tried a couple of different things with the getstring() command but it isnt working and i cant seem to find any help googling the issue. my code is this

Private Function QueryDown(ByVal queryString)
    Dim returnInfo As New StringBuilder
    Try
        Dim newQuery As String() = Split(queryString, ":")
        For Each Query In newQuery
            Dim cmd As New MySqlCommand(Query, connection1)
            Dim reader As MySqlDataReader
            reader = cmd.ExecuteReader()

            While reader.Read()
                For a = 0 To reader.FieldCount
                    Dim strng As String = reader.GetString(a)
                    returnInfo.Append(strng & ",")
                Next

                returnInfo.Append(";")
            End While
            reader.Close()
        Next
    Catch ex As Exception
        console("Error with MySQL: " & ex.Message)
        Return ex.Message
    End Try
    Return returnInfo.ToString
End Function

sorry the error i get when using this code is

There is already an open DataReader associated with this Connection which must be closed first

but if i change getstring(a) to getstring(1) everything is fine, im confused. any help here would be great, i want to formatted code to come back column,column,coloumn;nextrow, as you can see (i hope). Because each of my table has a different amount of coloumns and i want to be able to use the same function for each one. thanks again.

manji · Accepted Answer · 2011-04-11 13:04:29Z

2

Upper limit is reader.FieldCount - 1 not reader.FieldCount in:

For a = 0 To reader.FieldCount - 1

when a reaches reader.FieldCount there is an exception => reader.Close() is not executed => I suppose you call this function (or another) to open a new reader with the same connection => error.

When you call getstring(1) its working because 1 is within [0, FieldCount-1]

Update:

As @Zach Green said, try to always use using when ever possible which is a replacement for try...finally{ .Dispose() }: the dispose in finallyis applied to object beeing "used" and calling for DataReader/DataConnection it will call .Close() for you.

edited Apr 11, 2011 at 13:04

answered Apr 11, 2011 at 12:29

manji

48k5 gold badges98 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tye Over a year ago

you are the man sir, thank you very much. i feel like a retard for not realized that though.....whoops.

Zach Green Over a year ago

You should be executing your queries by utilizing the 'using' statement to make sure your reader is properly closed on exception: weblogs.asp.net/joseguay/archive/2008/07/22/…

manji Over a year ago

don't worry, it happens to the best of us ;)

Collectives™ on Stack Overflow

vb net SQl query problem

1 Answer 1

Update:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related