Check if record exists using SP & return value if it does

[while(!success)]

Hi guys,

I'm still new to .NET and your answers to my questions have been so helpful. I have another question yet again, however. This time my question has to do with sql server and handling it with code behind. I am creating a stored procedure which is supposed to check if a record exists in the table and if it does, return a value so that I may tell the user that the record already exists. If it does not exist, and insert statement is executed.
Here is my stored procedure:

    @UserID VARCHAR(50),
    @PlayerID VARCHAR(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM FB_UserSelections WHERE PlayerID = @PlayerID)
        RETURN 0 
    ELSE
        INSERT INTO FB_UserSelections (UserID, PlayerID) VALUES (@UserID, @PlayerID)
        RETURN 1
END
GO

Am I correctly returning a value? If so how do I handle this in the code behind so that I may set a label to visible invisible? Here is what I am attempting in the code behind:

String Pid = Request.QueryString["PlayerID"];
String UserId = Request.QueryString["UserID"];

using (SqlConnection con = new SqlConnection(constr))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandText = "FB_CheckIfDrafted";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserID", UserId);
    cmd.Parameters.AddWithValue("@PlayerID", Pid);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

LBl_DraftedPrompt.Visible = true;

I want to store the returned value of the SP in a variable in the code behind so that I could do an if/else statement to either display Lbl_DraftedPrompt or not. Thank you for your time and help.

EDIT
I have been testing my SP and it seems to work as I want because PlayerID can only exist once in the FB_UserSelections table. The only thing i'm having trouble with now is handling the RETURNED 1 or 0 in the code behind. But if you see any issues with what you see above, please let me know.

[pritaeas]

I think ExecuteNonQuery does not expect a result to be returned. I think you need ExecuteScalar, but am not entirely sure.

[JorgeM]

When performing a SQL SELECT, you can use ExecuteReader. When performing an UPDATE, INSERT, or DELETE, you can use ExecuteNonQuery().

With regard to your code, you have to read the values. Here is an example in VB (sorry, I dont know C# syntax as well). Just a sample from the top of my head so the code may need to be adjusted and definately converted to C# in your case.

con.Open()
cmd.Connection = con
cmd.CommandText = "select ..."
Dim rdr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
While rdr.Read()
    result = rdr(0)
End While
rdr.Close()
con.Close()

[while(!success)]

JorgeM,

In regards to your example, when you execute Read(), will that pull that Return value from my SP? Is it at all possible to assign the value to a variable in my code behind?
For instance:

int retValue = (value returned from stored procedure);

//then i could do
if (retValue == 1)
{
    label.Visible = true;
}
else
{
    label.Visible = false;
}

thank you for your suggestion and i'm also going to look into pritaeas' suggestion of using executescalar() as well.

[JorgeM]

assign the value to a variable in my code behind?

Yes absolutely. Your command statement could be that of a sQL query or a procedure.

[while(!success)]

JorgeM,

How are you implementing rdr(0)? Is there a method that your are trying to use along with that SqlDataReader object? Thanks again.

[while(!success)]

Hey I finally got it to work. I used the ExecuteScalar suggestion by pritaeas and had to change "RETURN" in my stored procedure to "SELECT"

Thanks again guys for your help, much appreciated.