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.
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
Next I will declare 2 strings for the queries. Variables will be replaced with
Parameters
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
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
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.
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
using (SqlDataReader sdr = cmd.ExecuteReader()) {
dt2.Load(sdr);
}
}