Click here to Skip to main content
14,697,191 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello everyone.
I am facing problem.
my problem is below.

first i am developing desktop application in Visual Studio 2010 in c# and back-end tool is sqlserver 2005.

i have table name warehousemaster and fields are (id,CompanyName,WarehouseCode,WarehouseName,wareaddress,city,contactno,FinancialYear).

now i inserted record in sql database is [1,ABC PVT.LTD.,VFFSV1,VFFSV1,ABCaddres ,ABCCity, 000000, 2019-2020]

now as per financial year changing in next month(2020-2021) i dont want to change existing data but i want to insert same data only financial year changing.
what are the possible ways???
i want to retrieve using sql data reader old data and insert that data .
i am tesing both ways using store procedure and query also.
below i mention as per store procedure. and also error message display insert time.
the error message is- " There is already an open DataReader associated with this Command which must be closed first. "

What I have tried:

SqlCommand cmd3 = new SqlCommand("retriveWarehouseNameGrid ",con);
cmd3.CommandType = CommandType.StoredProcedure;
SqlDataReader sdr3 = cmd3.ExecuteReader();
while (sdr3.Read())
{

warecompanyname = sdr3["CompanyName"].ToString();
warehousecode = sdr3["WarehouseCode"].ToString();
warehousename1 = sdr3["WarehouseName"].ToString();
wareaddress = sdr3["wareaddress"].ToString();
warecity = sdr3["city"].ToString();
warecontactno = sdr3["contactno"].ToString();
warefinnacialyear = sdr3["FinancialYear"].ToString();

SqlCommand cmd4 = new SqlCommand("insertWarehouseMaster", con);
cmd4.CommandType = CommandType.StoredProcedure;
cmd4.Parameters.AddWithValue("@WarehouseCode", warehousecode);
cmd4.Parameters.AddWithValue("@WareHouseName ", warehousename1);
cmd4.Parameters.AddWithValue("@CompanyName", warecompanyname);
cmd4.Parameters.AddWithValue("@financialyear", sumyear);
cmd4.Parameters.AddWithValue("@wareaddress", wareaddress);
cmd4.Parameters.AddWithValue("@city", warecity);
cmd4.Parameters.AddWithValue("@contactno", warecontactno);

cmd4.ExecuteNonQuery();
}
sdr3.close();
Posted
Updated 19-Mar-20 16:18pm
Comments
ZurdoDev 19-Mar-20 7:57am
   
The error means you need to either close the reader that is open or open a new reader on a new command.
Member 11952997 19-Mar-20 8:22am
   
thank u sir,
problem is i want to copy table data but save with different financial year.
ZurdoDev 19-Mar-20 8:26am
   
Yes, but that has nothing to do with your code or error.

The problem is that your first query will likely return several rows. You then have to store these values in a list to be able to create the new records for each of them.
A possible way would be to create a type to store the values for a single record, and on first command to store several instances of this type in a list. Then, after having disposed of the reader and first command, you can create the second command, and iterate previously created list to issue update instructions with the new financial year.
public class Ware
{
   public string CompanyName { get; set; }
   public string HouseCode { get; set; }
   public string HouseName { get; set; }
   public string Address { get; set; }
   public string City { get; set; }
   public string ContactNo { get; set; }
}

// ...

List<Ware> wares = new List<Ware>();

using (SqlConnection con = [initialize your connection here])
{
   con.Open();

   using (SqlCommand cmd3 = new SqlCommand("retriveWarehouseNameGrid ",con))
   {
      cmd3.CommandType = CommandType.StoredProcedure;

      using (SqlDataReader sdr3 = cmd3.ExecuteReader())
      {
         while (sdr3.Read())
         {
            Ware ware = new Ware();
            ware.CompanyName = sdr3["CompanyName"].ToString();
            ware.HouseCode = sdr3["WarehouseCode"].ToString();
            ware.HouseName = sdr3["WarehouseName"].ToString();
            ware.Address = sdr3["wareaddress"].ToString();
            ware.City = sdr3["city"].ToString();
            ware.ContactNo = sdr3["contactno"].ToString();
            wares.Add(ware);
         }
      }
   }

   using (SqlCommand cmd4 = new SqlCommand("insertWarehouseMaster", con))
   {
      cmd4.CommandType = CommandType.StoredProcedure;
      cmd4.Parameters.Add("@WarehouseCode", DbType.String);
      cmd4.Parameters.Add("@WareHouseName ", DbType.String);
      cmd4.Parameters.Add("@CompanyName", DbType.String);
      cmd4.Parameters.Add("@financialyear", DbType.String);
      cmd4.Parameters.Add("@wareaddress", DbType.String);
      cmd4.Parameters.Add("@city", DbType.String);
      cmd4.Parameters.Add("@contactno", DbType.String);

      foreach (Ware ware in wares)
      {
         cmd4.Parameters["@WarehouseCode"].Value = ware.HouseCode;
         cmd4.Parameters["@WareHouseName "].Value = ware.HouseName;
         cmd4.Parameters["@CompanyName"].Value = ware.CompanyName;
         cmd4.Parameters["@financialyear"].Value = "2020-2021";
         cmd4.Parameters["@wareaddress"].Value = ware.Address;
         cmd4.Parameters["@city"].Value = ware.City;
         cmd4.Parameters["@contactno"].Value = ware.ContactNo;
         cmd4.ExecuteNonQuery();
      }
   }
}
   
v2
Comments
Member 11952997 20-Mar-20 6:12am
   
thank u sir. i got solution.
While a DataReader is open, the connection is "busy" - because a DataReader does a "round trip" to the DB each time you read a new row.
While it's busy, you can't issue a new command on the same connection - because it could mess up the currently executing one.

There are two ways round this:
1) Use a DataAdapter instead of a DataReader and fill a DataTable with your data. Then you can process the DataTable and issue commands on the existing Connection object.
2) Create a second Connection object to use for your INSERT operations.

It's also a very good idea to use a using block to ensure that your Command and DataReader objects are automatically Disposed when they go out of scope - and the Connection as well.
   
Comments
Member 11952997 19-Mar-20 8:54am
   
thank u sir,
but i can't understand your two ways properly.
could u send me deep information or relative example?
OriginalGriff 19-Mar-20 8:57am
   
You are joking, right?
You don't know how to create an SqlConnection? How did you write the code you showed us?
Member 11952997 20-Mar-20 6:14am
   
sorry,i m not joking, i know little. but i got solution sir.
thank u.

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