如何循环和更新SQL表汇总任何现有值 [英] How to loop and update SQL table summing any existing values

问题描述

谢谢大家，我需要你的帮助，以下是在 TableA 中有两个表 TableA和TableB 我有以下字段代码，描述，值

程序将从 TableA 获取代码字段名称并搜索 TableB 如果找到它，则值字段将在 TableA 中更新，如果 TableA 有值，那么它将添加 TableA-> Values + TableB->值执行此操作的代码如下（m_Code == colname&& mValue > 0 ）{
mValue + = Convert.ToInt32（row [column] .ToString（））;
} else {
mValue = Convert.ToInt32（row [column] .ToString（））;
}

我的挑战是当它循环通过 TableB 时它只返回 TableB 到 TableA 。整个代码如下：



我尝试过：

 DataTable dt = GetDatafromDatabase（）;  //   =====返回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（））
 {
  //  对于每一行，打印每列的值。 
  foreach （DataColumn column  dt.Columns中的class =code-keyword> 
 {
  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 [柱]的ToString（））; }  else  {mValue = Convert.ToInt32（row [column] .ToString（））; } 
  //   ============插入tbl_TempReport以匹配相应的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（）; 
} 
 
} 
 
} 
} 

解决方案

< blockquote>你在每个循环中将m_Value设置为零，因此除了单个值之外它永远不会得到任何东西。此外，您正在使用SQL更新，但评论表明您要插入。



我会重写这样的代码：

使用列键和数值作为值创建字典。循环遍历行并根据列名称添加值。



遍历字典并使用最终值更新表格。



祝你好运。

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 :

What I have tried:

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

    }

    }
    }

解决方案

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.

这篇关于如何循环和更新SQL表汇总任何现有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！