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();
}