asked on

Deleting a record using a stored procedure

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

sunithnair

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 Romere

ASKER

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.

sunithnair

Did you not bing the column Original_ClaimNbr? I could not see it in the details view that is the problem i think

Carla Romere

ASKER

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.

CtrlAltDl

I don't think your delete event is ever taking place. Try adding AutoGenerateDeleteButton="true" to your detailsview.

Carla Romere

ASKER

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.

CtrlAltDl

You need to add a deleteparameter to your sqldatasource:

                <DeleteParameters>
                    <asp:Parameter Name="Original_ClaimNbr" />
                </DeleteParameters>

Open in new window

Carla Romere

ASKER

I already had that set up before I submitted the first question.

Carla Romere

ASKER

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?

ASKER CERTIFIED SOLUTION

Randy Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carla Romere

ASKER

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

The SelectedText was meant as an example... The correct PropertyName is "SelectedValue" I believe...

Carla Romere

ASKER

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 Romere

ASKER

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

Glad to see you got it working...