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 still this column is not updated. I don't have any errors in the debugging.
So how to get this column updated with the DropDownList inside the GridView?
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 önclick="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:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
e.Cancel = true;
GridView1.EditIndex = -1;
}
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");
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);
}
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);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
GridView1.EditIndex = -1;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
GridView1.DataBind();
}
UPDATE:
The complete updated code:
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")%>
</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: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, [DivisionCode] = @DivisionCode,
[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" />
<asp:Parameter Name="DivisionCode" 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"
InsertCommand="INSERT INTO [Divisions] ([SapCode], [DivisionShortcut]) VALUES (@SapCode, @DivisionShortcut)"
UpdateCommand="UPDATE [Divisions] SET [DivisionShortcut] = @DivisionShortcut WHERE [SapCode] = @SapCode">
<DeleteParameters>
<asp:Parameter Name="SapCode" Type="Double" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="SapCode" Type="Double" />
<asp:Parameter Name="DivisionShortcut" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="DivisionShortcut" Type="String" />
<asp:Parameter Name="SapCode" Type="Double" />
</UpdateParameters>
</asp:SqlDataSource>
Code-Behind:
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");
string username = GridView1.DataKeys[e.RowIndex].Value.ToString();
string name = txtEmployeeName.Text;
string jobTitle = txtJobTitle.Text;
string badgeNo = txtBadgeNo.Text;
string division_code = DivisionsList.SelectedValue.ToString();
UpdateEmployeeInfo(username, name, jobTitle, badgeNo, division_code);
}
private void UpdateEmployeeInfo(string username, string name, string jobTitle, string badgeNo, string division_code)
{
string connString = ConfigurationManager.ConnectionStrings["UsersInfoDBConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
string update = @"UPDATE Employee SET Name = @Name, JobTitle = @JobTitle, BadgeNo = @BadgeNo, DivisionCode = @DivisionCode
WHERE Username = @Username;";
SqlCommand cmd = new SqlCommand(update, conn);
cmd.Parameters.AddWithValue("@Username", username);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@JobTitle", jobTitle);
cmd.Parameters.AddWithValue("@BadgeNo", badgeNo);
cmd.Parameters.AddWithValue("@DivisionCode", division_code);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
GridView1.EditIndex = -1;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
GridView1.DataBind();
}
Now, I am getting this following error and I don't know why since the column will be as a dropdownlist in the Edit mode which means there is a data and there is nothing called NULL:
Cannot insert the value NULL into column 'DivisionCode', table 'UsersInfoDB.dbo.employee'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
So how to fix this error?