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