Why this DropDownList is not updating inside this GridView?

Question

0.00/5 (No votes)

See more:

I have the following database design:

SQL

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

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. I wrote the code but I got the following error:
*

> Invalid column name 'DivisionShortcut'.

*

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" OnRowEditing="GridView1_RowEditing"
             OnRowCancelingEdit="GridView1_RowCancelingEdit"
             OnRowUpdating="GridView1_RowUpdating">
            <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="Button" ShowEditButton="true" ShowCancelButton="true" />

                <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  önclick="return confirm('Are you sure to Delete the record?')">
                            <asp:LinkButton ID="lnkB" runat="Server" Text="Delete" CommandName="Delete"></asp:LinkButton>
                        </span>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:UsersInfoDBConnectionString %>"

            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], [Divisions] SET [Name] = @Name, [JobTitle] = @JobTitle,
                                                [BadgeNo] = @BadgeNo, [DivisionShortcut] = @division 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="DivisionShortcut" Type="String" />
                <asp:Parameter Name="Username" Type="String" />
            </UpdateParameters>
            <DeleteParameters>
                <asp:Parameter Name="Username" Type="String" />
            </DeleteParameters>
        </asp:SqlDataSource>

I forgot to mention that there is a SqlDataSource for the DropDownList.

SQL

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

UPDATE:

Code-Behind:

C#

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

//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";
        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;
            //            SqlDataSource1.UpdateCommand = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, 
            //                                                BadgeNo = @BadgeNo 
            //                                                WHERE Username = @Username";
            //            SqlDataSource1.Update();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
        GridView1.DataBind();
    }

And the error is:
System.Data.SqlClient.SqlException: Invalid column name 'DivisionShortcut'.

which is fired in the Catch block from the UpdateEmployeeInfo()

Posted 10-Jul-12 1:58am

matrix388

Updated 10-Jul-12 2:21am

v4

Add a Solution

Comments

pradiprenushe 10-Jul-12 8:11am

What are you doing in OnRowEditing? Add breakpoints to OnRowEditing , OnRowUpdating look when exception occurs.
Add SqlDatasource in gridview editTemplate where dropdown placed.

matrix388 10-Jul-12 8:22am

Please look at the updated question now.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pradiprenushe · Answer 1 · 2012-07-10T02:35:00

Solution 1

You have added

<asp:parameter name="DivisionShortcut" type="String" xmlns:asp="#unknown" />

in update parameter.
Change it to divison because you are using @division parameter.

Posted 10-Jul-12 2:35am

pradiprenushe

Sandeep Mewara · Answer 2 · 2012-07-10T02:44:00

Solution 2

You have defined wrong column in your update query. There is no column named "DivisionShortcut" in table Employee, hence the error.

You need to pass the SapCode as parameter and update the DivisionCode.
Something like:

C#

string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, BadgeNo = @BadgeNo, DivisionCode = @SapCode WHERE Username = @Username";

Posted 10-Jul-12 2:44am

Sandeep Mewara

Comments

matrix388 10-Jul-12 8:49am

I did and still nothing works with me.

Sandeep Mewara 13-Jul-12 3:24am

You need to assign correct values and then pass on. Other table will not get updated with this query. Only one table will get updated this way for sure.

ujjwal uniyal · Answer 3 · 2012-07-10T02:44:00

SQL

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

You are updating table employee but employee table don't have field DivisionShortcut.

Remove it .