Procedure or function 'xyz' has too many arguments specified

Question

I know this type of question has been asked at least a dozen times on SO, but I've gone through all the previous questions and answers I could find here and none that I have found apply. I am using several FormView objects on my page and now want to add the option to edit one of them. The FormView is populated by SqlDataSource linked to a stored procedure, so I made a stored procedure to update the records and added it to the SqlDataSource, which now looks like this:

<asp:SqlDataSource ID="TestSqlDataSource" runat="server" 
                   SelectCommandType="StoredProcedure" 
                   ConnectionString="<%$ ConnectionStrings:development %>" 
                   SelectCommand="usp_GetNewTestDetails" 
                   UpdateCommand="usp_UpdateTest" 
                   UpdateCommandType="StoredProcedure" 
                   onupdating="TestSqlDataSource_Updating">
    <SelectParameters>
        <asp:SessionParameter SessionField="TestID" Name="TestID"/>
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="testId" Type="Int32"/>
        <asp:Parameter Name="testerLicense" Type="Int32" />
        <asp:Parameter Name="premiseOwner" Type="String" />
        <asp:Parameter Name="premiseAddress" Type="String" />
        <asp:Parameter Name="premiseCity" Type="String" />
        <asp:Parameter Name="premiseState" Type="String" />
        <asp:Parameter Name="premiseZip" Type="String" />
        <asp:Parameter Name="protectionType" Type="String" />
        <asp:Parameter Name="testType" Type="String" />
        <asp:Parameter Name="repairs" Type="String" />
        <asp:Parameter Name="notes" Type="String" />
        <asp:Parameter Name="testDate" Type="DateTime" />
        <asp:Parameter Name="employerName" Type="String" />
        <asp:Parameter Name="employerAddress" Type="String" />
        <asp:Parameter Name="phoneNumber" Type="String" />
        <asp:Parameter Name="emailAddress" Type="String" />
        <asp:Parameter Name="dataEntryName" Type="String" />
        <asp:Parameter Name="dataEntryPhone" Type="String" />
        <asp:Parameter Name="signature" Type="String" />
        <asp:Parameter Name="entryDate" Type="DateTime" />
    </UpdateParameters>
</asp:SqlDataSource>

When I attempt to update a record, I'm getting an error that says there are too many parameters in my query. I have triple checked that the parameters in my code match exactly (name, number, and even order) to the ones in my stored procedure.

In attempting to find answers, I came across this post which referenced this site (had to go to archive.org because the original appears to be down). I applied what they suggested for adding the parameters to the trace function. What I found was that in my stored procedure, there are a few "lookup" values from the select procedure that aren't in the update procedure. The table I'm trying to update only has the ID of the tester, but the users would like to see their name as well, so it references that table, but in the FormView those fields are read-only during edit mode so they can't attempt to change it. All the other parameters line up... only those lookup values are off.

I thought that by including the specific list of parameters to pass that it would only use those parameters, but that appears to be incorrect. Now I'm stumped because I have already specified the parameters, and I can't see anywhere that they are being overwritten with the ones from the select stored procedure. Where do I tell it not to use those read-only values?

I don't want to remove them from the original stored procedure because they will be needed by the users. The one workaround that I have come up with so far is to add them to the stored procedure and then just never use them. Although I'm fairly certain this would work, it is very much just covering up the problem rather than fixing it.

---

EDIT: Additional code, per request

This is the method that outputs the parameters to the trace function:

protected void TestSqlDataSource_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    for (int i = 0; i < e.Command.Parameters.Count; i++)
    {
        Trace.Write(e.Command.Parameters[i].ParameterName);
        if (e.Command.Parameters[i].Value != null)
        {
            Trace.Write(e.Command.Parameters[i].Value.ToString());
        }
    }
}

---

This is the stored procedure used for updating:

ALTER PROCEDURE [dbo].[usp_UpdateTest]
    @testId INT ,
    @testerLicense INT ,
    @premiseOwner VARCHAR(50) ,
    @premiseAddress VARCHAR(150) ,
    @premiseCity VARCHAR(50) ,
    @premiseState VARCHAR(2) ,
    @premiseZip VARCHAR(10) ,
    @protectionType VARCHAR(11) ,
    @testType VARCHAR(2) ,
    @repairs VARCHAR(200) ,
    @notes VARCHAR(300) ,
    @testDate DATETIME ,
    @employerName VARCHAR(50) ,
    @employerAddress VARCHAR(150) ,
    @phoneNumber VARCHAR(25) ,
    @emailAddress VARCHAR(60) ,
    @dataEntryName VARCHAR(50) ,
    @dataEntryPhone VARCHAR(25) ,
    @signature VARCHAR(50) ,
    @entryDate DATETIME
AS 
    BEGIN
        SET NOCOUNT ON;

        UPDATE  dbo.Tests
        SET     TesterLicense = @testerLicense ,
                PremiseOwner = @premiseOwner ,
                PremiseAddress = @premiseAddress ,
                PremiseCity = @premiseCity ,
                PremiseState = @premiseState ,
                PremiseZip = @premiseZip ,
                ProtectionType = @protectionType ,
                TestType = @testType ,
                Repairs = @repairs ,
                Notes = @notes ,
                TestDate = @testDate ,
                EmployerName = @employerName ,
                EmployerAddress = @employerAddress ,
                PhoneNumber = @phoneNumber ,
                EmailAddress = @emailAddress ,
                DataEntryName = @dataEntryName ,
                DataEntryPhone = @dataEntryPhone ,
                Signature = @signature ,
                EntryDate = @entryDate 
        WHERE TestID = @testId
    END

Answer 1

Ended up figuring this out on my own. It was related to the fact that Visual Studio created the templates for me based on the stored procedure's schema. When the templates are autogenerated, every textbox in the <EditItemTemplate> section gets generated like this:

<asp:TextBox ID="TestIDTextBox" runat="server" Text='<%# Bind("TestID") %>'/>

Turns out that it is the Bind statement that caused this. VS automatically creates a parameter for any field populated using Bind. Although I was led to believe that the <UpdateParameters> section should have overridden this, it was actually the other way around. I was able to prevent these few read-only fields from passing back their parameters by changing the code on those fields from Bind to Eval:

<asp:TextBox ID="TestIDTextBox" runat="server" Text='<%# Eval("TestID") %>'/>

Works perfectly now.

---

Addendum:

Found that this causes the same problem in the other direction as well. If you mark a field you don't want edited as Eval but it needs to be included in the update (such as a row ID) it will not work, this time for too few parameters. I'm wondering now what the purpose of the <UpdateParameters> section even is, since it doesn't seem to follow anything in there...

Answer 2

You can delete unwanted parameters during e.g OnDeleting event (e.Command.Parameters.RemoveAt(i)).