question

XanderTodor-5936 avatar image
0 Votes"
XanderTodor-5936 asked YijingSun-MSFT answered

Delete Stored Procedure not finding Id in Editable GridView

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

 }

dotnet-csharpdotnet-aspnet-webforms
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered

Hi @XanderTodor-5936,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

rebin avatar image
0 Votes"
rebin answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hiya. Sorry, I copied the code from elsewhare. It is lblFunderId.

0 Votes 0 ·

In your `_Deleting event handler is that code correct? SQL parameters must start with an @ and yours doesn't. I would expect it to fail.

0 Votes 0 ·
XanderTodor-5936 avatar image
0 Votes"
XanderTodor-5936 answered Viorel-1 edited

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>


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe it should be QueryStringField="FunderId"?

(In case of controls you probably must use <asp:ControlParameter>).

0 Votes 0 ·