There is already an open DataReader associated with this Connection which must be closed first, AGAI

[ItsMe]

Hi,

I know, i'm repeating this question again. Please accept my apologize,
unable to understand clearly. Need to have some inputs in my routine. I'VE A
DEADLINE AND I'M STUCK !!!!!

I'm retrieving Data from Paradox database using (odbc), manipulating and
saving in access for report generation.

I'm trying to put what i'm doing simple pseudocode:
==================================

- Write SQL Statement and Retrieve Data from Paradox Tables using
DataAdapterParadox (OdbcConnection)
- Loop through the records till end-of-file
- Manipulate Data
- Writing SQL Statement to get some values from another table of
same paradox database
- Insert retrieved values from two sql statement into Access Table
- Close connection

My vb.net code:
===========
sSQL = "SELECT C.BRANDCODE, SUM(B.QTY) AS QTY" & _
" FROM SALESINFO A, DOMODELINFO B, PRODUCTINFO C" & _
" WHERE A.INVNO = B.INVNO AND A.YEAR = " &
CInt(cmbTotalSalesYear.Text) & _
" AND B.MODEL = C.MODEL" & _
" GROUP BY C.BRANDCODE ORDER BY C.BRANDCODE"
oDataReaderPdx = mDBReaderPdx(sSQL)
With oDataReaderPdx
If .HasRows Then
While .Read

' Here i want to write another sql statement, match
brandcode with another tables and get the total amount for that.
' If I use another datareader, it says "There is
already an open DataReader associated with this Connection which must be
closed first"
' How do i solve this issue ?

Dim iTotalJan As Int32
sSQL = "INSERT INTO tblYearlyReport (ReportType,
ComputerName, " & _
" FirstField, Qty)" & _
" VALUES (" & mQuotedStr("TOTALSALES") & ","
& _
mQuotedStr(mGetComputerName()) & "," & _
mQuotedStr(.GetValue(0)) & "," & _
.GetValue(1) & ")"
mExecCmdAccess(sSQL)
End While
End If
.Close()
End With


I hope you guyz understood my problem. Please Help !!!!

Thanks in Advance.

 

[William Ryan eMVP]

Either close the first connection or, if you must keep it open b/c you are
firing another reader from within it and you can't use another data
structure, use a second connection.

 

[Miha Markic [MVP C#]]

Or wait for Whidbey ;-)

 

[William Ryan eMVP]

Amen to that.

ADO.NET 2.0 is almost like relearning ADO.NET all over again, but wow is it
ever cool and learning it has proven to be quite a bit of fun.

Or wait for Whidbey ;-)

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Either close the first connection or, if you must keep it open b/c you are
firing another reader from within it and you can't use another data
structure, use a second connection.

 

[TheGuru]

try this...

Dim objComm As New SqlCommand("SP_MySPName", MyConnection)
objComm.CommandTimeout = 600
objComm.CommandType = CommandType.StoredProcedure
objComm.Connection.Open()
for i = 0 to 10

' create & pass parametere to SP
Dim myPara As New SqlParameter
mypara.value = i ' new value each time
objComm.Parameters.Add(myPara)

' execute
Dim myReader As SqlDataReader = objComm.ExecuteReader

'read output parameter or data
outputValue = objComm.Parameters.Item("@outputValue").Value

objComm.Parameters.Clear()

'close reader
myReader.Close()

next