I get too many arguments when trying to delete a row from my database

Question

I created a database with simple stored procedures to delete, insert, select, and update records in a database of three tables. All of them work, except for my delete instructions. When I try, I get the message Procedure or function has too many arguments . I tried to delete one parameter that he had, and ended up deleting all the records in the table, not the one that I targeted. What am I doing wrong? I have a feeling that there is an error in my SQL script, but I do not know what I can do differently to make it work.

Message:

The procedure or function Delete_Special contains too many arguments.

My SQL script:

 CREATE PROCEDURE [Delete_Special] @ThisID INT AS DELETE FROM [Daily_Specials] WHERE @ThisID = [ID] GO 

Event that invokes the stored procedure:

 Protected Sub BTN_DeleteEvt_Click(sender As Object, e As EventArgs) SQL_Specials.Delete() End Sub 

Abbreviated markup:

 <asp:SqlDataSource ID="SQL_Specials" runat="server" DeleteCommand="Delete_Special" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:ControlParameter ControlID="GV_Eagles_Specials" Name="ThisID" PropertyName="SelectedIndex" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> <asp:GridView ID="GV_Eagles_Specials" runat="server" DataSourceID="SQL_Specials" AutoGenerateColumns="False"> <Columns> <asp:TemplateField> <ItemTemplate> <asp:Button ID="BTN_EditSpecial" runat="server" CssClass="BigText" Text="Edit" OnClick="BTN_EditEvent_Click" /> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" HtmlEncode="False" DataFormatString="{0:MM/dd/yyyy}" /> <asp:BoundField DataField="Special" HeaderText="Special" SortExpression="Special" HtmlEncode="False" /> <asp:BoundField DataField="Side" HeaderText="Side" SortExpression="Side" HtmlEncode="False" /> <asp:BoundField DataField="Special_Price" HeaderText="Special Price" SortExpression="Special_Price" HtmlEncode="False" /> <asp:BoundField DataField="Soup" HeaderText="Soup" SortExpression="Soup" HtmlEncode="False" /> <asp:BoundField DataField="Soup_Price" HeaderText="Soup Price" SortExpression="Soup_Price" HtmlEncode="False" /> <asp:TemplateField ShowHeader="False"> <ItemTemplate> <asp:Button ID="BTN_DeleteEvt" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" CssClass="BigText" OnClick="BTN_DeleteEvt_Click" /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> 

Answer 1

It seems the problem is not in SQL Server. I was browsing MSDN for this problem and I found this

In the answer:

So, I am caving in with the stored proc, as it looks like an error is coming from here. One interesting thing that I notice is that the stored proc works fine with SQL Query Analyzer and returns the number of the auto-generated row as he intended. However, when I start from Visual Studio, I get the same @Identity error, and the saved process does not add the table row and does not return the row number.

I take a copy of Robert Vieira's SQL Server 2000 Professional Programming. The good 'ol Roberto should say on page 367:

"You must use the OUTPUT keyword when you call sproc, just like you did when you declared sproc. This gives SQL Server a warning about the special handling of this parameter. The OUTPUT keyword will not generate a runtime error, but a value for the output parameter will not be moved to your variable (you just end up with what was already there - most likely a NULL value) ... "

Since this is very similar to the @Identity error I was getting, I looked more closely at the @Identity definition in a stored proc, assuming something doesn’t let SQL Server know in advance that the stored process has an OUTPUT return value. The initialization variable dialog box that appears when the stored process starts has a drop-down list to initialize the @Identity value, which seems weirder because it is an OUTPUT variable. Two parameters: <DEFAULT> (the default value in the drop-down list) and <NULL> .

As a result of the lack of ideas than any rational thought process, I change <DEFAULT> to <NULL> in the dialog box and start the saved process.

Answer 2

Your request accepts an input parameter ( ThisID ), but you do not invoke a request with it.

You need to specify which ThisID you want to remove, and you need to analyze it in your function of removing from the GridView element.

Answer 3

I think you have your Where statement back

 CREATE PROCEDURE [Delete_Special] @ThisID INT AS DELETE FROM [Daily_Specials] WHERE @ThisID = [ID] GO 

I think you might want it like this instead

 CREATE PROCEDURE [Delete_Special] @ThisID INT AS DELETE FROM [Daily_Specials] WHERE [ID] = @ThisID GO 

the parameter is what you want to combine in the Where statement.

you want every entry in which the ID column is mapped to the @ThisID parameter to be deleted.