Umm Hani

NA
103
3.8k

how to insert multi record in grid view on one id of emp?

Jun 24 2019 5:37 AM

I have One problem how can i insert more then one companies record on one id of emp in grid view ??

In my Project have two table first Employee and second one Company

Default.aspx web form code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="gridviewempcompny.Default" %>

<!DOCTYPE html>

</head>

<body>

<div>

<tr>

</tr>

<tr>

<td class="auto-style1">EmpID</td>

<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<asp:Label ID="Label1" runat="server" Text="Emp Code"></asp:Label>

</td>

<asp:TextBox ID="txtEmpCode" runat="server"></asp:TextBox>

</td>

 </td>

</tr>

<tr>

<asp:Label ID="Label2" runat="server" Text="Emp Name"></asp:Label>

</td>

<asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>

</td>

 </td>

</tr>

<tr>

<asp:Label ID="Label3" runat="server" Text="Position"></asp:Label>

</td>

<asp:TextBox ID="cmbPosition" runat="server"></asp:TextBox>

</td>

 </td>

</tr>

<tr>

<asp:Label ID="Label4" runat="server" Text="Date Of Birth"></asp:Label>

<%--SHOW THE SELECTED DATE.--%>

</td>

<asp:TextBox ID="dtpDOB" runat="server" placeholder="Select a Date"></asp:TextBox>

</td>

 </td>

<%-- <asp:Button Text="Submit" ID="submit" OnClick="btClick" runat="server" />--%>

</td>

</tr>

<tr>

<asp:Label ID="Label5" runat="server" Text="Gender"></asp:Label>

</td>

<asp:TextBox ID="cmbGender" runat="server"></asp:TextBox>

</td>

 </td>

</tr>

<tr>

<asp:Label ID="Label6" runat="server" Text="State"></asp:Label>

</td>

<asp:RadioButton ID="rbtRegular" Text="Regular" runat="server" />

<asp:RadioButton ID="rbtContractual" Text="Contractual" runat="server" />

</td>

 </td>

</tr>

<tr>

 </td>

<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />

<asp:Button ID="btnDelete" runat="server" Text="Delete" />

</td>

<asp:Button ID="btnReset" runat="server" Text="Reset" />

</td>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

<asp:GridView ID="gvCompany" runat="server" AutoGenerateColumns="False" ShowFooter="True" DataKeyNames="EmpCmpID" ShowHeaderWhenEmpty="True" OnRowCommand="gvCompany_RowCommand" OnRowEditing="gvCompany_RowEditing" OnRowCancelingEdit="gvCompany_RowCancelingEdit"

OnRowUpdating="gvCompany_RowUpdating" OnRowDeleting="gvCompany_RowDeleting"

BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" OnSelectedIndexChanged="gvCompany_SelectedIndexChanged">

<%-- Theme Properties --%>

<asp:TemplateField>

<asp:ImageButton ImageUrl="~/Images/Select.png" runat="server" CommandName="Select" ToolTip="Select" Width="20px" Height="20px"/>

</ItemTemplate>

<asp:ImageButton ImageUrl="~/Images/save.png" runat="server" CommandName="Select" ToolTip="Select" Width="20px" Height="20px"/>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:ImageButton ImageUrl="~/Images/edit.png" runat="server" CommandName="Edit" ToolTip="Edit" Width="20px" Height="20px"/>

<asp:ImageButton ImageUrl="~/Images/delete.png" runat="server" CommandName="Delete" ToolTip="Delete" Width="20px" Height="20px"/>

</ItemTemplate>

<asp:ImageButton ImageUrl="~/Images/save.png" runat="server" CommandName="Update" ToolTip="Update" Width="20px" Height="20px"/>

<asp:ImageButton ImageUrl="~/Images/cancel.png" runat="server" CommandName="Cancel" ToolTip="Cancel" Width="20px" Height="20px"/>

</EditItemTemplate>

<asp:ImageButton ImageUrl="~/Images/addnew.png" runat="server" CommandName="AddNew" ToolTip="Add New" Width="20px" Height="20px"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="CompanyID">

<asp:Label Text='<%# Eval("EmpCmpID") %>' runat="server" />

</ItemTemplate>

<asp:TextBox ID="txtCompanyID" Text='<%# Eval("EmpCmpID") %>' runat="server" />

</EditItemTemplate>

<asp:TextBox ID="txtCompanyIDFooter" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="CompanyName">

<asp:Label Text='<%# Eval("CompanyName") %>' runat="server" />

</ItemTemplate>

<asp:TextBox ID="txtCompanyName" Text='<%# Eval("CompanyName") %>' runat="server" />

</EditItemTemplate>

<asp:TextBox ID="txtCompanyNameFooter" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="PositionID">

<asp:Label Text='<%# Eval("PositionID") %>' runat="server" />

</ItemTemplate>

<asp:TextBox ID="txtPosition" Text='<%# Eval("PositionID") %>' runat="server" />

</EditItemTemplate>

<asp:TextBox ID="txtPositionFooter" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="ExpYear">

<asp:Label Text='<%# Eval("ExpYear") %>' runat="server" />

</ItemTemplate>

<asp:TextBox ID="txtExpYear" Text='<%# Eval("ExpYear") %>' runat="server" />

</EditItemTemplate>

<asp:TextBox ID="txtExpYearFooter" runat="server" />

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<br />

<asp:Label ID="lblSuccessMessage" Text="" runat="server" ForeColor="Green" />

<br />

<asp:Label ID="lblErrorMessage" Text="" runat="server" ForeColor="Red" />

</div>

</form>

</body>

</html>

Default.Cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace gridviewempcompny

{

public partial class Default : System.Web.UI.Page

{

int inEmpID = 0;

// bool isDefaultImage = true;

string strConnectionString = @"Data Source=.; Initial Catalog=MasterDataDS; Integrated Security=True;";

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

// PopulateGridview();

LoadProducts();

// AddDefaultFirstRecord();

}

private void btnReset_Click(object sender, EventArgs e)

{

Clear();

}

void Clear()

{

txtEmpCode.Text = txtEmpName.Text = "";

rbtRegular.Checked = true;

}

bool ValidateMasterDetailForm()

{

bool _isValid = true;

if (txtEmpName.Text.Trim() == "")

{

// Response.Show("Employee Name is required");

_isValid = false;

}

//Add more validations if needed.

return _isValid;

}

SqlCommand sqlcmd;

void PopulateGridview()

{

DataTable dtbl = new DataTable();

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

sqlCon.Open();

sqlcmd = new SqlCommand("Select * from EmpCompany", sqlCon);

SqlDataAdapter sqlDa = new SqlDataAdapter(sqlcmd);

sqlDa.Fill(dtbl);

}

if (dtbl.Rows.Count > 0)

{

gvCompany.DataSource = dtbl;

gvCompany.DataBind();

gvCompany.Columns[2].Visible = false;

}

else

{

dtbl.Rows.Add(dtbl.NewRow());

gvCompany.DataSource = dtbl;

gvCompany.DataBind();

gvCompany.Rows[0].Cells.Clear();

gvCompany.Rows[0].Cells.Add(new TableCell());

gvCompany.Rows[0].Cells[0].ColumnSpan = dtbl.Columns.Count;

gvCompany.Rows[0].Cells[0].Text = "No Data Found ..!";

gvCompany.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;

// gvCompany.Columns[0].Visible = false;

// gvCompany.Columns[2]. = DataGridViewAutoSizeColumnMode.Fill;

// gvCompany.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

}

protected void gvCompany_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (ValidateMasterDetailForm())

{

int _EmpID = 0;

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

sqlCon.Open();

//Master

SqlCommand sqlCmd = new SqlCommand("EmployeeAdd", sqlCon);

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.AddWithValue("@EmpID", inEmpID);

sqlCmd.Parameters.AddWithValue("@EmpCode", txtEmpCode.Text.Trim());

sqlCmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text.Trim());

sqlCmd.Parameters.AddWithValue("@PositionID", cmbPosition.Text.Trim());

sqlCmd.Parameters.AddWithValue("@DOB", dtpDOB.Text);

sqlCmd.Parameters.AddWithValue("@Gender", cmbGender.Text.Trim());

sqlCmd.Parameters.AddWithValue("@State", rbtRegular.Checked ? "Regular" : "Contractual");

//sqlCmd.Parameters.AddWithValue("@ImagePath", DBNull.Value);

_EmpID = Convert.ToInt32(sqlCmd.ExecuteScalar());

}

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

foreach (GridViewRow dgvRow in gvCompany.Rows)

{

sqlCon.Open();

if (e.CommandName.Equals("AddNew")) break;

else

{

string query = "INSERT INTO EmpCompany(EmpId,CompanyName,PositionID,ExpYear) VALUES(@EmpId,@CompanyName,@PositionID,@ExpYear)";

SqlCommand sqlCmd = new SqlCommand(query, sqlCon); ;

//sqlCmd.Parameters.AddWithValue("@EmpCmpID", (gvCompany.FooterRow.FindControl("txtCompanyIDFooter") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@EmpId", _EmpID);

sqlCmd.Parameters.AddWithValue("@CompanyName", (gvCompany.FooterRow.FindControl("txtCompanyNameFooter") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@PositionID", (gvCompany.FooterRow.FindControl("txtPositionFooter") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@ExpYear", (gvCompany.FooterRow.FindControl("txtExpYearFooter") as TextBox).Text.Trim());

sqlCmd.ExecuteNonQuery();

LoadProducts();

lblSuccessMessage.Text = "New Record Added";

lblErrorMessage.Text = "";

}

protected void gvCompany_RowEditing(object sender, GridViewEditEventArgs e)

{

gvCompany.EditIndex = e.NewEditIndex;

// PopulateGridview();

}

protected void gvCompany_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

gvCompany.EditIndex = -1;

// PopulateGridview();

}

protected void gvCompany_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

try

{

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

sqlCon.Open();

string query = "UPDATE EmpCompany SET CompanyName=@CompanyName,PositionID=@PositionID,ExpYear=@ExpYear WHERE EmpCmpID=@EmpCmpID";

// string query = "INSERT INTO EmpCompany(EmpId,CompanyName,PositionID,ExpYear) VALUES(@EmpId,@CompanyName,@PositionID,@ExpYear)";

SqlCommand sqlCmd = new SqlCommand(query, sqlCon);

sqlCmd.Parameters.AddWithValue("@EmpId", TextBox5.Text);

// sqlCmd.Parameters.AddWithValue("@EmpCmpID", (gvCompany.Rows[e.RowIndex].FindControl("txtCompanyID") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@CompanyName", (gvCompany.Rows[e.RowIndex].FindControl("txtCompanyName") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@PositionID", (gvCompany.Rows[e.RowIndex].FindControl("txtPosition") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@ExpYear", (gvCompany.Rows[e.RowIndex].FindControl("txtExpYear") as TextBox).Text.Trim());

sqlCmd.Parameters.AddWithValue("@EmpCmpID", Convert.ToInt32(gvCompany.DataKeys[e.RowIndex].Value.ToString()));

sqlCmd.ExecuteNonQuery();

gvCompany.EditIndex = -1;

PopulateGridview();

lblSuccessMessage.Text = "Selected Record Updated";

lblErrorMessage.Text = "";

}

catch (Exception ex)

{

lblSuccessMessage.Text = "";

lblErrorMessage.Text = ex.Message;

}

protected void btnSave_Click(object sender, EventArgs e)

{

}

protected void gvCompany_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

try

{

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

sqlCon.Open();

string query = "DELETE FROM EmpCompany WHERE EmpCmpID = @EmpCmpID";

SqlCommand sqlCmd = new SqlCommand(query, sqlCon);

sqlCmd.Parameters.AddWithValue("@EmpCmpID", Convert.ToInt32(gvCompany.DataKeys[e.RowIndex].Value.ToString()));

sqlCmd.ExecuteNonQuery();

// PopulateGridview();

lblSuccessMessage.Text = "Selected Record Deleted";

lblErrorMessage.Text = "";

}

catch (Exception ex)

{

lblSuccessMessage.Text = "";

lblErrorMessage.Text = ex.Message;

}

protected void gvCompany_SelectedIndexChanged(object sender, EventArgs e)

{

try

{

using (SqlConnection sqlCon = new SqlConnection(strConnectionString))

{

sqlCon.Open();

// TextBox5.Text = gvCompany.SelectedRow.Cells[1].Text;

(gvCompany.FooterRow.FindControl("txtCompanyNameFooter") as TextBox).Text = gvCompany.SelectedRow.Cells[3].Text;

(gvCompany.FooterRow.FindControl("txtPositionFooter") as TextBox).Text = gvCompany.SelectedRow.Cells[4].Text;

(gvCompany.FooterRow.FindControl("txtExpYearFooter") as TextBox).Text = gvCompany.SelectedRow.Cells[5].Text; ;

// sqlCmd.ExecuteNonQuery();

gvCompany.EditIndex = -1;

// PopulateGridview();

}

catch (Exception ex)

{

lblSuccessMessage.Text = "";

lblErrorMessage.Text = ex.Message;

}

public string constr;

public SqlConnection con;

public void connection()

{

//Stoting connection string

string strConnectionString = @"Data Source=.; Initial Catalog=MasterDataDS; Integrated Security=True;";

con = new SqlConnection(strConnectionString);

con.Open();

}

Answers (1)

C# basics (ATTRIBUTES )

How to read several different commands in one method