Click here to Skip to main content
14,300,842 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a routine that checks for rows with NULL values, for a specified column. For each instance of a NULL value found i would like to insert values in the same using some criteria. Im getting a "There is already an open DataReader associated with this Command which must be closed first" exception. This is thrown when control flow has already surpassed the first INSERT condition.. but however when i check the table nothing has been committed. I was expecting to see data for the first condition despite of the exception. I need help on 1) if im doing the NULL check as well as insert correctly. For now i can observe that nothing is being committed. 2) I have checked and still cant see any datareader i have left unclosed.
My code:
using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
           {
               SqlCommand cmd = new SqlCommand("SELECT DISTINCT transdetail_id FROM Tdata WHERE period_name='" + (reportingPeriod) + "' AND agent_shortname IS NULL", conn);

               conn.Open();
               SqlDataReader dr = cmd.ExecuteReader();
               while (dr.Read())
               {

                   Models.FileUpload.Mapping mapping = new Models.FileUpload.Mapping();

                   mapping.TransactionDetailId = Convert.ToString(dr["transdetail_id"]);

                   using (SqlConnection conn1 = new SqlConnection(Helpers.DatabaseConnect))
                   {

                       SqlCommand cmd1 = new SqlCommand("SELECT company_code FROM TData WHERE transdetail_id='" + Convert.ToString(mapping.TransactionDetailId) + "'", conn1);

                       conn1.Open();
                       SqlDataReader dr1 = cmd1.ExecuteReader();

                        if (dr1.Read() == true)
                        {

                           mapping.companycode = Convert.ToString(dr1["company_code"]);

                       if (mapping.companycode == Harare)
                       {
                           using (SqlConnection connp = new SqlConnection(Helpers.DatabaseConnect))
                           {

                               cmd.CommandText = @"INSERT INTO TData(agent_shortname) VALUES(@agentanalysiscode) WHERE transdetail_id='" + Convert.ToString(mapping.TransactionDetailId) + "'";
                               cmd.Parameters.AddWithValue("@agentanalysiscode", HreCode);
                               try
                               {
                                   connp.Open();
                                   cmd.ExecuteNonQuery();
                               }
                               catch (SqlException e)
                               {
                                   ViewBag.ErrorMessage = "Processing error !";
                                   return View();
                               }
                               connp.Close();
                           }
                       }

                      //else if (mapping.companycode == Bulawayo)
                   //insert different value based on company_code i.e Bulawayo
                       }

                       conn1.Close();
                   }

               }
              conn.Close();

           }


What I have tried:

When i do run the SQL query
SELECT        transdetail_id
FROM            TData
WHERE        (agent_shortname IS NULL)
in SSMS i am able to get transdetail_id for the NULL columns:

Just for clarity my columns are all nvarchar(MAX). Any help on what i might be missing.
Posted
Updated 11-Sep-19 7:36am
v3
Comments
Richard Deeming 5 days ago
   
"SELECT DISTINCT transdetail_id FROM Tdata WHERE period_name='" + (reportingPeriod) + "' AND agent_shortname IS NULL"


Don't do it like 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[^]
Rate this:
Please Sign up or sign in to vote.

Solution 1

I think you need to change your INSERT command to an UPDATE command.
Example here: SQL UPDATE Statement | With multiple columns[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

You have a lot of issues with your code:

1. Why do you keep recreating the same SqlConnection object? Just use the same one over and over again. You are just wasting memory and adding load to your SQL Server.

2. You are redefining the CommandText of the cmd variable when cmd is open for reading and you are currently indexing through it in your while statement. You should just create a new variable for that third SQL line. (That is where your error is coming from)

3. Your insert statement has an invalid syntax. Did you mean to use an UPDATE statement?

Hopefully fixing these will set you on the right track.

Brent
   
Comments
Member 12928655 12-Sep-19 4:52am
   
Thanks guys.. On changing to an UPDATE statement i am still unable to insert into the columns with NULL values..What am i missing,
using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
{
Models.FileUpload.Mapping mapping = new Models.FileUpload.Mapping();

SqlCommand cmd = new SqlCommand("SELECT DISTINCT transdetail_id FROM TData WHERE period_name='" + Convert.ToString(reportingPeriod) + "' AND agent_shortname IS NULL", conn);

conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
mapping.TransactionDetailId = Convert.ToString(dr["transdetail_id"]);
}

else if (dr.Read() == false)
{
mapping.TransactionDetailId = string.Empty;
}
conn.Close();

SqlCommand cmd1 = new SqlCommand("SELECT company_code FROM TData WHERE transdetail_id='" + Convert.ToString(mapping.TransactionDetailId) + "'", conn);
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read() == true)
{

mapping.companycode = Convert.ToString(dr1["company_code"]);
}
else if (dr.Read() == false)
{
mapping.companycode = string.Empty;
}

conn.Close();
}
using (SqlConnection conn1 = new SqlConnection(Helpers.DatabaseConnect))
{
Models.FileUpload.Mapping mapping = new Models.FileUpload.Mapping();

if (mapping.companycode == Harare)
{
String query = "UPDATE TData SET agent_shortname =@agentanalysiscode WHERE company_code=@companycode";
SqlCommand cmd3 = new SqlCommand(query, conn1);
cmd3.Parameters.AddWithValue("@company_code", Convert.ToString(mapping.companycode));
cmd3.Parameters.AddWithValue("@agentanalysiscode", HreCode);
try
{
conn1.Open();
cmd3.ExecuteNonQuery();
}
catch (SqlException e)
{
ViewBag.ErrorMessage = "Processing error !";
return View();
}
conn1.Close();
}

else if (mapping.companycode == Bulawayo)
{

String query = "UPDATE TData SET agent_shortname =@agentanalysiscode WHERE company_code=@companycode";
SqlCommand cmd3 = new SqlCommand(query, conn1);
cmd3.Parameters.AddWithValue("@company_code", Convert.ToString(mapping.companycode));
cmd3.Parameters.AddWithValue("@agentanalysiscode", ByoCode);
try
{
conn1.Open();
cmd3.ExecuteNonQuery();
}
catch (SqlException e)
{
ViewBag.ErrorMessage = "Processing error !";
return View();
}
conn1.Close();
}


}
dbrenth 12-Sep-19 8:02am
   
If this is an exact copy paste then you need to fix your parameter names:

String query = "UPDATE TData SET agent_shortname =@agentanalysiscode WHERE company_code=@companycode";
SqlCommand cmd3 = new SqlCommand(query, conn1);
cmd3.Parameters.AddWithValue("@company_code", Convert.ToString(mapping.companycode));

In the first line it is @companycode and in the last line it is @company_code
In both places in your code. These need to match.
dbrenth 12-Sep-19 8:04am
   
Also don't keep closing and opening the connection. At the end of the using statement the connection will be closed automatically. It can remain open throughout the using statement.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100