Connection property has not been initialized when filling a DataSet

So here is my issue:

Currently I am trying to make a report that will show 1st and 2nd shifts, by multiple days...

So if they select the range 6/02 - 6/04, I am running a query 3 times... once for 6/02, 6/03, and 6/04... Also they can select shift, so it would be those dates, but 4:30AM-4:30PM for 1st shift....

Currently I have a error, when trying to put my queries/calls inside the for loop... I calculate the difference of the two dates and set them up fine, its just my connection string gives me the error:

enter image description here

If the image is not easy to see here is a text description of the error:

Server Error in '/mfgx_test' Application.

Fill: SelectCommand.Connection property has not been initialized.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.

Source Error:

Line 623: Dim dsTop1 As New DataSet Line 624: Dim daTop1 As New SqlDataAdapter(strSql, myCn1) Line 625:
daTop1.Fill(dsTop1) Line 626: Line 627: myCn1.Close()

Source File: C:\inetpub\wwwroot\mfgx_test\defectsbyround.aspx.vb
Line: 625

Which leads me to beleive that something is wrong with my connection string being outside of my for loop... My code is as follows (Although a bit cleaned up so it is easier to read):

    Dim myCn1 As New SqlConnection
    myCn1.ConnectionString = "server=Blah;database=Blah;user id=Blah;password=Blah"
    myCn1.Open()

    For i = 0 To Session("DaysDiff")
        strSql = "Blah.Blah"
        Dim dsTop1 As New DataSet
        Dim daTop1 As New SqlDataAdapter(strSql, myCn1)
        daTop1.Fill(dsTop1)

        myCn1.Close()
        myCn1 = Nothing

        If dsTop1.Tables(0).Rows.Count > 0 Then
           spitout2(dsTop1)
        End If

           txtStartdate.Text = DateAdd("d",1,txtStartdate.Text)
           txtEnddate.Text = DateAdd("d",1,txtEnddate.Text)
   Next

Solution

That's because you are closing the connection inside your loop and so for next iteration there is no open connection present and hence the exception (see pointed below)

   myCn1.Close()
    myCn1 = Nothing

You should declare the Dataset and tableadapter as well out side the loop context. Your code should somewhat look like below

Dim myCn1 As New SqlConnection
myCn1.ConnectionString = "server=Blah;database=Blah;user id=Blah;  password=Blah"
myCn1.Open()
Dim dsTop1 As New DataSet
Dim daTop1

For i = 0 To Session("DaysDiff")
    strSql = "Blah.Blah"
    daTop1 As New SqlDataAdapter(strSql, myCn1)
    daTop1.Fill(dsTop1)

 .......
Next

     myCn1.Close()
    myCn1 = Nothing