How to loop and update SQL table summing any existing values

Question

0.00/5 (No votes)

See more:

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 :

C#

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 :

What I have tried:

C#

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

    }

    }
    }

Posted 17-May-16 10:25am

AbrahamOlatubosun1973

Updated 17-May-16 21:27pm

Tomas Takac

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sinisa Hajnal · Answer 1 · 2016-05-17T21:27:00

Solution 1

You're setting m_Value to zero in each loop so it never gets anything but single value. Also, you're using SQL update and yet, comment indicates you want to insert.

I would rewrite the code like this:
Create dictionary with column key and numeric value as value. Loop through rows and add values based on column names.

Loop through dictionary and update the table with final values.

Good luck.

Posted 17-May-16 21:27pm

Sinisa Hajnal

Comments

AbrahamOlatubosun1973 18-May-16 8:34am

thank you Sinisa, that really will not solve my challenge, can i explian more on the issue,

Hi all, Please i need your help on this module part of my program. i have 2 tables, TableA which has "code,description,value" and TableB which has "code, values" example below :

TableA TableB

code description value code value1 value2 value3 value4
---------------------------- ---------------------------------------------
CD1 BOOKS cd1 12 21 10 21
CD2 BREADS cd2 09 10 10 11
CD3 PHONES cd3 19 11 29 13
CD4 FISH cd4 10 12 22 12

the idea is to update TableA with the Values of TableB where TableA.code=TableB.code but if their are values in TableA then Update content of TableA Value field by adding the new values to the old value field where TableB.code = TableA.code. i have written the following code but it's only updating one row, below is my code :

DataTable dt = GetDatafromDatabase(); //===== returns a DataTable

string SQLT = "SELECT * FROM tbl_TempReport";

if (cn.State == ConnectionState.Closed) {cn.Open(); }

SqlCommand cmdT = new SqlCommand(SQLT, cn);

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

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

mdp.ExecuteNonQuery();

}

}

}

}

i need your help to achieve this. Thank you