Data Reader Problem

Hi All,

I got an error while running this code.There is already an open DataReader associated with this Command which must be closed first

How can I resolve the error?

protected void Page_Load(object sender, EventArgs e)
{
string sql;

SqlConnection Connection = new SqlConnection("ConnectionString");
sql = "SELECT PO_SE_Line_ID FROM PO_STOCK_QUERY_LINE_DETAILS WHERE TRANS_NUM ='TR-A-00-01-93'";

SqlCommand command = new SqlCommand(sql, Connection);
SqlDataReader Dr;
Connection.Open();
Dr = command.ExecuteReader();


while (Dr.Read())
{

sql = "SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93' and PO_SE_Line_ID=" + Dr["PO_SE_Line_ID"].ToString();

SqlCommand command1 = new SqlCommand(sql, Connection);

SqlDataReader Dr1;

Dr1 = command1.ExecuteReader();

while(Dr1.Read())
{

Response.Write(Dr["SUPPLIER_ITEM_CODE"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_MAN_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_PAT_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ITEM_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ADDIT_DESC"].ToString());

}
Dr1.Close();

}
Dr.Close();

I tried to close the first data reader before opening the second data reader.still the error persists.

I suggest that you open a second data connection.

|||

Hi,

If I open a second connection it is showing the error there is already an open connection......

When I tried to close and open the connection Iam getting the error as Invalid attempt to read data when Datareader is closed.

|||

Hi

I guess that your facing problem because your using same sql string for both the command.

the first command already uses the string and you are trying to change that script while datareading, so obviously it gives error

so my suggestion is to use another string named like sql1, so your problem will be solved out.

I hope this will helpful to u...

|||

It's better create a stored procedure and return the results once. In your design, the performance could be a problem.

|||

Howdy!

I generally think nested queries within queries in the code is bad form (like running a query than gens a value to use in another recursion of queries).

I noticed that your first query runs against PO_STOCK_QUERY_LINE_DETAILS tables with a TRANS_NUM filter, then your 2nd query uses a field value from the first to run against the same table. I'm not sure what the design goal was, but it looks like you could simply run the query one time?

In other words, you're running a query againt a table to get a list of values to run against the same table, just run this query with a single data reader:

SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93'

(If you wind up needing for example, a master/detail view, you'd be better off using a join or creating a relationship object in code after returning 2 tables via a dataset).

DT

Data Not Copying to MS Access Table

Hello,

I have a Data Flow Source that uses a SQL Command to pull data. In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.

I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults.

I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.

How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2

DECLARE @.CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)

--and fill it

INSERT INTO @.CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging

--Assign an ID to each block of data for each occurance of 'Reason:'

DECLARE @.ID int
SET @.ID = 1
UPDATE @.CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @.ID - 1 ELSE @.ID END,
@.ID = CASE WHEN ColCategory = 'Reason:' THEN @.ID + 1 ELSE @.ID END

--Then put the data together

SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @.CategoryTable a
LEFT JOIN @.CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @.CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @.CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @.CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @.CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @.CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @.CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @.CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @.CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @.CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'

WHERE a.ColCategory = 'Order ID:'

Are you doing this on an x64 machine. Is it possible that your package is executed in 64-bit mode? There is no 64-bit version of the JET provider.

There have been many posts about this before. Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

|||

Hi Cdun,

When you preview the Source, do you find any rows existing for the query? If yes, When you execute the dataflow task can you see any rows in Data grid viewer?

In fact if there are rows, it must be transfered to the destination table.

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

When you execute the dataflow task can you see any rows in Data grid viewer?

There are rows in the preview, but no rows in the data grid viewer. I'll check into the 64 bit setting.

|||

Bob Bojanic - MSFT wrote:

Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

I went to the package properties, debugging, and set Run64BitRuntime to False. I still get the same result The data will preview, but will not transfer into the Access table.

In the Data Flow Task, I'm using an OLEDB source to execute the sql statement. Should I be using an Execute SQL Task instead? The sql statement also uses a TABLE variable. Could this be a problem?

|||There must be something wrong with the way I'm trying to deliver the data to the Access table, because I can't get data to a sql server table destination either.|||

Never mind on this. I resorted to creating a table UDF as the source, and it looks like that will work.

cdun2

Data lost after INSERT command

I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.Are you sure that when User B selects dates that are in the set selected by User A you are not using an Update statement instead of an Insert statement?|||It would be very helpful if you would post the queries involved, as well as the table DDL.|||Thank you for taking the time to respond. I have a bit of code where I do a DELETE prior to doing the INSERT. Your question led me to examine my DELETE statement again, only to discover that I had made it too broad so that it was deleting all records having a particular date, not just the one of the user currently logged in. I will adopt the suggestion to include code in future postings. Thanks again.