在同一命令代码中插入和更新 [英] Insert and Updating in the same command code

问题描述

我有一个Web表单，用户可以输入数据并将其保存到数据库中。用户可以保存数据并稍后返回编辑或稍后完成。让我们说用户输入数据并将其保存在数据库的顶部。稍后再回来看到一两个数字不正确并编辑这些数字。用户保存的数据将填充回Web表单，以便用户可以看到他们输入的内容。我的问题是，如何在不将新记录插入数据库的情况下更新数据？



以下是填充文本框的代码：

I have a web form that a user can enter data and save it to the database. The user can save the data and come back to edit it later or finish it later. Lets say the user enters data and saves it top the database. Comes back later and sees that one or two numbers are incorrect and edit those numbers. The data that the user saved is populated back to the web form so the user can see what they entered. My question is, How can the user update the data without inserting a new record into the database?

Here is the code I have for populating the textboxes:

if (!IsPostBack)
        {
            if (TextBoxUser_ID.Text.Trim().Length > 0)
            {
                SqlConnection con44 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
                con44.Open();

                SqlCommand scmd44 = new SqlCommand("Select FT_UNDERGR, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, THCAS, FTE40, HC50, FTE4050 from Table44 where User_ID = '" + TextBoxUser_ID.Text + "'", con44);
                SqlDataReader dr44 = scmd44.ExecuteReader();
                if (dr44.Read())
                {
                    TextBoxFTUG.Text = dr44["FT_UNDERGR"].ToString();
                    TextBoxFTG.Text = dr44["FT_GRAD"].ToString();
                    TextBoxTHUGDR.Text = dr44["FTE_UNDERG"].ToString();
                    TextBoxTHGDR.Text = dr44["FTE_GRAD"].ToString();
                    TextBoxNCCDR.Text = dr44["NON_CREDIT"].ToString();
                    TextBoxTCNC.Text = dr44["TOTAL_FTE"].ToString();
                    TextBoxTNFUG.Text = dr44["FCFTUHC"].ToString();
                    TextBoxTNFG.Text = dr44["FCFTPBHC"].ToString();
                    TextBoxTNCPUG.Text = dr44["FCPTUHC"].ToString();
                    TextBoxTNCPG.Text = dr44["FCPTPBHC"].ToString();
                    TextBoxTNNCC.Text = dr44["NCHC"].ToString();
                    TextBoxFTE40.Text = dr44["FTE40"].ToString();
                    TextBoxHC50.Text = dr44["HC50"].ToString();
                    TextBoxFTE4050.Text = dr44["FTE4050"].ToString();
                    TextBoxTHUG.Text = dr44["UNDERG12"].ToString();
                    TextBoxTHG.Text = dr44["Postb9"].ToString();
                    TextBoxT1234.Text = dr44["TOTAL123b4b"].ToString();
                    TextBoxTHCAS.Text = dr44["THCAS"].ToString();
                }
                con44.Close();
                dr44.Close();

这是插入代码：

Here is the Insert code:

protected void ButtonSave_Click(object sender, EventArgs e)
    {
        SqlConnection con7 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
        con7.Open();

        SqlCommand cmd = new SqlCommand("Insert into Table44 (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050) values (@User_ID, @FT_UNDERGR, @DATE, @FT_GRAD, @FTE_UNDERG, @FTE_GRAD, @NON_CREDIT, @TOTAL_FTE, @FCFTUHC, @FCFTPBHC, @FCPTUHC, @FCPTPBHC, @NCHC, @UnderG12, @Postb9, @Total123b4b, @FTEYR, @THCAS, @FTE40, @HC50, @FTE4050);", con7);

        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("@User_ID", TextBoxUser_ID.Text);
        cmd.Parameters.AddWithValue("@FT_UNDERGR", TextBoxFTUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FT_GRAD", TextBoxFTG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE_UNDERG", TextBoxTHUGDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE_GRAD", TextBoxTHGDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@NON_CREDIT", TextBoxNCCDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@TOTAL_FTE", TextBoxTCNC.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCFTUHC", TextBoxTNFUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCFTPBHC", TextBoxTNFG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCPTUHC", TextBoxTNCPUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCPTPBHC", TextBoxTNCPG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@NCHC", TextBoxTNNCC.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTEYR", lblYEAR1.Text);
        cmd.Parameters.AddWithValue("@DATE", TextBoxDATE.Text);
        cmd.Parameters.AddWithValue("@UnderG12", TextBoxTHUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@Postb9", TextBoxTHG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@Total123b4b", TextBoxT1234.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@THCAS", TextBoxTHCAS.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE40", TextBoxFTE40.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@HC50", TextBoxHC50.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE4050", TextBoxFTE4050.Text.Replace(",", ""));

        cmd.ExecuteNonQuery();

        if (Page.IsPostBack)
        {
            ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully saved the electronic portion of the Enrollment Profile. You will now be direct to the Graduation Rate Information Page');location.href='Gradrate.aspx';", true);
        }

推荐答案

创建存储过程 [ ^ ]如果记录不存在则插入或更新。示例sql：

create stored procedure[^] to insert or update if record does not exist. sample sql:

IF EXISTS (SELECT * FROM YourTable WHERE pkid= @pkid)
        UPDATE YourTable 
        SET col1= @col1, ......
        WHERE pkid= @pkid
     ELSE
        INSERT INTO YourTable (col1, col2,...)

最终从c＃代码执行存储过程

名为Table44的表的User_ID字段将能够告诉你它是更新还是插入。这个User_ID应该是主键。检查是否存在User_ID值以确定是否插入或更新。

The "User_ID" field of table named "Table44" will be able to tell you whether it is an update or insert. This "User_ID" should be a primary key. Check the existence of the "User_ID" value to determine whether to insert or update.

SqlConnection con7 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
        con7.Open();
IF EXISTS (SELECT * FROM Table44 WHERE User_ID = @User_ID)
        UPDATE Table44 
        SET User_ID = @User_ID, FT_UNDERGR = @FT_UNDERGR, ..... 
        WHERE User_ID = @User_ID
     ELSE
        INSERT INTO Table44 (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050) values (@USER_ID, @FT_UNDERGR, @DATE, @FT_GRAD, @FTE_UNDERG, @FTE_GRAD, @NON_CREDIT, @TOTAL_FTE, @FCFTUHC, @FCFTPBHC, @FCPTUHC, @FCPTPBHC, @NCHC, @UnderG12, @Postb9, @Total123b4b, @FTEYR, @THCAS, @FTE40, @HC50, @FTE4050);", con7);

cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("@User_ID", TextBoxUSER_ID.Text);
        cmd.Parameters.AddWithValue("@FT_UNDERGR", TextBoxFTUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FT_GRAD", TextBoxFTG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE_UNDERG", TextBoxTHUGDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE_GRAD", TextBoxTHGDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@NON_CREDIT", TextBoxNCCDR.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@TOTAL_FTE", TextBoxTCNC.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCFTUHC", TextBoxTNFUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCFTPBHC", TextBoxTNFG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCPTUHC", TextBoxTNCPUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FCPTPBHC", TextBoxTNCPG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@NCHC", TextBoxTNNCC.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTEYR", lblYEAR1.Text);
        cmd.Parameters.AddWithValue("@DATE", TextBoxDATE.Text);
        cmd.Parameters.AddWithValue("@UnderG12", TextBoxTHUG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@Postb9", TextBoxTHG.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@Total123b4b", TextBoxT1234.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@THCAS", TextBoxTHCAS.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE40", TextBoxFTE40.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@HC50", TextBoxHC50.Text.Replace(",", ""));
        cmd.Parameters.AddWithValue("@FTE4050", TextBoxFTE4050.Text.Replace(",", ""));

        cmd.ExecuteNonQuery();

这篇关于在同一命令代码中插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！