[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deleting a record using a stored procedure

Posted on 2009-02-12
15
Medium Priority
?
1,590 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

0
Comment
Question by:Carla Romere
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 16

Expert Comment

by:sunithnair
ID: 23624753
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..
0
 

Author Comment

by:Carla Romere
ID: 23624851
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.
0
 
LVL 16

Expert Comment

by:sunithnair
ID: 23624882
Did you not bing the column Original_ClaimNbr? I could not see it in the details view that is the problem i think
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Carla Romere
ID: 23625461
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.
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 23625602
I don't think your delete event is ever taking place.  Try adding AutoGenerateDeleteButton="true" to your detailsview.
0
 

Author Comment

by:Carla Romere
ID: 23625770
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.
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 23625820
You need to add a deleteparameter to your sqldatasource:
                <DeleteParameters>
                    <asp:Parameter Name="Original_ClaimNbr" />
                </DeleteParameters>

Open in new window

0
 

Author Comment

by:Carla Romere
ID: 23625857
I already had that set up before I submitted the first question.
0
 

Author Comment

by:Carla Romere
ID: 23626013
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?
0
 
LVL 7

Accepted Solution

by:
Randy Wilson earned 2000 total points
ID: 23626118
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....

0
 

Author Comment

by:Carla Romere
ID: 23626478
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'.
0
 
LVL 7

Expert Comment

by:Randy Wilson
ID: 23626582
The SelectedText was meant as an example...  The correct PropertyName is "SelectedValue" I believe...
0
 

Author Comment

by:Carla Romere
ID: 23626700
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.
0
 

Author Comment

by:Carla Romere
ID: 23626916
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.
0
 
LVL 7

Expert Comment

by:Randy Wilson
ID: 23632073
Glad to see you got it working...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question