SqlDataSource过程或函数需要未指定的参数

本文关键字:未指定 参数 过程 函数 SqlDataSource | 更新日期: 2023-09-27 18:20:58

我有一个SqlDataSource,我正试图向其添加UpdateCommand,以便在选择GridView的更新按钮时可以调用存储过程。由于需要调用两个过程中的一个,这取决于记录是否已经存在于数据库表中(INSERT表示新记录,UPDATE表示现有记录),因此我在代码隐藏中执行此操作。当我在RowUpdating事件中设置Updateparameters时,我得到以下异常:

过程或函数"usp_TestLogInsert"需要参数"@Status",但未提供该参数

这里是ASP代码和代码背后的这一点。我在后面的代码中显式地创建参数,所以我不明白为什么会出现这个异常。我已经验证了这些参数与存储过程所需的参数相同。

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>

SqlDataSource过程或函数需要未指定的参数

您需要在参数名称之前去掉@,SqlDataSource会为您添加它。参见此处

当您实例化数据源时,也可以尝试添加参数,然后在代码后面设置它们的值:

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

然后:

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();
}