SelectedCommand property has not been initialized before calling 'Fill'

[dougancil]

I have the following code:

Public Class Payrollfinal
    Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        exportfileButton.Enabled = False
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim _CMD As SqlCommand = New SqlCommand
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        Try
            adapter.Fill(ds)
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_allsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
        Try
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        exportfileButton.Enabled = True
    End Sub

and when I run I'm given the error:

The SelectCommand Property has not been initialized before calling 'Fill'

I was given the following code to correct this problem:

oCmd = cnn.CreateCommand
oCmd.CommandText = "sp_allsum"
adapter.SelectCommand = oCmd 
adapter.Fill(ds)

and intellisense is telling me that I need to declare cnn. Can anyone please advise what cnn may be used for?

Thank you

Doug

[Unhnd_Exception]

It looks like there is a whole bag of issues.

The adapter needs to have its select command set first. Thats your above error. That select command needs to have its connection property set as well. That would be your next error.

This would be the proper way to fill your data set.

This example is to fill your data set. Although I don't quite understand what your doing. You have datasets and readers. Trying to Fill an in memory version of the database and also wanting to use a reader to communicate directly with the database. In either case doing nothing with either one.

Post more and more of a response can be given.

Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load        
        exportfileButton.Enabled = False

        Dim Connection As New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
        Dim oCmd As New System.Data.SqlClient.SqlCommand
        Dim adapter As New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet

        oCmd.Connection = Connection
        oCmd.CommandText = "sp_allsum"
        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        oCmd.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)

        adapter.SelectCommand = oCmd

        Try
            Connection.Open()

            adapter.Fill(ds)

            Connection.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            Connection.Dispose()
            oCmd.Dispose()
            adapter.Dispose()
            ds.Dispose()
        End Try

        exportfileButton.Enabled = True
    End Sub

[dougancil]

Unhnd,

here's my code for this form:

Imports System.Data.SqlClient
Imports System.IO
Public Class Payrollfinal
    Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        exportfileButton.Enabled = False
        Dim oCmd As New System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        Dim _CMD As New SqlCommand
        Dim cnn As New SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        oCmd = cnn.CreateCommand
        oCmd.CommandText = "sp_allsum"
        adapter.SelectCommand = oCmd
        adapter.Fill(ds)
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_allsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Try
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        exportfileButton.Enabled = True
    End Sub
    Private Sub payrollsubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollsubmitButton.Click
        exportfileButton.Enabled = False
        exportfileButton.Enabled = True
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        connection = New SqlConnection(connectionString)
        connection.Open()
        connection.Close()
    End Sub
    Private Sub exportfileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportfileButton.Click
        Dim bar As New clsDGVToASCII
        bar.DGVTOASCII(payrolldgv, "c:\Temp\", "payroll.txt", ",")
    End Sub
End Class

First let me say that the code under the payrollSubmitButton_Click event is not finished. What I'm trying to do with this code is that I have two other forms that calculate the payroll for a time period with a series of stored procedures. The SP that I execute here (sp_allsum) is supposed to sum produce results, present them in a window, and then allow the user to submit that to a database called Payroll and also to export it as a csv file. I hope that makes things clearer.