如何检查值是否已经存在与否数据库SQL Server 2008中插入新记录时 [英] How to check whether value already exists or not in database when inserting new record in sql server 2008

问题描述

我创建了一个存储过程如果该值已经存在那么它会更新该值从表中选择价值，但如果不存在，那么它会插入新的价值。

我用存储过程是如下：

  CREATE PROCEDURE [DBO]。[sp_Insert]
（
    @Tid INT = NULL，
    @Quid INT = NULL，
    @Selected INT = NULL，
    @Score INT = NULL
）
如
   DECLARE @AlreadyExisted BIT
   SET @AlreadyExisted = 0   IF（EXISTS（SELECT FROM奎德Sel_Ans）
   开始
      SET @AlreadyExisted = 1      UPDATE Sel_Ans
      组选定= @选择
      WHERE奎德= @Quid
   结束
   其他
   开始
     INSERT INTO Sel_Ans（奎德，入选）
       VALUES（@Quid，@Selected）
   结束
返回
 

和在C＃（Asp.net）我用这个code，但它给错误错误连接到数据库作为其在try块去。我建议我的code的任何变化才能正常工作。

  //会话[计数器]是我的柜台，这将使下一个记录在明年点击事件
如果（Convert.ToInt32（会话[计数器]）＆LT; = 7）
{
   INT T = Convert.ToInt32（会话[计数器]）;   的SqlCommand MyCmd =新的SqlCommand（sp_Insert，sqlconn）;
   MyCmd.CommandType = CommandType.StoredProcedure;   的SqlParameter objParam1 = NULL;
   的SqlParameter objParam2 = NULL;
   的SqlParameter objParam3 = NULL;
   的SqlParameter objParam4 = NULL;   objParam1 = MyCmd.Parameters.Add（@ TID，SqlDbType.Int）;
   objParam1.Value ='1';
   objParam2 = MyCmd.Parameters.Add（@英镑，SqlDbType.Int）;
   objParam2.Value =一[T  -  2];
   objParam3 = MyCmd.Parameters.Add（@选，SqlDbType.Int）;
   objParam3.Value = RB;
   objParam4 = MyCmd.Parameters.Add（@分数，SqlDbType.Int）;
   objParam4.Value ='1';   尝试
   {
       如果（sqlconn.State == ConnectionState.Closed）
       {
          sqlconn.Open（）;
          MyCmd.ExecuteNonQuery（）;
       }
   }
   赶上（异常前）
   {
      的Response.Write（错误连接数据库！）;
   }
}
 

解决方案

当您传递价值，你的存储过程：

  objParam1 = MyCmd.Parameters.Add（@ TID，SqlDbType.Int）;
objParam1.Value ='1';
 

您正在传递一个字符串值（ 1 ）。

您必须通过这为整数，如您对存储过程数据类型是 INT ：

  objParam1.Value = 1;
 

做同样为其他参数也是如此。

  IF（EXISTS（SELECT FROM奎德哪里Sel_Ans奎德= @Quid）
 

I have created a stored procedure for selecting value from the table if that value already exists then it will update that value but if not exists then it will insert new value.

I used stored procedure for that as follows :

Create PROCEDURE [dbo].[sp_Insert]
(
    @Tid int = NULL,
    @Quid int = NULL,
    @Selected int = NULL,
    @Score int = NULL
)
AS
   DECLARE @AlreadyExisted BIT 
   SET @AlreadyExisted = 0  

   IF (EXISTS (SELECT Quid FROM  Sel_Ans) 
   BEGIN
      SET @AlreadyExisted = 1 

      UPDATE Sel_Ans
      SET Selected=@Selected
      WHERE Quid = @Quid 
   END 
   ELSE 
   BEGIN 
     INSERT INTO Sel_Ans (Quid, Selected) 
       VALUES (@Quid, @Selected)
   END
RETURN

And in C# (Asp.net) I used this code but its giving error "Error connecting to the database" as its going in try block. Suggest me any changes to my code to work correctly.

//session["Counter"] is my counter that will give next record in next click event
if (Convert.ToInt32(Session["Counter"]) <= 7)
{
   int t = Convert.ToInt32(Session["Counter"]);

   SqlCommand MyCmd = new SqlCommand("sp_Insert", sqlconn);
   MyCmd.CommandType = CommandType.StoredProcedure;

   SqlParameter objParam1 = null;
   SqlParameter objParam2 = null;
   SqlParameter objParam3 = null;
   SqlParameter objParam4 = null;

   objParam1 = MyCmd.Parameters.Add("@Tid", SqlDbType.Int);
   objParam1.Value = '1';
   objParam2 = MyCmd.Parameters.Add("@Quid", SqlDbType.Int);
   objParam2.Value = a[t - 2];
   objParam3 = MyCmd.Parameters.Add("@Selected", SqlDbType.Int);
   objParam3.Value = rb;
   objParam4 = MyCmd.Parameters.Add("@Score", SqlDbType.Int);
   objParam4.Value = '1';

   try
   {
       if (sqlconn.State == ConnectionState.Closed)
       {
          sqlconn.Open();
          MyCmd.ExecuteNonQuery();
       }
   }
   catch (Exception ex)
   {
      Response.Write("Error Connecting to Database!");
   }
}

解决方案

When you are passing value to your stored procedure:

objParam1 = MyCmd.Parameters.Add("@Tid", SqlDbType.Int);
objParam1.Value = '1'; 

You are passing a string value ('1').

You have to pass this as an integer, as your datatype in the sproc is int:

 objParam1.Value = 1;

do the same for the other parameter as well.

IF (EXISTS (SELECT Quid FROM Sel_Ans where Quid = @Quid)

这篇关于如何检查值是否已经存在与否数据库SQL Server 2008中插入新记录时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！