Hi,
i have an outer loop using Datareader to get PeriodEndingDate.
After reading each record, i need to run a stored Procedure based on the data i got it.
I use the same connection for this.
When it reaches the Stored Procedure, it is giving error
There is already an open DataReader associated with this Connection which must be closed first.
How can i fix it?Do i really need to make another connection?
Thanks in advance
Jenni.


objConn.Open() ‘SqlConnection
        sql = "Select PeriodEndingDate from PayPeriods where (PeriodEndingDate >= DATEADD(day, - 90,'2/5/2006' )) "

        cmdSelect = New SqlCommand(sql, objConn)
        dtrPayroll = cmdSelect.ExecuteReader

        While dtrPayroll.Read
                  PeriodEndingDate = dtrPayroll("PeriodEndingDate")
                StartWorkDate = DateValue(DateAdd(DateInterval.Day, -6, CType(PeriodEndingDate, Date)))
                EndWorkDate = DateValue(DateAdd(DateInterval.DayOfYear, 0, CType(PeriodEndingDate, Date)))

                'Execute Stored Procedure
                cmdPostTime = New SqlCommand("Sp_CalcPayPeriod", objConn)
                cmdPostTime.CommandType = CommandType.StoredProcedure
                'cmdPostTime.CommandTimeout = 180 ' 3 mints
                cmdPostTime.Parameters.Add("@FromDate", StartWorkDate)
                cmdPostTime.Parameters.Add("@ToDate", EndWorkDate)
                cmdPostTime.ExecuteNonQuery()


        End While
        dtrPayroll.Close()
        objConn.Close()

Hi,
    Unfortunately if you open a DataReader in a connection ADO.NET won't allow you to use the same connection for executing any other command or SP.

There are two solutions here
1) Use a different connection object to call SP
2) Use DataTable instead of DataReader to loop through the object and call the SP using the connection object.

It is not how much we do,
but how much love we put in the doing.

-Mother Theresa