插入，更新和记录存在不起作用 [英] Insert, Update and record exist not working

问题描述

我有一个网络表单，用户可以保存他们的进度并返回编辑或稍后添加。当用户输入他们的数据并想要回来时，他们可以将其保存到数据库表中，直到用户回来为止。当用户返回时，数据被填充回文本框，用户可以完成数据输入或编辑那里的数据并完成输入并单击保存。数据已插入数据库但未更新。在我的代码中我做错了什么不行？

  protected   void  ButtonSubmit2_Click（ object  sender，EventArgs e）
 {
 SqlConnection con =  new  SqlConnection（System.Configuration.ConfigurationManager.ConnectionStrings [  HotConnectionString]的ConnectionString）。 
 con.Open（）; 
  string  cmdStr =  选择计数（* ）来自Table88，其中User_ID =' + TextBoxUser_ID.Text +  '; 
 SqlCommand userExist =  new  SqlCommand（cmdStr，con）; 
 SqlCommand cmd =  new  SqlCommand（  select表88中的User_ID，con）; 
  int  temp = Convert.ToInt32（userExist.ExecuteScalar（）。ToString（））; 
 
  if （temp ==  0 ）
 {
  const   string  commandText =  @  INSERT INTO Table99 
（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）
 SELECT 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 FROM Table88 WHERE User_ID = @User_ID;; 
 
 使用（SqlCommand cmd2 =  new  SqlCommand（commandText，con））
 cmd.Parameters.AddWithValue（  @ User_ID，TextBoxUser_ID.Text）; 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
} 
  else   if （temp ==  1 ）
 {
  const   string  commandText =  @  UPDATE INTO Table99 
（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）
 SELECT 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 FROM Table88 WHERE User_ID = @User_ID;; 
} 

解决方案

您的更新语句错误。以下是UPDATE INTO SELECT的示例

 UPDATE表1 
 SET Col2 = t2.Col2，
 Col3 = t2 .Col3 
 FROM Table1 t1 
 INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1 
 WHERE t1.Col1 IN（21,31）
 GO 

有关详细信息，请参阅以下链接： -



http：//blog.sqlauthority。 COM / 2013/04/30 / SQL服务器更新从 - 选择 - 语句使用联接功能于更新语句多表-在更新语句

将您的更新 更改为  -  更新  INTO 表99 
 设置 User_ID = User_ID，FT_UNDERGR = FT_UNDERGR，
 DATE = DATE，FT_GRAD = FT_GRAD，FTE_UNDERG = FTE_UNDERG，
 FTE_GRAD = FTE_GRAD，NON_CREDIT = NON_CREDIT，
 TOTAL_FTE = TOTAL_FTE，FCFTUHC = FCFTUHC 
 FCFTPBHC = FCFTPBHC，FCPTUHC = FCPTUHC， 
 FCPTPB HC = FCPTPBHC，NCHC = NCHC，
 UnderG12 = UnderG12，Postb9 = Postb9，
 Total123b4b = Total123b4b，FTEYR = FTEYR，
 THCAS = THCAS，FTE40 = FTE40，
 H1C50 = H1C50，FTE4050 = FTE4050 
  WHERE  User_ID =  @ UserId  

您好，



您需要使用存储过程而不是在代码端编写SQL。请仔细阅读以下存储过程，它将帮助您按预期实现结果。



要了解并使用存储过程，请查看以下几个链接。





Sql Server - 如何在Sql server中编写存储过程 [ ^ ]

SQL Server存储过程 [ ^ ]

使用ASP.Net示例选择SQL Server存储过程 [ ^ ]

 创建  PROCEDURE  AddInsertUser 
  @ UserId   VARCHAR （< span class =code-digit> 30 ）
  AS  
  IF   EXISTS （ SELECT   1  < span class =code-keyword> FROM  Table88 其中 User_ID =  @ UserId ）
  BEGIN  
 更新  INTO  Table99 
（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）
  SELECT  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  FROM  Table88  WHERE  User_ID =  @ UserId  
  END  
  ELSE  
  BEGIN  
  INSERT   INTO 表99 
（User_ID，FT_UNDERGR， DATE ，FT_GRAD，FTE_UNDERG，FTE_GRAD，NON_CREDIT，TOTAL_FTE，FCFTUHC，FCFTPBHC，FCPT UHC，FCPTPBHC，NCHC，UnderG12，Postb9，Total123b4b，FTEYR，THCAS，FTE40，HC50，FTE4050）
  SELECT  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  FROM  Table88  WHERE  User_ID =  @ UserId ; 
  END  
 
  SELECT  *  FROM  Table88  WHERE  User_ID =  @ UserId   / *  将更新或新插入的记录返回到代码方* /  

I have a web form that a user can save their progress and come back to edit or add to it later. When a user enters their data and wants to come back they can save it to a database table where it stays until the user comes back. When the user comes back the data is populated back into the textboxes and the user can finish their data entry or edit the data that is there and finish their entry and click save. The data is getting Inserted into the database but not updated. What did I do wrong in my code for this not work?

protected void ButtonSubmit2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
            con.Open();
            string cmdStr = "Select count(*) from Table88 where User_ID='" + TextBoxUser_ID.Text + "'";
            SqlCommand userExist = new SqlCommand(cmdStr, con);
            SqlCommand cmd = new SqlCommand("select User_ID from Table88", con);
            int temp = Convert.ToInt32(userExist.ExecuteScalar().ToString());

            if (temp == 0)
            {
                const string commandText = @"INSERT INTO Table99
            (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)
             SELECT 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 FROM Table88 WHERE User_ID = @User_ID;";

                using (SqlCommand cmd2 = new SqlCommand(commandText, con))
                    cmd.Parameters.AddWithValue("@User_ID", TextBoxUser_ID.Text);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            else if (temp == 1)
            {
                const string commandText = @"UPDATE INTO Table99
            (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)
             SELECT 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 FROM Table88 WHERE User_ID = @User_ID;";
            }

解决方案

Your update statement is wrong. Here is the example of UPDATE INTO SELECT

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Refer the following link for more details:-

http://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement

change your update to - UPDATE INTO Table99
            set User_ID=User_ID, FT_UNDERGR=FT_UNDERGR,
DATE=DATE,  FT_GRAD=FT_GRAD, FTE_UNDERG=FTE_UNDERG,
FTE_GRAD=FTE_GRAD, NON_CREDIT=NON_CREDIT,
TOTAL_FTE=TOTAL_FTE, FCFTUHC = FCFTUHC
FCFTPBHC=FCFTPBHC, FCPTUHC=FCPTUHC,
FCPTPBHC=FCPTPBHC, NCHC=NCHC,
UnderG12= UnderG12, Postb9=Postb9,
Total123b4b=Total123b4b, FTEYR=FTEYR,
THCAS=THCAS,FTE40=FTE40,
H1C50=H1C50, FTE4050=FTE4050
WHERE User_ID = @UserId

Hi,

You need to user Stored Procedure instead of writing SQL on code side. Kindly review below stored procedure which will help you to achieve your result as expected.

To understand and work with stored procedure, review below few links.


Sql Server - How to write a Stored procedure in Sql server[^]
SQL Server Stored Procedure[^]
Select SQL Server Stored Procedures using ASP.Net Example[^]

CREATE PROCEDURE AddInsertUser
    @UserId VARCHAR(30)
AS
    IF EXISTS(SELECT 1 FROM Table88 where User_ID= @UserId)
        BEGIN
            UPDATE INTO Table99
            (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)
             SELECT 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 FROM Table88 WHERE User_ID = @UserId
        END
    ELSE
        BEGIN
            INSERT INTO Table99
            (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)
             SELECT 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 FROM Table88 WHERE User_ID = @UserId;
        END

    SELECT * FROM Table88 WHERE User_ID = @UserId /* To return updated or newly inserted record to code side */

这篇关于插入，更新和记录存在不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！