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?

Andrew
  • 815
  • 2
  • 13
  • 33
  • Can you post the code for `dsChecklist_Updating`? – Tim Jul 05 '13 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. – Andrew Jul 05 '13 at 15:10
  • Please post the TSQL for `[Automation].[manual].[usp_TestLogInsert]`. – Hogan Jul 05 '13 at 15:24

1 Answers1

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>
Andrew
  • 815
  • 2
  • 13
  • 33