CODEWITHSUNDEEP
asp.netsqlsql-server
Mash
Mash

Reputation: 159

stored procedure to delete entire table

I want to delete all the rows of a table on a button click.the stored procedure is as follows:

create proc spTest
as 
begin
     Delete from tblTest
end

The code-behind is as follows:

protected void Button3_Click(object sender, EventArgs e)
{
     string CS = ConfigurationManager.ConnectionStrings["EasyRozMoney_ConnectionString"].ConnectionString;
     using (SqlConnection con = new SqlConnection(CS))
     {
         SqlCommand cmd = new SqlCommand("spTest", con);
         cmd.CommandType = System.Data.CommandType.StoredProcedure;
         con.Open();
         lblStatus.Text = "Tasks Deleted Successfully.";
     }
}

but the table remains unaffected although the label shows all tasks deleted successfully. What is the problem? I know something is very silly that I am doing.

PS: I don't want to use Truncate.

Upvotes: 1

Views: 127

Answers (5)

Adil
Adil

Reputation: 148178

You have created Command but did not execute it. You have to call ExecuteNonQuery in order to exeucte the Command

As a addition note, put the code in try-catch block so that your application does not terminated in case of exception

protected void Button3_Click(object sender, EventArgs e)
{
  try
  {
     string CS = ConfigurationManager.ConnectionStrings["EasyRozMoney_ConnectionString"].ConnectionString;
     using (SqlConnection con = new SqlConnection(CS))
     {
         SqlCommand cmd = new SqlCommand("spTest", con);
         cmd.CommandType = System.Data.CommandType.StoredProcedure;
         con.Open();
         cmd.ExecuteNonQuery();
         lblStatus.Text = "Tasks Deleted Successfully.";
     }
  }
  catch(Exception ex)
  {
      lblStatus.Text = "Tasks could not be deleted, Error " + ex.Message;
  }
}

Upvotes: 2

Dgan
Dgan

Reputation: 10295

protected void Button3_Click(object sender, EventArgs e)
{
     string CS = ConfigurationManager.ConnectionStrings["EasyRozMoney_ConnectionString"].ConnectionString;
     using (SqlConnection con = new SqlConnection(CS))
     {
         SqlCommand cmd = new SqlCommand("spTest", con);
         cmd.CommandType = System.Data.CommandType.StoredProcedure;
         con.Open();
if(cmd.ExecuteNonQuery()>0)
{
         lblStatus.Text = "Tasks Deleted Successfully.";
}
else 
{
         lblStatus.Text = "Unable to  Delete tasks";
}

     }
}

Upvotes: 0

joker
joker

Reputation: 990

You are forgoted to execute the command.

add this cmd.ExecuteNonQuery(); to Button3_Click event

Upvotes: 0

Marco
Marco

Reputation: 23945

You are never acutally executing your query.

Call it like this:

using (SqlConnection con = new SqlConnection(CS))
{
     SqlCommand cmd = new SqlCommand("spTest", con);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     con.Open();
     /*new:*/
     cmd.ExecuteNonQuery();
     lblStatus.Text = "Tasks Deleted Successfully.";
}

Upvotes: 0

TechDo
TechDo

Reputation: 18659

You have to execute the query using ExecuteNonQuery command.

protected void Button3_Click(object sender, EventArgs e)
{
     string CS = ConfigurationManager.ConnectionStrings["EasyRozMoney_ConnectionString"].ConnectionString;
     using (SqlConnection con = new SqlConnection(CS))
     {
         SqlCommand cmd = new SqlCommand("spTest", con);
         cmd.CommandType = System.Data.CommandType.StoredProcedure;
         con.Open();
         cmd.ExecuteNonQuery();
         lblStatus.Text = "Tasks Deleted Successfully.";
     }
}

Upvotes: 1

Related Questions