the programm will take the code field name from TableA and search TableB if it find it then the Values field will be Updated in TableA also if TableA have value then it will add TableA->Values + TableB->Values the code that does it is below :
if (m_Code == colname && mValue > 0) { mValue += Convert.ToInt32(row[column].ToString()); } else { mValue = Convert.ToInt32(row[column].ToString()); }
my challanges is that when it loop through TableB it only return the last value in TableB to TableA . the entire code is bellow :
DataTable dt = GetDatafromDatabase(); //===== returns a DataTable
string SQLT = "SELECT * FROM tbl_TempReport";
string colname;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdT = new SqlCommand(SQLT, cn);
SqlDataReader rt = cmdT.ExecuteReader();
while (rt.Read())
{
// For each row, print the values of each column.
foreach (DataColumn column in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
colname = column.ColumnName.ToString();
int mValue = 0;
string m_Code = rt["code"].ToString();
if (m_Code == colname)
{
if (m_Code == colname && mValue > 0) { mValue += Convert.ToInt32(row[column].ToString()); } else { mValue = Convert.ToInt32(row[column].ToString()); }
//============ insert into tbl_TempReport to match the appropriate column
string SQLP = "UPDATE tbl_TempReport SET VALUEP = @VALUEP WHERE code = @code";
SqlCommand cmdp = new SqlCommand(SQLP, cn);
cmdp.Parameters.AddWithValue("@VALUEP", SqlDbType.Int).Value = mValue;
cmdp.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rt["code"].ToString();
cmdp.ExecuteNonQuery();
}
}
}
}
string SQLT = "SELECT * FROM tbl_TempReport";
string colname;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdT = new SqlCommand(SQLT, cn);
SqlDataReader rt = cmdT.ExecuteReader();
while (rt.Read())
{
// For each row, print the values of each column.
foreach (DataColumn column in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
colname = column.ColumnName.ToString();
int mValue = 0;
string m_Code = rt["code"].ToString();
if (m_Code == colname)
{
if (m_Code == colname && mValue > 0) { mValue += Convert.ToInt32(row[column].ToString()); } else { mValue = Convert.ToInt32(row[column].ToString()); }
//============ insert into tbl_TempReport to match the appropriate column
string SQLP = "UPDATE tbl_TempReport SET VALUEP = @VALUEP WHERE code = @code";
SqlCommand cmdp = new SqlCommand(SQLP, cn);
cmdp.Parameters.AddWithValue("@VALUEP", SqlDbType.Int).Value = mValue;
cmdp.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rt["code"].ToString();
cmdp.ExecuteNonQuery();
}
}
}
}
Bhuvanesh MohankumarPosted May 18, 2016, 4:00 AM
http://stackoverflow.com/questions/556552/updating-database-records-in-a-loop