在sql中添加到现有行 [英] Adding to an existing row in sql

问题描述

您好，有人可以找出我要去哪里的地方吗，我试图让我的C#程序查看一个表，并确定所选值是否已经存在，如果有，请更新与之关联的值，如果不添加新值值.
尝试过几种方法后，我认为我是代码盲者，非常感谢您的帮助.

下面的代码:

Hello, could anyone spot where i am going wrong, i am trying to get my c# program to look at a table, and determine if the selected value already exists and if so update the value associated with it, if not add new value.
Having tried several methods i think i am code blind, any help would be greatly appreciated, thank you.

Code below:

// *********************************************************************
// Calculate Product & Amount then pass results to 'Temptotalmade' table
// *********************************************************************

string product = ProductSelect.Text;

SqlCommand readTempTable = new SqlCommand();
string sqlReadTempTable = "Select Product,amount from TempTotalMade where FactoryID = '" + FactoryID.TrimEnd() + "' and AreaID = '" + Program.MAKE + "' and DaysNightsID = '" + Program.DAYS + "' and Product = '" + product.TrimEnd() + "'";
MessageBox.Show(sqlReadTempTable);
readTempTable.Connection = databaseConnection;
readTempTable.CommandText = sqlReadTempTable;

if  (readTempTable == null)
{
    string productdays = ProductSelect.Text;
    SqlCommand addNewTotMade = new SqlCommand();
    addNewTotMade.Connection = databaseConnection;
    string sqlAddNewTotMade = "Insert into TempTotalMade (FactoryID,AreaID,DaysNightsID,Product,amount) values ('" + FactoryID.TrimEnd() + "','" + Program.MAKE + "','" + Program.DAYS + "','" + productdays.TrimEnd() + "'," + make.totalBrickDays + ")";
    MessageBox.Show(sqlAddNewTotMade);
    addNewTotMade.CommandText = sqlAddNewTotMade;
    addNewTotMade.ExecuteNonQuery();
}

else
{
    SqlDataReader getproductdata;

    getproductdata = readTempTable.ExecuteReader();
    getproductdata.Read();

    string productexists = (getproductdata[("Product")].ToString());
    string productstring = productexists.TrimEnd();
    string amountexist = (getproductdata[("amount")].ToString());
    int isamount = int.Parse(amountexist);
    getproductdata.Close();

    double newtotal = isamount + make.totalBrickDays;
    SqlCommand updateproduct = new SqlCommand();
    updateproduct.Connection = databaseConnection;
    string sqlUpdateProduct = "Update TempTotalMade set amount = " + newtotal + " where FactoryID ='" + FactoryID.TrimEnd() + "' and AreaID = '" + Program.MAKE + "' and DaysNightsID = '" + Program.DAYS + "' and Product = '" + productstring.TrimEnd() + "'";
    updateproduct.CommandText = sqlUpdateProduct;
    updateproduct.ExecuteNonQuery();
    label33.Text = newtotal.ToString();

}

this.mADEPRODUCTDAYSTableAdapter.Fill(this.PIMPDataSet.MADEPRODUCTDAYS, @FactoryID);

textBox1.Text = "";
textBox2.Text = "";

推荐答案

由于您还没有说过要针对的SQL Server版本，我假设它是更高版本-方便，因为它们提供了非常有用的 MERGE [

As you haven''t said which version of SQL Server you are targetting, I''m assuming it''s a later version - which is handy because they provide a very useful MERGE[^] command which allows you to perform what are sometimes known as upserts.

An upsert is a SQL command that can either be an insert or an update, depending on the selection criteria.

An extra point - never concatenate strings to create SQL statements. You leave yourself wide open to SQL Injection attacks.

这篇关于在sql中添加到现有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！