Minosum Minosum - 3 months ago 47
SQL Question

SQL C# Check if record exists, if exists delete

Using stored procedures to try to check if a record exists or not, if it does it should do so by deleting and giving the appropriate message, if not send another message. here is my C# code.

protected void deleteButton_Click(object sender, EventArgs e)
{
int deletedCustomers;

if (String.IsNullOrEmpty(txtCustID.Text))
{
lblError.Text = "Please enter a valid Customer ID";
return;
}
else
{
dynamic procedure = "dbo.deleteCustomer";

using (command = new SqlCommand(procedure, conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CustID", txtCustID.Text);
}

try
{
conn.Open();
command.ExecuteNonQuery();
deletedCustomers = command.ExecuteNonQuery();

if (deletedCustomers > 0) {
lblError.Text = "Deleted!";
}
else
{
lblError.Text = "Customer ID does not exist";
}

conn.Close();
}
catch (Exception ex)
{
lblError.Text = "Error: " + ex.Message;
conn.Close();
}
}
}


Here is my SQL Server stored procedure:

CREATE PROCEDURE [dbo].[deleteCustomer]
@CustID INT
AS
DELETE FROM [dbo].[Customer]
WHERE CustID = @CustID


Running this code deletes the record as I want, but doesn't output the right label, when I try and delete a record that doesn't exist or exists it gives me the 'Does not exist' text output never the 'Deleted' text output in the IF statement.

What do I need to change in the IF statement in order for it to see that the
command.ExecuteNonQuery
equals anything or not, so I can get it to show the correct output? Thank you.

Answer

You need to get @@ROWCOUNT as follow:

CREATE PROCEDURE [dbo].[deleteCustomer]
@CustID INT
AS
DELETE FROM [dbo].[Customer]
WHERE CustID = @CustID
select @@ROWCOUNT

It will return No of effective rows

Remove this statement

command.ExecuteNonQuery();

and Just use this one

 deletedCustomers = command.ExecuteScalar();

EDIT

try
       {
           conn.Open();

           deletedCustomers = command.ExecuteScalar();

           if (deletedCustomers > 0) {
               lblError.Text = "Deleted!";   
           }
           else
           {
               lblError.Text = "Customer ID does not exist";
           }

           conn.Close();              
       }