如何检查值是否已经存在与否数据库SQL Server 2008中插入新记录时

本文介绍了如何检查值是否已经存在与否数据库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）我用这个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) 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)