ExecuteNonQuery question

[bonnysammy]

I'm sure I'm missing something that is fairly easy, but I can't get it to work.

I have a SQL Update Stored Proc. In this SP, the Primary Key can be modified. So, First I check if the PK is changing, then if it is, I check to see in the PK already exists.

Similar to this

If @OldValue = @NewValue
    BEGIN
        If exists(Select PrimaryKey From TableToUpdate Where PrimaryKey = @NewValue)
            BEGIN
                Return -50
            END
        ELSE
            BEGIN
                Update TableToUpdate Set @PrimaryKey = @NewValue, OtherValue=@OtherValue, @OtherValue2 = @OtherValue2
            END
    END
Else
    BEGIN
        Update TableToUpdate Set @PrimaryKey = @NewValue, OtherValue=@OtherValue, @OtherValue2 = @OtherValue2
    END

Any coding mistakes are due to typos. I have this on a PC that I cannot access right now.

.cs

string OldValue = txtOldvalue.Text.ToString();
string NewValue = txtNewvalue.Text.ToString();
string OtherValue = txtOthervalue.Text.ToString();
string OtherValue2 = txtOthervalue2.Text.ToString();

string connStr = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

SQLConnection conn = new SQL Connection(connStr);

SQLCommand comm = new SqlCommand("UpdateNewValues", conn);
comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("@OldValue", SqlDbType.VarChar).Value = OldValue;
comm.Parameters.Add("@NewValue", SqlDbType.VarChar).Value = NewValue;
comm.Parameters.Add("@OtherValue", SqlDbType.VarChar).Value = OtherValue;
comm.Parameters.Add("@OtherValue2", SqlDbType.VarChar).Value = OtherValue2;

conn.Open();
int rows = comm.ExecuteNonQuery();
lblInfo.Text = rows.ToString();
conn.close();

I have tried setting the text of lblInfo to rows both before and after the connection close. But lblInfo always has -1 no matter what I do. The record updates if it should so it's not a problem with the record updating. But I still get no return other than -1. Is there something I am missing?

Thank you

[madmital]

This works:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE UpdateNewValues
	-- Add the parameters for the stored procedure here
	@OldValue nvarchar(100),
	@NewValue nvarchar(100),
	@OtherValue nvarchar(100),
	@OtherValue2 nvarchar(100)
AS
BEGIN
If @OldValue = @NewValue
    BEGIN
        If Exists(Select PrimaryKey From TableToUpdate Where PrimaryKey = @NewValue)
            BEGIN
                Return -50
            END
        ELSE
            BEGIN
                Update TableToUpdate Set PrimaryKey = @NewValue, OtherValue = @OtherValue, OtherValue2 = @OtherValue2 WHERE primarykey = @oldvalue
            END
    END
Else
    BEGIN
        Update TableToUpdate Set PrimaryKey = @NewValue, OtherValue = @OtherValue, OtherValue2 = @OtherValue2 WHERE primarykey = @oldvalue
    END
END
GO

protected void Page_Load(object sender, EventArgs e)
    {
        string OldValue = "2"; //txtOldvalue.Text.ToString();
        string NewValue = "1"; //txtNewvalue.Text.ToString();
        string OtherValue = "othersomething"; //txtOthervalue.Text.ToString();
        string OtherValue2 = "hopscotch"; //txtOthervalue2.Text.ToString();

        string connStr = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand("UpdateNewValues", conn);
        comm.CommandType = CommandType.StoredProcedure;

        comm.Parameters.Add("@OldValue", SqlDbType.VarChar).Value = OldValue;
        comm.Parameters.Add("@NewValue", SqlDbType.VarChar).Value = NewValue;
        comm.Parameters.Add("@OtherValue", SqlDbType.VarChar).Value = OtherValue;
        comm.Parameters.Add("@OtherValue2", SqlDbType.VarChar).Value = OtherValue2;

        conn.Open();
        int rows = comm.ExecuteNonQuery();
        //lblInfo.Text = rows.ToString();
        Response.Write(rows);
        conn.Close();
}