Friday, February 24, 2012

Delete SP not firing?

I cannot get this event to fire. I am using TextBox9 to see if statements are processed and it never fills so it appears the Button4_Click never happens. Any ideas?

Thank you,

<asp:ButtonID="Button4"runat="server"OnClick="Button4_Click"Text="Delete Submission"/>

protected void Button4_Click(object sender, EventArgs e)
{
string CompanyDeleteID = TextBox10.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);
SqlCommand cmd = new SqlCommand("DeleteSubmission", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.C_ID", CompanyDeleteID);
TextBox9.Text ="SP completed";


}

PROCEDURE dbo.DeleteSubmission
@.C_ID int
AS
BEGIN
DELETE
FROM tblCompanyInfo_Submit
WHERE C_ID = @.C_ID

DELETE
FROM tblStoreStudioSubmit
WHERE C_ID = @.C_ID

DELETE
FROM tblContractorSubmit
WHERE C_ID = @.C_ID

RETURN
ENDWHERE CD_ID = @.C_ID
RETURN

Hi,

Is your event contained within a <script> tag in your aspx or in the code behind?

|||

Code Behind - however it is now firing once I removed Causes Validation from the Button4 control but the rows are not being removed. It looks like it processes and the Test textbox acknowledges the SP call is being made. Seems like it may be getting rolled back.

|||

Is your stored procedure working without error? You could test it through query window and see.

|||

I see this above. The bold section looks a little out of whack to me.

PROCEDURE dbo.DeleteSubmission
@.C_ID int
AS
BEGIN
DELETE
FROM tblCompanyInfo_Submit
WHERE C_ID = @.C_ID

DELETE
FROM tblStoreStudioSubmit
WHERE C_ID = @.C_ID

DELETE
FROM tblContractorSubmit
WHERE C_ID = @.C_ID

RETURN
ENDWHERE CD_ID = @.C_ID
RETURN

|||

Nevermind, I see. You are not actually executing your command. ie cmd.ExecuteNonQuery

|||

copy and paste error - actually ends at the END. The SP works in when called from directly in Visual Studio from the Server Explorer but does not successfully remove the rows when called from the page.

|||

Sorry, try this.

protected void Button4_Click(object sender, EventArgs e)
{
string CompanyDeleteID = TextBox10.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);
SqlCommand cmd = new SqlCommand("DeleteSubmission", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.C_ID", CompanyDeleteID);

cmd.ExecuteNonQuery();
TextBox9.Text ="SP completed";

}

|||

You are right. Tried the cmd.ExecuteNonQuery(); but it complained that a connection was not opened. So I installed a try, catch, finally block. Still looks like the SP is processing but the rows are either not being removed or are be rolled back.

string CompanyDeleteID = TextBox10.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);
SqlCommand cmd = new SqlCommand("DeleteSubmission", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.C_ID", CompanyDeleteID);
try
{
con.Open();
}
catch (SqlException exc)
{
TextBox4.Text += string.Format("Error: {0}<br/>", exc.Message);
}
finally
{
con.Close();
TextBox9.Text = "SP finished";
}

|||

I still don't see you executing the command. Try this.

string CompanyDeleteID = TextBox10.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);
SqlCommand cmd = new SqlCommand("DeleteSubmission", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.C_ID", CompanyDeleteID);
try
{
con.Open();

cmd.ExecuteNonQuery();
}
catch (SqlException exc)
{
TextBox4.Text += string.Format("Error: {0}<br/>", exc.Message);
}
finally
{
con.Close();
TextBox9.Text = "SP finished";
}

|||

I must be blind - thanks for your patience.

|||

No worries, I apologize for not seeing the two issues to begin with.

I'm glad I could help,

Best of Luck!

No comments:

Post a Comment