Retrieve table data and save that data into same table in C#

Question

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 19-Mar-20 1:21am

Member 11952997

Updated 19-Mar-20 15:18pm

Add a Solution

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.

2 solutions

Add a Solution

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

phil.o · Answer 1 · 2020-03-19T15:19:00

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.

C#

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();
      }
   }
}

OriginalGriff · Answer 2 · 2020-03-19T02:05:00

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.

Retrieve table data and save that data into same table in C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Retrieve table data and save that data into same table in C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us