dataset filling from SQL - too cryptic!

For the love of Pete, can someone just list for me the correct method for filling a dataset from an SQL database

I get this error:
Fill: SelectCommand.Connection property has not been initialized.


    Public Function GetDataSet(ByVal sTable As String, _
                           Optional ByVal sColumn As String = "*", _
                           Optional ByVal sFiltCol As String = Nothing, _
                           Optional ByVal sFiltVal As String = Nothing, _
                           Optional ByVal bFiltOut As Boolean = False, _
                           Optional ByVal sSortCol As String = Nothing, _
                           Optional ByVal sSortDir As String = Nothing) _
                     As DataSet
        'takes SQL parameters
        'returns dataset

        Dim dbConn As New SqlConnection(dbConnectStr)
        Dim adapter As New SqlDataAdapter
        Dim ds As DataSet
        Dim qu As New SQLQuery

        adapter.TableMappings.Add("Table", sTable)
        dbConn.Open()
        With qu
            .setTable = sTable
            .setReturnColumn = sColumn
            .setOrder(sSortCol, sSortDir)
        End With
        Dim cmd As SqlCommand = New SqlCommand(qu.Assembled)
        cmd.CommandType() = CommandType.Text
        adapter.SelectCommand = cmd
        ds = New DataSet(sTable)
        adapter.Fill(ds)
        dbConn.Close()

        Return ds

    End Function


where:
- dbConnectStr is a valid database connection string
- qu.Assembled returns a valid SQL query string




Answer this question

dataset filling from SQL - too cryptic!

  • PiotrP

    Oh never mind, I just scrapped the whole thing (that example was from MSDN) and started again with a different example:

        Public Function GetDataSet(ByVal sTable As String, _
                               Optional ByVal sColumn As String = "*", _
                               Optional ByVal sFiltCol As String = Nothing, _
                               Optional ByVal sFiltVal As String = Nothing, _
                               Optional ByVal bFiltOut As Boolean = False, _
                               Optional ByVal sSortCol As String = Nothing, _
                               Optional ByVal sSortDir As String = Nothing) _
                         As DataSet
            'takes SQL parameters
            'returns dataset

            Dim dbConn As SqlConnection = New SqlConnection(dbConnectStr)
            Dim adapter As SqlDataAdapter = New SqlDataAdapter
            Dim ds As DataSet = New DataSet
            Dim qu As New SQLQuery

            With qu
                .setTable = sTable
                .setReturnColumn = sColumn
                .setOrder(sSortCol, sSortDir)
            End With
            Dim selectCMD As SqlCommand = New SqlCommand(qu.Assembled, dbConn)


            selectCMD.CommandTimeout = 30
            adapter.SelectCommand = selectCMD
            dbConn.Open()
            adapter.Fill(ds, sTable)
            dbConn.Close()

            Return ds

        End Function



  • dataset filling from SQL - too cryptic!