3

I have a GridView that is bound to a SqlDataSource. I am running into issues with the UpdateCommand executing, it seems to be executing twice. When I step through the code in the gvChecklist_RowUpdating event, I see everything runs properly and the stored procedure is executed. I have verified the stored procedure works properly since there is a new record in the db table. When this event exits though, I get the following error:

Procedure or function usp_TestLogInsert has too many arguments specified.

I see that the Updating event on the datasource is being called after the RowUpdating event. I have tried to cancel this event to prevent the multiple update attempts, but then nothing happens and the GridView stays in edit mode.

ASP Code

<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"
                   runat="server"                                
                   ConnectionString="<%$ ConnectionStrings:AutomationDBConnectionString %>" 
                   onupdating="dsChecklist_Updating" >
  <UpdateParameters>
    <asp:Parameter Name="Status" DbType="Int32" />
    <asp:Parameter Name="TestID" DbType="Int32" />
    <asp:Parameter Name="TestedBy" DbType="String" />
    <asp:Parameter Name="Notes" DbType="String" />
    <asp:Parameter Name="JiraTicket" DbType="String" />
    <asp:Parameter Name="Build" DbType="String" />
  </UpdateParameters>
</asp:SqlDataSource>

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["Status"].DefaultValue = status;
        dsChecklist.UpdateParameters["TestID"].DefaultValue = testID;
        dsChecklist.UpdateParameters["TestedBy"].DefaultValue = user;
        dsChecklist.UpdateParameters["Notes"].DefaultValue = notes;
        dsChecklist.UpdateParameters["JiraTicket"].DefaultValue = jira;
        dsChecklist.UpdateParameters["Build"].DefaultValue = build;

        gvChecklist.DataBind();
    }
    //else //Record already exists in TestLog. UPDATE
    //{
    //TODO
    //}
}

EDIT: I used the SQL Profiler to see what parameters are being sent in the stored procedure. Even though the INSERT occurs and a new record is created, an extra parameter called ID is being sent. I never explicitly send this parameter, but it seems that it is created since I have a BoundField called ID in my GridView. To build on the original question, is there a way to make the UpdateCommand ignore this BoundField so it does no automatically use it as a parameter?

3
  • Can you post the code for dsChecklist_Updating? Commented Jul 5, 2013 at 15:07
  • That event is empty. I created it in the code to see if it was being hit. It appears that the UpdateCommand is executed in my RowUpdating event, but after the dsChecklist_Updating event is called. Commented Jul 5, 2013 at 15:10
  • Please post the TSQL for [Automation].[manual].[usp_TestLogInsert]. Commented Jul 5, 2013 at 15:24

1 Answer 1

1

After using the SQL Profiler, I saw that BoundField ID was being added as an UpdateCommand Parameter implicitly. Changing this field to a TemplateItem fixes this issue.

                       <asp:TemplateField>
                        <ItemTemplate>
                            <asp:TextBox ID="tbId" Text='<%#Eval("ID") %>' ReadOnly="true" runat="server"></asp:TextBox>
                        </ItemTemplate>
                       </asp:TemplateField>
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.