Tech
Forums
Jobs
Books
Events
Live
Interviews
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
1
Reply
How to loop and update SQL table summing any existing values
Abraham Olatubosun
May 17 2016 4:30 PM
357
Reply
Thank you all,i need your help in the following areai have two table
TableA and TableB
in
TableA
i have the following fields
"code, description, values"
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();
}
}
}
}
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
How to use the VLOOKUP function using NPOI c# ?
how to avoid duplicate records based on time?