3 Replies - 592 Views - Last Post: 19 May 2016 - 02:57 AM Rate Topic: -----

#1 Abraham1973   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 18-May 16

How to Update Table Matching Column name with row and summing the valu

Posted 18 May 2016 - 09:40 AM

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 :

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



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

This post has been edited by Skydiver: 18 May 2016 - 11:38 AM
Reason for edit:: Put tables into code tags to preserve formatting.


Is This A Good Question/Topic? 0
  • +

Replies To: How to Update Table Matching Column name with row and summing the valu

#2 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5103
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: How to Update Table Matching Column name with row and summing the valu

Posted 18 May 2016 - 09:54 AM

I don't understand what you're trying to do.

In table B, you have a Code (let's call that a Key for now), plus several values.

In table A, you have the same Code (let's call that one a Key as well), plus one value.

So, if you have one matching row in table A, which value from table B goes into Table A's Value field? If you have more than one match, which one gets which value?

I think you need to clarify your question.
Was This Post Helpful? 0
  • +
  • -

#3 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6166
  • View blog
  • Posts: 21,268
  • Joined: 05-May 12

Re: How to Update Table Matching Column name with row and summing the valu

Posted 18 May 2016 - 11:39 AM

I put the sample tables into code tags to make it more legible. Some parts of the question become clearer, but Curtis Rutland questions still have merit.
Was This Post Helpful? 0
  • +
  • -

#4 Abraham1973   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 18-May 16

Re: How to Update Table Matching Column name with row and summing the valu

Posted 19 May 2016 - 02:57 AM

Dear Curtis Rutland and Sky Diver,

thank you for the swift response the issue is, i inherit the code from my incubant programmer.
the real sample is as follows
[b]
TableA TableB

code   description   value                    code   BOOKS   BREADS   PHONES   FISH[/b] 
-----------------------------------          ------------------------------------------
CD1   BOOKS                                   CD1    12      22        21      10
CD2   BREADS                                  CD2    01      12        22      09
CD3   PHONES                                  CD3    21      23        23      10
CD4   FISH                                    CD4    20      26        33      30



Now from TABLeA and TableB we can see that Code column have the same data and it is the key field, the idea is to search for data in column Description of TableA that match tableB column and store the Values in it respected column in TableA, if their is value in that respected column then do a summation and the result to be stored back to tableA value column of the respected row. And we have 2000 records to loop through and archive this, the code below only store for the first row of TableA but we are expected to repeat this for the 2000 record.

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

 }

  }

}

}
[code]

Dear Curtis Rutland and Sky Diver,

thank you for the swift response the issue is, i inherit the code from my incubant programmer.
the real sample is as follows

[b]TableA [/b]                                                     [b]TableB[/b]

[b]code   description   value[/b]             [b]code   BOOKS   BREADS   PHONES   FISH[/b]
-----------------------------------          ------------------------------------------
CD1   BOOKS                                   CD1    12      22        21      10
CD2   BREADS                                  CD2    01      12        22      09
CD3   PHONES                                  CD3    21      23        23      10
CD4   FISH                                    CD4    20      26        33      30


Now from TABLeA and TableB we can see that Code column have the same data and it is the key field, the idea is to search for data in column Description of TableA that match tableB column and store the Values in it respected column in TableA, if their is value in that respected column then do a summation and the result to be stored back to tableA value column of the respected row. And we have 2000 records to loop through and archive this, the code below only store for the first row of TableA but we are expected to repeat this for the 2000 record.

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

 }

  }

}

}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1