Why this column cannot be updated using DropDownList inside the GridView?

Question

I have the following database design:

Employee Table: Username, Name, JobTitle, BadgeNo, IsActive, DivisionCode
Divisions Table: SapCode, DivisionShortcut

(DivisionCode is a foreign key to the SapCode in the Divisions Table)

And I have a GridView that I am using it to add, delete and update/edit the employees information. This information is employee Username, Name, BadgeNo, JobTitle, IsActive and the DivisionShortcut. The Divisions will be listed in DropDownList. The problem now is: with updating the division of the employee. I wrote the code but I got the following error: *

Invalid column name 'DivisionShortcut'.

ASP.NET Code:

    <asp:TemplateField HeaderText="Division">
        <ItemTemplate>
            <%# Eval("DivisionShortcut")%>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:DropDownList ID="DivisionsList" runat="server" DataSourceID="DivisionsListDataSource"
                                DataTextField="DivisionShortcut" DataValueField="SapCode">
            </asp:DropDownList>
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:BoundField DataField="Username" HeaderText="Network ID" ReadOnly="True" 
        SortExpression="Username" />

    <asp:TemplateField HeaderText="Name">
        <ItemTemplate>
            <%# Eval("Name")%>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtEmployeeName" runat="server" Text='<%# Bind("Name")%>' />
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Job Title">
        <ItemTemplate>
            <%# Eval("JobTitle")%>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtJobTitle" runat="server" Text='<%# Bind("JobTitle")%>' />
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Badge No.">
        <ItemTemplate>
            <%# Eval("BadgeNo")%>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtBadgeNo" runat="server" Text='<%# Bind("BadgeNo")%>' />
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Is Active?">
        <ItemTemplate>
            <%# Eval("IsActive")%>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:CheckBox ID="isActive" runat="server" 
                          Checked='<%# Eval("IsActive").ToString().Equals("True") %>'/>
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Delete?">
        <ItemTemplate>
            <span onclick="return confirm('Are you sure to Delete the record?')">
                <asp:ImageButton ID="lnkB" runat="server" ImageUrl="Images/icons/delete24.png" CommandName="Delete" />
            </span>
        </ItemTemplate>
    </asp:TemplateField>
</Columns>

"

SelectCommand="SELECT     dbo.Divisions.DivisionShortcut, dbo.employee.Username, dbo.employee.Name, dbo.employee.JobTitle, dbo.employee.BadgeNo, dbo.employee.IsActive
                FROM         dbo.Divisions INNER JOIN
                            dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode"
UpdateCommand="UPDATE [employee] SET [Name] = @Name, [JobTitle] = @JobTitle, 
                                    [BadgeNo] = @BadgeNo WHERE [Username] = @Username"
DeleteCommand="DELETE FROM [employee] WHERE [Username] = @Username">
<UpdateParameters>
    <asp:Parameter Name="Name" Type="String"  />
    <asp:Parameter Name="JobTitle" Type="String" />
    <asp:Parameter Name="BadgeNo" Type="String" />
    <asp:Parameter Name="Username" Type="String" />
</UpdateParameters>
<DeleteParameters>
    <asp:Parameter Name="Username" Type="String" />
</DeleteParameters>

" SelectCommand="SELECT * FROM Divisions">

Code-Behind: //For editing any row in the GridView protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; }

//For canceling any editng in any row in the GridView
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    e.Cancel = true;
    GridView1.EditIndex = -1;
}

//For updating the information in any row in the GridView
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow gvrow = GridView1.Rows[e.RowIndex];

    DropDownList DivisionsList = (DropDownList)gvrow.FindControl("DivisionsList"); 

    TextBox txtEmployeeName = (TextBox)gvrow.FindControl("txtEmployeeName");
    TextBox txtJobTitle = (TextBox)gvrow.FindControl("txtJobTitle");
    TextBox txtBadgeNo = (TextBox)gvrow.FindControl("txtBadgeNo");

    CheckBox isActive = (CheckBox)gvrow.FindControl("isActive");

    //For getting the ID (primary key) of that row
    string username = GridView1.DataKeys[e.RowIndex].Value.ToString();

    string name = txtEmployeeName.Text;
    string jobTitle = txtJobTitle.Text;
    string badgeNo = txtBadgeNo.Text;
    string division = DivisionsList.SelectedValue.ToString();

    UpdateEmployeeInfo(username, name, jobTitle, badgeNo, division);
}


private void UpdateEmployeeInfo(string username, string name, string jobTitle, string badgeNo, string division)
{
    string connString = ConfigurationManager.ConnectionStrings["UsersInfoDBConnectionString"].ConnectionString;
    SqlConnection conn = new SqlConnection(connString);
    string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
                        BadgeNo = @BadgeNo, DivisionShortcut = @division WHERE Username = @Username; 
                        UPDATE Divisions SET [DivisionShortcut] = @division WHERE SapCode = @SapCode;";

    SqlCommand cmd = new SqlCommand(update, conn);

    cmd.Parameters.AddWithValue("@Name", name);
    cmd.Parameters.AddWithValue("@JobTitle", jobTitle);
    cmd.Parameters.AddWithValue("@BadgeNo", badgeNo);
    cmd.Parameters.AddWithValue("@division", division);
    cmd.Parameters.AddWithValue("@Username", username);
    //cmd.Parameters.AddWithValue("@IsActive", isActive.checked);

    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        GridView1.EditIndex = -1;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cmd.Dispose();
        conn.Close();
        conn.Dispose();
    }
    GridView1.DataBind();
}

So how I can be able to update the Divivsion field for the employee using the DropDownList inside the GridView?

UPDATE:

Here's my updated code and still nothing is working with me: ASP.NET Code:

<asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
            AutoGenerateColumns="False" DataKeyNames="Username" 
            DataSourceID="SqlDataSource1" BorderWidth="1px" BackColor="#DEBA84" 
             CellPadding="3" CellSpacing="2" BorderStyle="None" 
             BorderColor="#DEBA84">
            <FooterStyle ForeColor="#8C4510" 
              BackColor="#F7DFB5"></FooterStyle>
            <PagerStyle ForeColor="#8C4510" 
              HorizontalAlign="Center"></PagerStyle>
            <HeaderStyle ForeColor="White" Font-Bold="True" 
              BackColor="#A55129"></HeaderStyle>
            <Columns>
                <asp:CommandField ButtonType="Image" ShowEditButton="true" ShowCancelButton="true"
                                EditImageUrl="Images/icons/edit24.png" UpdateImageUrl="Images/icons/update24.png" 
                                CancelImageUrl="Images/icons/cancel324.png" />

                <asp:TemplateField HeaderText="Division">
                    <ItemTemplate>
                        <%# Eval("DivisionShortcut")%>
                        <asp:HiddenField ID="divisioncode" value='<%#Eval("divisioncode")%>' runat="server" />
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DivisionsList" runat="server" DataSourceID="DivisionsListDataSource"
                                          DataTextField="DivisionShortcut" DataValueField="SapCode">
                        </asp:DropDownList>
                    </EditItemTemplate>
                </asp:TemplateField>

                <asp:BoundField DataField="Username" HeaderText="Network ID" ReadOnly="True" 
                    SortExpression="Username" />

                <asp:TemplateField HeaderText="Name">
                    <ItemTemplate>
                        <%# Eval("Name")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEmployeeName" runat="server" Text='<%# Bind("Name")%>' />
                    </EditItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Job Title">
                    <ItemTemplate>
                        <%# Eval("JobTitle")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtJobTitle" runat="server" Text='<%# Bind("JobTitle")%>' />
                    </EditItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Badge No.">
                    <ItemTemplate>
                        <%# Eval("BadgeNo")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtBadgeNo" runat="server" Text='<%# Bind("BadgeNo")%>' />
                    </EditItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Is Active?">
                    <ItemTemplate>
                        <%# Eval("IsActive")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:CheckBox ID="isActive" runat="server" 
                                      Checked='<%# Eval("IsActive").ToString().Equals("True") %>'/>
                    </EditItemTemplate>
                </asp:TemplateField>

                <asp:TemplateField HeaderText="Delete?">
                    <ItemTemplate>
                        <span onclick="return confirm('Are you sure to Delete the record?')">
                            <asp:ImageButton ID="lnkB" runat="server" ImageUrl="Images/icons/delete24.png" CommandName="Delete" />
                        </span>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:UsersInfoDBConnectionString %>" 

            SelectCommand="SELECT     dbo.Divisions.DivisionShortcut,dbo.employee.DivisionCode, dbo.employee.Username, dbo.employee.Name, dbo.employee.JobTitle, dbo.employee.BadgeNo, dbo.employee.IsActive
                    FROM         dbo.Divisions INNER JOIN
                                dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode"
            UpdateCommand="UPDATE [employee] SET [Name] = @Name, [JobTitle] = @JobTitle, 
                                                [BadgeNo] = @BadgeNo WHERE [Username] = @Username"
            DeleteCommand="DELETE FROM [employee] WHERE [Username] = @Username">
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String"  />
                <asp:Parameter Name="JobTitle" Type="String" />
                <asp:Parameter Name="BadgeNo" Type="String" />
                <asp:Parameter Name="Username" Type="String" />
            </UpdateParameters>
            <DeleteParameters>
                <asp:Parameter Name="Username" Type="String" />
            </DeleteParameters>
        </asp:SqlDataSource>

        <asp:SqlDataSource ID="DivisionsListDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:UsersInfoDBConnectionString %>" 
            SelectCommand="SELECT * FROM Divisions">
        </asp:SqlDataSource>

Code-Behind:

//For editing any row in the GridView
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) 
    {
        GridView1.EditIndex = e.NewEditIndex;
    }

    //For canceling any editng in any row in the GridView
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        e.Cancel = true;
        GridView1.EditIndex = -1;
    }

    //For updating the information in any row in the GridView
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow gvrow = GridView1.Rows[e.RowIndex];

        DropDownList DivisionsList = (DropDownList)gvrow.FindControl("DivisionsList");
        HiddenField hidden = (HiddenField)gvrow.FindControl("divisioncode");
        string division_code = hidden.Value.ToString();

        TextBox txtEmployeeName = (TextBox)gvrow.FindControl("txtEmployeeName");
        TextBox txtJobTitle = (TextBox)gvrow.FindControl("txtJobTitle");
        TextBox txtBadgeNo = (TextBox)gvrow.FindControl("txtBadgeNo");

        CheckBox isActive = (CheckBox)gvrow.FindControl("isActive");

        //For getting the ID (primary key) of that row
        string username = GridView1.DataKeys[e.RowIndex].Value.ToString();

        string name = txtEmployeeName.Text;
        string jobTitle = txtJobTitle.Text;
        string badgeNo = txtBadgeNo.Text;
        string division = DivisionsList.SelectedValue.ToString();

        //string divisioncode = DivisionsList.SelectedValue;

        UpdateEmployeeInfo(username, name, jobTitle, badgeNo, division, division_code);
    }


    private void UpdateEmployeeInfo(string username, string name, string jobTitle, string badgeNo, string division, string divisioncode)
    {
        string connString = ConfigurationManager.ConnectionStrings["UsersInfoDBConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
                            BadgeNo = @BadgeNo WHERE Username = @Username; 
                            UPDATE Divisions SET [DivisionShortcut] = @division WHERE SapCode = @SapCode;";

        SqlCommand cmd = new SqlCommand(update, conn);

        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@JobTitle", jobTitle);
        cmd.Parameters.AddWithValue("@BadgeNo", badgeNo);
        cmd.Parameters.AddWithValue("@division", division);
        cmd.Parameters.AddWithValue("@Username", username);
        cmd.Parameters.AddWithValue("@SapCode", divisioncode);
        //cmd.Parameters.AddWithValue("@IsActive", isActive.checked);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            GridView1.EditIndex = -1;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
        GridView1.DataBind();
    }

score 2 · Accepted Answer · 2012-07-16 06:05:08Z

2

You have error in following line code part:

string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
                        BadgeNo = @BadgeNo, DivisionShortcut = @division WHERE Username = @Username;
                                                     ^^
                        UPDATE Divisions SET [DivisionShortcut] = @division WHERE SapCode = @SapCode;";

dividionshortcut is not a member of employee table. Just remove it from marked position and run it again I am sure this will remove your error.

Code to be used. Add following field in your gridview:

 <asp:TemplateField HeaderText="Division">
            <ItemTemplate>
                <%# Eval("DivisionShortcut")%>
<asp:HiddenField ID="divisioncode" value='<%#Eval("divisioncode")%>' runat="server" />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:DropDownList ID="DivisionsList" runat="server" DataSourceID="DivisionsListDataSource"
                                    DataTextField="DivisionShortcut" DataValueField="SapCode">
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>

First use following select command

 SelectCommand="SELECT     dbo.Divisions.DivisionShortcut,dbo.employee.DivisionCode, dbo.employee.Username, dbo.employee.Name, dbo.employee.JobTitle, dbo.employee.BadgeNo, dbo.employee.IsActive
                    FROM         dbo.Divisions INNER JOIN
                                dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode"
    UpdateCommand="UPDATE [employee] SET [Name] = @Name, [JobTitle] = @JobTitle, 
                                        [BadgeNo] = @BadgeNo WHERE [Username] = @Username"
    DeleteCommand="DELETE FROM [employee] WHERE [Username] = @Username">
    <UpdateParameters>
        <asp:Parameter Name="Name" Type="String"  />
        <asp:Parameter Name="JobTitle" Type="String" />
        <asp:Parameter Name="BadgeNo" Type="String" />
        <asp:Parameter Name="Username" Type="String" />
    </UpdateParameters>
    <DeleteParameters>
        <asp:Parameter Name="Username" Type="String" />
    </DeleteParameters>

Second when row updating event is called use following:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow gvrow = GridView1.Rows[e.RowIndex];

    DropDownList DivisionsList = (DropDownList)gvrow.FindControl("DivisionsList"); 
    HiddenField hidden = (HiddenField)gvrow.FindControl("divisioncode");
    string division_code=hidden.value.ToString();
    TextBox txtEmployeeName = (TextBox)gvrow.FindControl("txtEmployeeName");
    TextBox txtJobTitle = (TextBox)gvrow.FindControl("txtJobTitle");
    TextBox txtBadgeNo = (TextBox)gvrow.FindControl("txtBadgeNo");

    CheckBox isActive = (CheckBox)gvrow.FindControl("isActive");

    //For getting the ID (primary key) of that row
    string username = GridView1.DataKeys[e.RowIndex].Value.ToString();

    string name = txtEmployeeName.Text;
    string jobTitle = txtJobTitle.Text;
    string badgeNo = txtBadgeNo.Text;
    string division = DivisionsList.SelectedValue.ToString();

    UpdateEmployeeInfo(username, name, jobTitle, badgeNo, division,division_code);
}

Now in code behind add one more parameter for division code in your function and call following update function:

 private void UpdateEmployeeInfo(string username, string name, string jobTitle, string badgeNo, string division,string divisioncode)
    {
        string connString = ConfigurationManager.ConnectionStrings["UsersInfoDBConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
                            BadgeNo = @BadgeNo WHERE Username = @Username; 
                            UPDATE Divisions SET [DivisionShortcut] = @division WHERE SapCode = @SapCode;";

        SqlCommand cmd = new SqlCommand(update, conn);

        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@JobTitle", jobTitle);
        cmd.Parameters.AddWithValue("@BadgeNo", badgeNo);
        cmd.Parameters.AddWithValue("@division", division);
        cmd.Parameters.AddWithValue("@Username", username);
        cmd.Parameters.AddWithValue("@DapCode", divisioncode);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            GridView1.EditIndex = -1;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
        GridView1.DataBind();
    }

I hope this will solve your problem

edited Jul 16, 2012 at 6:05

answered Jul 11, 2012 at 11:11

user1465587

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

7 Comments

Tech Lover Over a year ago

I got the following error: 'Must declare the scalar variable "@SapCode".'

Tech Lover Over a year ago

How to get the value of the SapCode and where should I use it. Sorry for asking a lot but I am a new ASP.NET developer. Please have a look at my code-behind above.

user1465587 Over a year ago

You are having DivisionCode is a foreign key to the SapCode in the Divisions Table so you can use division code of updated employee from employee table as a value of sapcode as they both will be same (I hope).

Tech Lover Over a year ago

what is the value of divisioncode variable that you added in this function. As you know the value of division = dropdownlist.text, so what about divisioncode

user1465587 Over a year ago

you can use hidden field in your GV and assign it the value of divisioncode received from select query. And in your code behind you can access that value along with other values and send it to your update function.

|

nyxthulhu · Accepted Answer · 2012-07-11 10:02:13Z

0

Its your code here

UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
                    BadgeNo = @BadgeNo, DivisionShortcut = @division WHERE Username =     @Username;

DivisionShortcut is not a member of the Employee table. You have an update statement under it so I think you just accidently left it in there, deleting it from this update line will fix the issue.

answered Jul 11, 2012 at 10:02

nyxthulhu

9,8029 gold badges60 silver badges92 bronze badges

1 Comment

Tech Lover Over a year ago

I removed it and still facing an error which is: 'Must declare the scalar variable "@SapCode".'

Collectives™ on Stack Overflow

Why this column cannot be updated using DropDownList inside the GridView?

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related