Using multiple datareader in VB.NET 2005 or C#.NET 2005 with MySQL 5.0 as back end

Question

Hi guys,

       I am using MySQL 5.0 as back end. I am using MySqlData.dll in my project. When i am trying to execute the following code,

Try Dim cnData As New MySql.Data.MySqlClient.MySqlConnection Dim lcmd, lcmd1 As New MySql.Data.MySqlClient.MySqlCommand Dim ldr, ldr1 As MySql.Data.MySqlClient.MySqlDataReader

        cnData.ConnectionString = "SERVER=localhost;Uid=root;Pwd=root;Database=mytestdatabase"
        cnData.Open()
        lcmd = New MySql.Data.MySqlClient.MySqlCommand("select uom_id,uom_name from uom", cnData)
        ldr = lcmd.ExecuteReader
        While ldr.Read
            lcmd1 = New MySql.Data.MySqlClient.MySqlCommand("select uom_id,uom_name from uom1 where uom_id=" & ldr.Item("uom_id") & "", cnData)
            ldr1 = lcmd1.ExecuteReader
            While ldr1.Read
                MsgBox(ldr1.Item("uom_id"))
                MsgBox(ldr1.Item("uom_name"))
            End While
            ldr1.Close()
        End While
        ldr.Close()
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

I am getting error message as "There is already an OpenDataReader associated with this Connection which must be closed first." I have to use overlapped multiple datareader .Where i went wrong? Can anyone help me please. It will very help full to me.

Sivakumar.P

Answer 1

This code is the equivalent of what you are trying to do:

Try
    Dim cnData As New MySql.Data.MySqlClient.MySqlConnection
    Dim lcmd As MySql.Data.MySqlClient.MySqlCommand
    Dim ldr As MySql.Data.MySqlClient.MySqlDataReader

    cnData.ConnectionString = "SERVER=localhost;Uid=root;Pwd=root;Database=mytestdatabase"
    cnData.Open()
    lcmd = New MySql.Data.MySqlClient.MySqlCommand("select uom1.uom_id, uom1.uom_name from uom join uom1 ON uom.uom_id = uom1.uom_id ORDER BY uom_id", cnData)
    ldr = lcmd.ExecuteReader
    While ldr.Read
        MsgBox(ldr.Item("uom_id"))
        MsgBox(ldr.Item("uom_name"))
    End While
    ldr.Close()
    cnData.Close()

Catch ex As Exception
    MsgBox(ex.Message)
End Try