为什么存储过程返回-1

问题描述

我对存储过程非常陌生.我没有更多的想法.

I am very new with stored procedure.I don't have more idea.

我正在尝试使用存储过程在数据库中插入数据.

I am trying to insert data in my database using stored procedure.

如果存在，则返回0，否则插入数据并返回1.

If it exist it return 0 otherwise insert data and return 1.

这样我就可以在标签上打印出已插入数据或数据已经存在的文本.

So that I can print text on label that data inserted or data already exist.

每个条目都返回-1.

这是我的存储过程.不是吗?

Here is my stored procedure. Is it not right ?

    USE [HRMS]
    GO
    /****** Object:  StoredProcedure [dbo].[hrm_AddLanguages2]    Script Date:   05/02/2014 10:19:38 ******/
   SET ANSI_NULLS ON
   GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[hrm_AddLanguages2]
    -- Add the parameters for the stored procedure here

    @Name varchar(120),
    @CreatedOn datetime,
    @UpdatedOn datetime=0,
    @CreatedBy bigint=0,
    @UpdatedBy bigint=0,
    @IsDeleted bit=0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRANSACTION
        DECLARE @ID int;
        SELECT @ID = coalesce((select max(ID) + 1 from Languages), 1)
    COMMIT
    -- Insert statements for procedure here
    IF EXISTS(SELECT Name FROM [dbo].[Languages] WHERE Name = @Name)
    Begin
    Return 0
    End
    Else
        INSERT INTO [dbo].[Languages](Name, CreatedOn,UpdatedOn,CreatedBy,UpdatedBy,IsDeleted) VALUES(@Name,@CreatedOn,@UpdatedOn,@CreatedBy,@UpdatedBy,@IsDeleted)
        Return 1
END

这是我的Default.aspx.cs代码-

Here is my Default.aspx.cs code-

protected void btnSave_Click(object sender, EventArgs e)
{

if (Page.IsValid)
    {
    btnAdd.Visible = true;
    btnDelete.Visible = true;
    Label2.Visible = true;
    tblAdd.Visible = false;
    int valueReturn = add();
    if (valueReturn == 1)
    {
        Label2.Text = "Successfully Added";
    }
    else
    {
        Label2.Text = "Already Exist";
    }
    }
    GridView1.DataBind();
    txtName.Text = "";
}

public int add()
{
    string strcon =  ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString;
    SqlConnection sqlConnection = new SqlConnection(strcon);

    SqlCommand command = new SqlCommand("hrm_AddLanguages2", sqlConnection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text;
    command.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = DateTime.Now;
    command.Parameters.Add("@UpdatedOn", SqlDbType.DateTime).Value = DateTime.Now;
    command.Parameters.Add("@CreatedBy", SqlDbType.BigInt).Value = 1;
    command.Parameters.Add("@UpdatedBy", SqlDbType.BigInt).Value = 1;
    command.Parameters.Add("@IsDeleted", SqlDbType.Bit).Value = 0;
    sqlConnection.Open();
    return command.ExecuteNonQuery();
}

推荐答案

您正在Default.aspx中使用ExecuteNonQuery

You are using ExecuteNonQuery in your Default.aspx

如果没有记录受查询影响，则ExecuteNonQuery返回-1.

ExecuteNonQuery returns -1 if no records affected by your query.

在这里，您已经在存储过程中设置了nocounton.这就是为什么在您的executenonquery中没有记录受影响的值(意味着-1)即将到来.

Here you have set nocounton in your stored procedure. That's why in your executenonquery no records affected value( means -1) is coming.

设置计数，您将获得理想的结果.

Set your count on you will get desired result.

您可以通过下面的链接获取更多详细信息-

You can go through below link for more details-

http://blogs.msdn.com/b/spike/archive/2009/01/27/sqlcommand-executenonquery-returns-1-when-doing-insert-update-delete.aspx

这篇关于为什么存储过程返回-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！