SqlDataSource Procedure or function expects parameter which was not specified

Question

I have a SqlDataSource that I am attempting to add an UpdateCommand to so I can call a stored procedure when a GridView's update button is selected. I am doing this in code behind due to the fact that I need to call one of two procedures, depending on if the record already exists in the database table (INSERT for new, UPDATE for existing record). When I set the Updateparameters in the RowUpdating event, I get the following exception:

Procedure or function 'usp_TestLogInsert' expects parameter '@Status', which was not supplied.

Here is the ASP code and code behind for this. I am explicitly creating the parameters in the code behind, so I do not see why I get this exception. I have already verified that these are the same parameters needed for the stored procedure.

C#

protected void gvChecklist_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    SQLConnections sql = new SQLConnections();
    SqlDataSource dsChecklist = (SqlDataSource)LoginView1.FindControl("dsChecklist");
    var dd = (DropDownList)gvChecklist.Rows[e.RowIndex].FindControl("ddStatus");
    var status = dd.SelectedValue;
    var testID = sql.SQLSelectSingle(String.Format("SELECT ID FROM Automation.manual.Tests WHERE Task = '{0}'", (String)e.OldValues["Task"]), "pwautosql01");
    string user = Page.User.Identity.Name;
    string notes = (String)e.NewValues["Notes"];
    string jira = (String)e.NewValues["JiraTicket"];
    var dbID = e.NewValues["ID"];
    string build = "TODO";

    if (dbID == null) //Record does not exist in TestLog, INSERT a new one
    {
        dsChecklist.UpdateCommand = "[Automation].[manual].[usp_TestLogInsert]";
        dsChecklist.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
        dsChecklist.UpdateParameters.Add("@Status", TypeCode.Int32, status);
        dsChecklist.UpdateParameters.Add("@TestID", TypeCode.Int32, testID);
        dsChecklist.UpdateParameters.Add("@TestedBy", TypeCode.String, user);
        dsChecklist.UpdateParameters.Add("@Notes", TypeCode.String, notes);
        dsChecklist.UpdateParameters.Add("@JiraTicket", TypeCode.String, jira);
        dsChecklist.UpdateParameters.Add("@Build", TypeCode.String, build);

        dsChecklist.Update();
    }
    //else //Record already exists in TestLog. UPDATE
    //{
        //TODO
    //}
}

ASP

            <asp:UpdatePanel ID="upGridView" runat="server" UpdateMode="Conditional">
            <ContentTemplate>
            <asp:GridView ID="gvChecklist" runat="server" 
                    AutoGenerateColumns="false" DataSourceID="dsChecklist"
                    AutoGenerateEditButton="true" onrowupdating="gvChecklist_RowUpdating">
                <Columns>
                    <asp:TemplateField HeaderText="Status">
                        <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%#Eval("Status") %>' />
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="ddStatus" runat="server" DataTextField="Status" DataValueField="ID" DataSourceID="dsStatus" />
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="Division" 
                            HeaderText="Division"
                            readonly="true" />
                    <asp:BoundField DataField="Application"
                            HeaderText="Application"
                            readonly="true" />
                    <asp:BoundField DataField="Task"
                            HeaderText="Task"
                            readonly="true" />
                    <asp:BoundField DataField="TestedBy" HeaderText="Tested By" readonly="true"/>
                    <asp:BoundField DataField="Notes" HeaderText="Notes" ReadOnly="false"/>
                    <asp:BoundField DataField="JiraTicket"
                            HeaderText="JIRA Ticket"
                            readonly="false" />
                    <asp:BoundField DataField="ID" />
                </Columns>
            </asp:GridView>
            </ContentTemplate>
            <Triggers>
            <asp:PostBackTrigger ControlID="gvChecklist" />
        </Triggers>
            </asp:UpdatePanel>
            </td>
        </tr>
    </table>

    <asp:SqlDataSource ID="dsChecklist" SelectCommand=" SELECT l.ID, d.division, c.Context AS Application, t.Task, l.TestedBy, l.Notes, l.JiraTicket, s.Status
                                                        FROM Automation.manual.Tests t
                                                        OUTER APPLY 
                                                            (SELECT TOP 1 *
                                                            FROM Automation.manual.TestLog l
                                                            WHERE l.TestID = t.ID 
                                                            ORDER BY l.Date DESC) l
                                                        INNER JOIN Automation.dbo.Context c ON  c.ID = t.Context
                                                        INNER JOIN Automation.dbo.Division d ON d.ID = t.Division
                                                        LEFT OUTER JOIN Automation.manual.Status s ON s.ID = l.Status"
                                                       UpdateCommand=""
                       runat="server" 
                       ConnectionString="<%$ ConnectionStrings:AutomationDBConnectionString %>"  />

   <asp:SqlDataSource ID="dsStatus" SelectCommand="SELECT ID, Status FROM Automation.manual.Status"                               
                      runat="server" 
                      ConnectionString="<%$ ConnectionStrings:AutomationDBConnectionString %>">
                      </asp:SqlDataSource>

When you step through your code, does var status come back with an actual value? — Mike Marks
– Mike Marks, Commented Jul 3, 2013 at 19:53
Shot in the dark, but try casting it to an Int32 when you pull it from 'dd', assuming status is an 'int' in your table. I'm assuming it is supposed to be 'int' from your 'UpdateParameters.Add' method on 'status'. I wouldn't think this would be the issue because you have it as a var, but doesn't hurt to make sure. — Mike Marks
– Mike Marks, Commented Jul 3, 2013 at 19:56

HasaniH · Accepted Answer · 2013-07-04 13:29:31Z

4

You need to leave out the @ before the parameter names, the SqlDataSource adds it for you. See here

Also try adding the parameters when you instantiate the data source then setting their values in your code behind:

<UpdateParameters>
    <asp:Parameter Name="Status"   Type="Int32" />
    <asp:Parameter Name="TestID"  Type="Int32" />
    <asp:Parameter Name="TestedBy"    Type="String" />
    <asp:Parameter Name="Notes"       Type="String" />
    <asp:Parameter Name="JiraTicket"     Type="String" />
    <asp:Parameter Name="Build" Type="String" />
</UpdateParameters>

then:

if (dbID == null) //Record does not exist in TestLog, INSERT a new one
{
    dsChecklist.UpdateCommand = "[Automation].[manual].[usp_TestLogInsert]";
    dsChecklist.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
    dsChecklist.UpdateParameters["@Status"].Value = status;
    dsChecklist.UpdateParameters["@TestID"].Value = testID;
    dsChecklist.UpdateParameters["@TestedBy"].Value = user;
    dsChecklist.UpdateParameters["@Notes"].Value = notes;
    dsChecklist.UpdateParameters["@JiraTicket"].Value = jira;
    dsChecklist.UpdateParameters["@Build"].Value = build;

    dsChecklist.Update();
}

edited Jul 4, 2013 at 13:29

answered Jul 3, 2013 at 20:39

HasaniH

8,4326 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Andrew Over a year ago

This works and the stored procedure executes, I can see the values in the database table. But when the event exits, Procedure or function usp_TestLogInsert has too many arguments specified. is thrown.

HasaniH Over a year ago

You'll need to clear the parameters for subsequent calls or just update their values. You're probably adding them multiple times.

Andrew Over a year ago

When I call dsChecklist.UpdateParameters.Clear(); the original issue arises again. Where would I clear the parameters to prevent them from being added again?

HasaniH Over a year ago

Sorry you misunderstood. You can either clear the list after the call then add them again or leave them and update their values. The point is to ensure that when you're about to make the call you have the correct number of parameters.

Andrew Over a year ago

Could you add some example code by chance? I think I am still missing something. I am clearing the list right before the event finishes, which throws the exception.

|

Collectives™ on Stack Overflow

SqlDataSource Procedure or function expects parameter which was not specified

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related