Click here to Skip to main content
15,890,374 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want read data from 2 different table trxdat and trxitem

but at 2nd read I am getting error

System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'


command and string are all different

how do I operate with both the reader because at each row of trxdat , i have to read related data from tritem

What I have tried:

string str1 = "select * from dbo.trxdat where cmp_code = '" + SPGlobal.spcmp_code + "' and trx_type = '" + RMST + "'";
SqlCommand mcmd = new SqlCommand(str1, SPDConn);
SqlDataReader mrdr = mcmd.ExecuteReader();
DataTable dt = new DataTable();

string str2 = "select * from dbo.tritem where cmp_code = '" + SPGlobal.spcmp_code + "' and trx_type = '" + RMST + "'and trx_no = '" + trx_no + "'";
SqlCommand icmd = new SqlCommand(str2, SPDConn);
SqlDataReader irdr = icmd.ExecuteReader();
Posted
Updated 12-May-20 20:03pm
Comments
Richard MacCutchan 12-May-20 15:33pm    
You need to close the duplicate reader.
Richard Deeming 12-May-20 15:55pm    
You have a much bigger problem than that. Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
j snooze 12-May-20 17:48pm    
along with what Richard says about using parameterized queries. I see you create a datatable and do nothing with it. Why not fill the datatable after getting the first reader, then close the first reader and get your second reader data?

The message means exact what it says.

Once you have a DataReader running on a connection object, that connection cannot do anything else, like another query, until the DataReader is closed.

You're going to have to redesign your code. Nobody can tell you how to do that without guessing because you haven't said what you're trying to do and the code you have posts is a bit confused, not giving any real direction either.
 
Share this answer
 
As previously stated...
1. Your code is vulnerable to SQL Injection.
2. Your error is caused by attempting to have 2 reader open simultaneously.
3. You are declaring a DataTable but never using it.
Also noticed...
4. Your queries are nearly identical

I'm gonna guess you would you like this to fill a pair of DataTables from these near identical queries.

First thing I am going to do is to declare two datatables to store the reader results.
C#
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
Next I will declare 2 strings for the queries. Variables will be replaced with Parameters
C#
string qry1 = "SELECT * FROM dbo.trxdat WHERE cmp_code = @CmpCode AND trx_type = @TrxType";
string qry2 = "SELECT * FROM dbo.tritem WHERE cmp_code = @CmpCode AND trx_type = @TrxType AND trx_no = @TrxNo";
Next we are going to declare the ONE Sql Command object that is needed for this. This will be wrapped within a Using block which will dispose and clean it up when we are finished. Once that command is created, we can add the parameters to it
C#
using (SqlCommand cmd = new SqlCommand(qry1, SPDConn)) {
	cmd.Parameters.AddWithValue("@CmpCode", SPGlobal.spcmp_code);
	cmd.Parameters.AddWithValue("@TrxType", RMST);
Now we'll create a DataReader to execute the command and fill one of the DataTables. This will also be wrapped within a USING block so that it is closed and disposed of as soon as we are done with it
C#
  using (SqlDataReader sdr = cmd.ExecuteReader()) {
      dt1.Load(sdr);
}
At this point, sdr no longer exists, so the connection is once again available for use.
Our SqlCommand object is still a viable object, and as it is no longer in use; we can simply change the properties we need to.
The command text will need to be changed
The parameters are part of a collection, and because the ones that are populating are needed for the second query; all we need to do is to add the last one.
C#
cmd.CommandText = qry2;
cmd.Parameters.AddWithValue("@TrxNo", trx_no);
And then we'll repeat the process- use an SqlDataReader within a Using block to fill the second data table.
The last closing bracket is for the Using block that was created for the Command object, and this will dispose of the CMD object properly
C#
	using (SqlDataReader sdr = cmd.ExecuteReader()) {
		dt2.Load(sdr);
	}
}
 
Share this answer
 
Okay
instead of reader I should use insert with parameter
and set primary key for table dt1
I was trying to use reader because I was having few calculations and data manipulations

Thanks for logic
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900