CODEWITHSUNDEEP
asp.netsql-servervb.nett-sql
Shortstuff81000
Shortstuff81000

Reputation: 479

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

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

The message:

Procedure or function Delete_Special has too many arguments specified.

My SQL script:

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

The event that calls the stored procedure:

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

The abridged 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>

Upvotes: 5

Views: 396

Answers (3)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

Your query takes an input parameter (ThisID) but you do not call the query with it.

You need to specify which ThisID you want to delete, and you need to parse it into your delete function from your GridView's item.

Upvotes: 1

Phrancis
Phrancis

Reputation: 2280

Looks like the problem is not with SQL Server. I was browsing MSDN for this problem and I found this

In the answer:

So, I'm thrashing around with the stored proc since it looks like the error is originating from there. One interesting thing that I notice is that the stored proc runs just fine from SQL Query Analyzer and returns the autogenerated row number like it's supposed to. However, when run from within Visual Studio, I get the same @Identity error and the stored proc neither adds a table row nor returns a row number.

I borrow a copy of Professional SQL Server 2000 Programming by Robert Vieira. Good 'ol Roberto has the following to say on page 367:

"You must use the OUTPUT keyword when you call the sproc, much as you did when you declared the sproc. This gives SQL Server advance warning about the special handling that parameter will require. Be aware, however, that forgetting to include the OUTPUT keyword won't create a runtime error, but the value for the output parameter won't be moved into your variable (you'll just wind up with what was already there - most likely a NULL value). ..."

Since this sounded a lot like the @Identity error I was getting, I took a closer look at the @Identity definition in the stored proc on the assumption that something is failing to let SQL Server know in advance that the stored proc has an OUTPUT return value. The variable initialization dialog box that pops up when you run the stored proc has a dropdown box for initializing the value of @Identity which seems wierd because it's an OUTPUT variable. The two options are <DEFAULT> (the default setting in the dropdown) and <NULL>.

More as a result of being out of ideas than any rational thought process, I change <DEFAULT> to <NULL> in the dialog box and run the stored proc.

Upvotes: 3

Malachi
Malachi

Reputation: 3231

I think that you have your Where statement backwards

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

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

you want every record where the ID column matches the Parameter @ThisID to be deleted.

Upvotes: 1

Related Questions