The problem is whenever I update any record, it gets updated in database. But in gridview it adds another record instead of updating the existing one. Same problem with delete operation, record gets deleted from database but not from gridview.
I have searched it already but didn't found proper solution.
Here is the code :
(The method setGroupGridView is called after each operation i.e. insert,update,delete
& the code is error free just need to add some lines for gridview updation.)
Main Form
public partial class frmMain : Form
{
Connection cn = new Connection();
int GId,MId,STId,LId,LTId;
DataSet ds = new DataSet();
DataTable dt;
public frmMain()
{
InitializeComponent();
}
public frmMain(Form frm)
{
InitializeComponent();
}
private void frmMain_Load(object sender, EventArgs e)
{
setGroupGridView();
GId = cn.AutoCodeGen("tblGroupDetails");
lblGId.Text = GId.ToString();
}
# region Group Details Code
private void btnGAdd_Click(object sender, EventArgs e)
{
GroupDetails gd=new GroupDetails(lblGId.Text,txtGName.Text);
gd.Add();
setGroupGridView();
resetGroupDetails();
}
private void btnGUpdate_Click(object sender, EventArgs e)
{
GroupDetails gd = new GroupDetails(lblGId.Text, txtGName.Text);
gd.Update();
setGroupGridView();
resetGroupDetails();
btnGUpdate.Enabled = false;
}
private void dgvGroupDetails_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 0)
{
btnGUpdate.Enabled = true;
lblGId.Text = dgvGroupDetails.Rows[e.RowIndex].Cells[2].Value.ToString();
txtGName.Text = dgvGroupDetails.Rows[e.RowIndex].Cells[3].Value.ToString();
}
else if (e.ColumnIndex == 1)
{
GroupDetails gd = new GroupDetails(dgvGroupDetails.Rows[e.RowIndex].Cells[2].Value.ToString(), dgvGroupDetails.Rows[e.RowIndex].Cells[3].Value.ToString());
gd.Delete();
cn.da.Update(dt);
setGroupGridView();
resetGroupDetails();
}
}
private void resetGroupDetails()
{
GId = cn.AutoCodeGen("tblGroupDetails");
lblGId.Text = GId.ToString();
txtGName.Text = "";
}
private void setGroupGridView()
{
dt = new DataTable();
dt = cn.GetDetails("select * from tblGroupDetails");
dgvGroupDetails.DataSource = dt;
dgvGroupDetails.ForeColor = Color.Black;
for (int i = 2; i < dgvGroupDetails.Columns.Count; i++)
{
dgvGroupDetails.Columns[i].Width = 150;
}
}
#endregion
}
Class for insert, update & delete:
class GroupDetails
{
protected int GId;
protected string GName;
Connection con;
public GroupDetails(string _GId, string _GName)
{
con = new Connection();
GId = Convert.ToInt16(_GId);
GName = _GName;
}
public void Add()
{
con.Execute("insert into tblGroupDetails(GId,GName) values("+ GId +",'"+ GName +"')");
}
public void Update()
{
con.Execute("update tblGroupDetails set GName='"+ GName +"' where GId=" + GId +"");
}
public void Delete()
{
con.Execute("delete from tblGroupDetails where GId=" + GId + "");
}
}
Class for connection to db:
public class Connection
{
public SqlConnection con;
public SqlCommand cmd = new SqlCommand();
public SqlDataAdapter da;
public DataSet ds = new DataSet();
public DataTable dt = new DataTable();
int code;
public Connection()
{
try
{
con = new SqlConnection("Data Source=abc;Initial Catalog=db;Integrated Security=True;Pooling=False");
con.Open();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Connection Failed");
}
}
public void Execute(string query)
{
try
{
cmd.CommandText = query;
cmd.Connection = con;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Operation Failed.");
}
}
public DataTable GetDetails(string query)
{
try
{
cmd.CommandText = query;
cmd.Connection = con;
da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Operation Failed.");
return null;
}
}
}
For solution I have already tried following code, But works none for me :
//1st solution
dgvGroupDetails.Rows.Clear();
// 2nd solution
If(dgvGroupDetails.DataSource != null)
{
dgvGroupDetails.DataSource=null;
}
else
{
dgvGroupDetails.Rows.Clear();
}