[RESOLVED] Function that retrives a datatable from a sql statement VS2008 and MySql

[KenBZim]

Hi All

i need a bit of help with the code in this function.
basically what the function does is return a datatable which is a result of
the Sql query that is sent to the function
Although it works, I dont like the 'If Not Reader.IsClosed Then Reader.Close()
the only reason that it is there is that if i take it out then the function executes
fine the first time but thereafter i get the error:
'There is already an open datareader associated with this connection'

I am sure there must be a better way of writing this code.
Any help would be greatly appreciated.

Public Function GetResultSet(ByVal SqlQuery As String) As DataTable
'This Function Returns as table of data based
'on the sql query passed into the function

GetResultSet = Nothing
Dim Reader As MySqlDataReader

Try
Dim Table As New DataTable
Dim cmd As New MySqlCommand(SqlQuery, Datacon)
Reader = cmd.ExecuteReader
If Reader.HasRows Then
Table.Load(Reader)
Return Table
End If
Catch DBError As Exception

Msgbox("Error Retrieving Data")


Finally
If Not Reader.IsClosed Then Reader.Close()
End Try

[Niya]

You could use a DataAdapter. Here is an example using it to retrieve data from Sql Server:-

'
        Dim conn As SqlConnection = New SqlConnection(connectionString)
 
        Dim myDataAdapter As New SqlDataAdapter
        Dim cmd As New SqlCommand("Select * From MyTable", conn)
        Dim myDataTable As New DataTable
 
        'Set the select command on the DataAdapter
        myDataAdapter.SelectCommand = cmd
 
        'Fill the DataTable
        myDataAdapter.Fill(myDataTable)

Note that whatever state the connection was in when Fill was called will be restored after Fill is finished. So if the connection was closed before, it will be closed after.

[KenBZim]

Hi Thanks for that, what i need however is a function that returns a datatable that can be used from anywhere in the app, what i am trying along the lines that you have given me is:

Public Function GetResultSet(ByVal SqlQuery As String) As DataTable
Dim myDataAdapter As New MySqlDataAdapter
Dim cmd As New MySqlCommand(SqlQuery, Datacon)
Dim myDataTable As New DataTable

'Set the select command on the DataAdapter
myDataAdapter.SelectCommand = cmd

'Fill the DataTable
myDataAdapter.Fill(myDataTable)
End Function

How do i get the function to return the datatable?

[Niya]

Return myDataTable

[KenBZim]

Hi Niya

Many thanks

[techgnome]

you're already doing it... and doing it properly... execute a reader, use it to load the datatable, close the reader...

I would make some changes though...

Public Function GetResultSet(ByVal SqlQuery As String) As DataTable
'This Function Returns as table of data based 
'on the sql query passed into the function

Dim Table As DataTable = nothing
Dim Reader As MySqlDataReader

Try
    Dim cmd As New MySqlCommand(SqlQuery, Datacon)
    Reader = cmd.ExecuteReader
    If Reader.HasRows Then
        Table = new DataTable
        Table.Load(Reader)
    End If
Catch DBError As Exception
    MessageBox.Show("Error Retrieving Data")
End Try
If Not Reader.IsClosed Then Reader.Close()
return Table

-tg