Delete Stored Procedure not finding Id in Editable GridView

Question

I have a fairly simple stored procedure that drops an Id when the Administrator does not need it. I use it on the front end of my web app.

This is the T-SQL:

ALTER  PROCEDURE [dbo].[spGRNTSFunderId_Delete]
@FunderId int = NULL 
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM tblGRNTSFunders WHERE ([FunderId] = @FunderId AND @FunderId IS NOT NULL) END

I am only trying to use the front-end to call this form a Button within the GridView as this is how I've done it on the other pages.

<asp:LinkButton runat="server" ID="lbtnDelete" CommandName="Delete"></asp:LinkButton>

The delete is in the SDS declaration.

<asp:SqlDataSource ID="sdsFunders_Page" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseLive %>"
        SelectCommandType="StoredProcedure"
        SelectCommand="spGRNTSFunders_Select"
        DeleteCommandType="StoredProcedure"
        DeleteCommand="spGRNTSFunders_Delete">
        <SelectParameters>
            <asp:QueryStringParameter DefaultValue="0" Name="FunderId" QueryStringField="FunderId" Type="Int32" ConvertEmptyStringToNull="true" />
        </SelectParameters></asp:SqlDataSource>

Checking the SQL Profiler, the command that executes on Delete is exec spFunderId_Delete but no @FunderId is called. It is supposed to be spFunderId_Delete @FunderId=someId

I tried coding it but it also does not work. It finds the Id though.. This is what I tried.

protected void gvFunders_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    string procedureDelete = "spGRNTSFunders_Delete";

    int index = gvFunders.EditIndex;
    GridViewRow row = gvWorkPeriods_WP.Rows[index];

    // find wp ID
    Label lblFunderId = (Label)row.FindControl("lblFunderId");
    string FunderId = lblFunderId.Text;

    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = procedureDelete;

    cmd.Parameters.AddWithValue("FunderId", FunderId);
    gvFunders_WP.DataBind();

}

Answer 1

Hi @Xander Todor ,

After adding delete parametes, it will now execute the SP and find the FunderId but it will always be NULL, so I have almost solved it.

It must know which row and id.Otherwise,it can't know delete which id. So, it will null. I suggest you could do like this:

    `sqlCommand.Parameters.AddWithValue("@FunderId", Convert.ToInt32(id.Text))`  

Best regards,
Yijing Sun

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

After adding delete parametes, it will now execute the SP and find the FunderId but it will always be NULL, so I have almost solved it.

<DeleteParameters>
    <asp:QueryStringParameter Name="FunderId" QueryStringField="lblFunderId" Type="Int32"  />
</DeleteParameters>

Answer 3

In your QueryStringParameter you are specifying the QueryStringField as lblWorkPeriodId. Shouldn't it be lblFunderId?

Also I would not recommend your IS NULL check in your delete sproc. It doesn't do anything really. If no ID is passed then the parameter is NULL. NULL doesn't equate to anything in SQL unless you use IS (NOT) NULL. So the condition is not providing any value.

Answer 4

Why you don't use SqlDataSource to perform CURD operations instead ADO.NET inside the RowDeleting event ? try this

create table tblGRNTSFunders  (
    FunderId INT,
    first_name VARCHAR(50),
    kast_name VARCHAR(50)
);

GO

insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (1, 'Baudoin', 'Dedenham');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (2, 'Deena', 'Ferber');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (3, 'Tuesday', 'Hanhart');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (4, 'Arlyne', 'Gilli');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (5, 'Lizbeth', 'Rouff');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (6, 'Rayshell', 'Whimper');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (7, 'Brinna', 'Eneas');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (8, 'Aile', 'Elvins');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (9, 'Evvie', 'Bomb');
insert into tblGRNTSFunders  (FunderId, first_name, kast_name) values (10, 'Lara', 'Vogeler');






CREATE PROC spGRNTSFunderId_Delete
 @FunderId int
 AS
 SET NOCOUNT ON;
 IF @FunderId IS NOT NULL
 DELETE FROM tblGRNTSFunders WHERE (FunderId = @FunderId)



 protected void gvFunders_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

            GridViewRow row = gvFunders.Rows[e.RowIndex];
            Label lblFunderId = (Label)row.FindControl("lblFunderId");


            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["microsoftQADBConnectionString"].ConnectionString.ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {


                    cmd.CommandText = "spGRNTSFunderId_Delete";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConnection1;
                    cmd.Parameters.AddWithValue("@FunderId", Convert.ToInt32(lblFunderId.Text));
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();


                }
            }



        }