We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Deleting a record using a stored procedure

Carla Romere
Carla Romere asked
on
Medium Priority
1,677 Views
Last Modified: 2012-06-21
Have a stored procedure that copies a record to a new table, then deletes the record from the original table. When I run the sproc in SQL Server Development Studio, it runs correctly, inserts the record and then removes the record from the original table.

However, when I implement the stored procedure in my asp.net page, it doesn't delete the record, nor does it insert the record into the new table.

Stored Procedure partially included below.
SQL .net Parameters included below.
STORED PROC
ALTER PROCEDURE [dbo].[cl_DeleteClaim]
	@Original_ClaimNbr int
 
AS
BEGIN
SET NOCOUNT ON;
 
INSERT INTO [TagIt].[dbo].[CL_Status_Data_Deleted]
           ([ClaimNbr]
           ,[DateEntered]
           ,[create_userid]
<several fields snipped>
             )
     
SELECT * FROM CL_Status_Data
WHERE ClaimNbr=@Original_ClaimNbr
 
DELETE FROM [TagIt].[dbo].[CL_Status_Data]
    WHERE ClaimNbr=@Original_ClaimNbr
END
 
ASP.NET CODE
 
<asp:SqlDataSource 
	ID="SqlDataSource1" 
	runat="server" 
	ConnectionString="<%$ ConnectionStrings:TagIt %>"
	SelectCommand="SELECT DISTINCT [ClaimNbr], [FirstName], [LastName], [Address], [City], [St], [Zip] FROM [CL_Status_Data] WHERE ([ClaimNbr] = @ClaimNbr)" 
	DeleteCommand="cl_DeleteClaim"  
	DeleteCommandType="StoredProcedure">
<SelectParameters>
	<asp:ControlParameter ControlID="txtClaimNbr" Name="ClaimNbr" PropertyName="Text"
		Type="Int32" />
</SelectParameters>
<DeleteParameters>
                 <asp:Parameter Name="Original_ClaimNbr" Type="int32" />
</DeleteParameters>
</asp:SqlDataSource>
 
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Width="345px" OnItemDeleted="DetailsView1_ItemDeleted">
<Fields>
<asp:BoundField DataField="ClaimNbr" HeaderText="ClaimNbr" InsertVisible="False"
ReadOnly="True" SortExpression="ClaimNbr" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="St" HeaderText="St" SortExpression="St" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
<asp:CommandField ShowDeleteButton="True" ButtonType="button" />
</Fields>
</asp:DetailsView>

Open in new window

Comment
Watch Question

Can you try running sql profiler and see what is the sql query executed when you press the delete command in the aspx page? I suspect it is not getting the parameter..
Carla RomereDirector of Information Technology

Author

Commented:
Okay - that is the problem. Now, I can't figure out WHY it's not receiving the parameter. I have a textbox on the page where the user puts in the id they want to delete. DetailsView pulls it up correctly. Within the DetailsView is the Delete button.

exec cl_DeleteClaim @Original_ClaimNbr = NULL

So I know the sproc is firing - but isn't getting the value from the DetailsView selected record/value from the textbox.
Did you not bing the column Original_ClaimNbr? I could not see it in the details view that is the problem i think
Carla RomereDirector of Information Technology

Author

Commented:
The data field in the details view is "ClaimNbr". The parameter is "Original_ClaimNbr. I copied an example I found on the 'net and thought I named everything correctly, but maybe not.
I don't think your delete event is ever taking place.  Try adding AutoGenerateDeleteButton="true" to your detailsview.
Carla RomereDirector of Information Technology

Author

Commented:
I added AutoGenerateDeleteButton="true" as you suggested. That didn't seem to make a difference. It's still not picking up the value of the selected record in the DetailsView. I know it's probably something simple I'm missing here, but it's escaping me.
You need to add a deleteparameter to your sqldatasource:
                <DeleteParameters>
                    <asp:Parameter Name="Original_ClaimNbr" />
                </DeleteParameters>

Open in new window

Carla RomereDirector of Information Technology

Author

Commented:
I already had that set up before I submitted the first question.
Carla RomereDirector of Information Technology

Author

Commented:
Okay - in reviewing this issue again, where does the sproc learn that the txtClaimNbr field contents is the value it should use for the @Original_ClaimNbr parameter?
.Net Developer
Commented:
You have to set the Delete Parameter up like you have the Select Parameter to get it's value from a control, in this case the Details View


      


Change this:


                 


To this:


                 


Have to have the ClaimNbr column set as a key to the details view....

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Carla RomereDirector of Information Technology

Author

Commented:
DataKeyNames="ClaimNbr" added and re-ran the page. That throws this error:

DataBinding: 'System.Web.UI.WebControls.DetailsView' does not contain a property with the name 'SelectedText'.
Randy Wilson.Net Developer

Commented:
The SelectedText was meant as an example...  The correct PropertyName is "SelectedValue" I believe...
Carla RomereDirector of Information Technology

Author

Commented:
Okay - making progress here. Now I get this error:
Procedure or function cl_DeleteClaim has too many arguments specified.

In SQL Server Profiler, I get this comment:
exec cl_DeleteClaim @Original_ClaimNbr = 17978, @ClaimNbr = 17978

So I'm getting the parameter passed now, but it's picking up the SelectParameter AND the DeleteParameter. In the sproc, the only parameter defined is @Original_ClaimNbr.
Carla RomereDirector of Information Technology

Author

Commented:
Okay- it's working now. Here's what I ended up having to do. I had to set up the SelectParameter and the DeleteParameter identically and modify the sproc to reflect that.

<asp:SqlDataSource
      ID="SqlDataSource1"
      runat="server"
      ConnectionString="<%$ ConnectionStrings:TagIt %>"
      SelectCommand="SELECT DISTINCT [ClaimNbr], [FirstName], [LastName], [Address], [City], [St], [Zip] FROM [CL_Status_Data] WHERE ([ClaimNbr] = @ClaimNbr)"

      DeleteCommand="cl_DeleteClaim"  
      DeleteCommandType="StoredProcedure">

<SelectParameters>
      <asp:ControlParameter ControlID="txtClaimNbr" Name="ClaimNbr" PropertyName="Text" Type="Int32" />
</SelectParameters>

<DeleteParameters>
<asp:ControlParameter ControlID="txtClaimNbr" Name="ClaimNbr" PropertyName="Text" Type="Int32" />
</DeleteParameters>

</asp:SqlDataSource>

It now executes the sproc, deleting the correct row, and inserting that row into the other table.
Randy Wilson.Net Developer

Commented:
Glad to see you got it working...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.