VS 2008 [RESOLVED] Function that retrives a datatable from a sql statement VS2008 and MySql-VBForums
Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2005
    Location
    Bulawayo, Zimbabwe
    Posts
    576

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

    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

  2. #2
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    5,501

    Re: Function that retrives a datatable from a sql statement VS2008 and MySql

    You could use a DataAdapter. Here is an example using it to retrieve data from Sql Server:-
    vbnet Code:
    1. '
    2.         Dim conn As SqlConnection = New SqlConnection(connectionString)
    3.  
    4.         Dim myDataAdapter As New SqlDataAdapter
    5.         Dim cmd As New SqlCommand("Select * From MyTable", conn)
    6.         Dim myDataTable As New DataTable
    7.  
    8.         'Set the select command on the DataAdapter
    9.         myDataAdapter.SelectCommand = cmd
    10.  
    11.         'Fill the DataTable
    12.         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.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena


    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. -jmcilhinney

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2005
    Location
    Bulawayo, Zimbabwe
    Posts
    576

    Re: Function that retrives a datatable from a sql statement VS2008 and MySql

    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?

  4. #4
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    5,501

    Re: Function that retrives a datatable from a sql statement VS2008 and MySql

    vbnet Code:
    1. Return myDataTable
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena


    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. -jmcilhinney

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2005
    Location
    Bulawayo, Zimbabwe
    Posts
    576

    Re: Function that retrives a datatable from a sql statement VS2008 and MySql

    Hi Niya

    Many thanks

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,268

    Re: [RESOLVED] Function that retrives a datatable from a sql statement VS2008 and MyS

    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...
    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.