Answers

Reload Data to Datagridview from database for editing, using

Ashfaque Baig

894

I am trying to reload data to datagridview from database for edit using combobox value but code is generating one more PRODUCT column in datagridview. Screen shot and my full codes are below. How i can reload data in exiting column of PRODUCT.

DATABASE NAME: MyDb

TABLE NAME: tblSALEITEMDETAILS

COLUMN NAME: 1)ID, 2)PRODUCTNAME, 3)QUANTITY, 4) RATE, 5)INVOICENO

My current codes are as below

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.OleDb;
namespace invoiceedit
{
public partial class SALEINVOICE : Form
{
static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashfaque Baig\Documents\MyAccessDb\MyDb.accdb;Persist Security Info=False;";
OleDbConnection con = new OleDbConnection(conString);
OleDbCommand cmd = new OleDbCommand();
DataTable dtProduct = new DataTable();
DataTable dtCustomer = new DataTable();
DataTable dtInvoiceNo = new DataTable();
DataGridViewComboBoxColumn PRODUCTNAME = new DataGridViewComboBoxColumn();
public SALEINVOICE()
{
InitializeComponent();
}
private void SALEINVOICE_Load(object sender, EventArgs e)
{
CreatDataGridColumns();
CUSTOMERLIST();
combCUSTOMERNAME.DataSource = dtCustomer;
combCUSTOMERNAME.DisplayMember = "CUSTOMERNAME";
INVOICENOLIST();
combINVOICENO.DataSource = dtInvoiceNo;
combINVOICENO.DisplayMember = "INVOICENO";
}
private DataSet CUSTOMERLIST()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand cmd;
DataSet dsCustomer = new DataSet();
//SELECT STMTS string tblCustomer = "SELECT * FROM tblCUSTOMER";
//PRODUCTTABLE cmd = new OleDbCommand(tblCustomer, con);
adapter.SelectCommand = cmd;
adapter.SelectCommand.CommandText = tblCustomer;
adapter.Fill(dsCustomer, "CUSTOMERNAME");
dtCustomer = dsCustomer.Tables[0]; return dsCustomer;
}
private DataSet PRODUCTLIST()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand cmd;
DataSet dsProduct = new DataSet();
//SELECT STMTS string tblPRODUCT = "SELECT * FROM tblPRODUCT";
//PRODUCTTABLE cmd = new OleDbCommand(tblPRODUCT, con);
adapter.SelectCommand = cmd;
adapter.SelectCommand.CommandText = tblPRODUCT;
adapter.Fill(dsProduct, "PRODUCTNAME");
dtInvoiceNo = dsProduct.Tables[0];
return dsProduct;
}
private DataSet INVOICENOLIST()
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand cmd;
DataSet dsPurchaseGrnList = new DataSet();
//SELECT STMTS string tblSALEBILDETAILS = "SELECT * FROM tblSALEBILDETAILS";
//PRODUCTTABLE cmd = new OleDbCommand(tblSALEBILDETAILS, con);
adapter.SelectCommand = cmd;
adapter.SelectCommand.CommandText = tblSALEBILDETAILS;
adapter.Fill(dsPurchaseGrnList, "INVOICENO");
dtInvoiceNo = dsPurchaseGrnList.Tables[0];
return dsPurchaseGrnList;
}
public void CreatDataGridColumns()
{
//ADD COLUMNS DataGridViewComboBoxColumn PRODUCTNAME = new DataGridViewComboBoxColumn();
PRODUCTNAME.HeaderText = "PRODUCTNAME";
PRODUCTNAME.Name = "PRODUCTNAME";
PRODUCTNAME.AutoComplete = true;
PRODUCTNAME.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
PRODUCTNAME.DisplayMember = "PRODUCTNAME";
PRODUCTNAME.Width = 200;
ArrayList row = new ArrayList();
//FILL ARRAYLIST FROM DATATABLE
foreach (DataRow dr in dtProduct.Rows)
{
row.Add(dr["PRODUCTNAME"].ToString());
}
//ADD TO COMBO
PRODUCTNAME.Items.AddRange(row.ToArray());
//ADD THE COMBO TO DATAGRIDVIEW
dataGridITEMDETAILS.Columns.Add(PRODUCTNAME);
PRODUCTNAME.DataSource = PRODUCTLIST().Tables[0];
DataGridViewColumnCollection columns = dataGridITEMDETAILS.Columns;
DataGridViewTextBoxColumn textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "QUANTITY";
textColumn.Name = "QUANTITY";
textColumn.HeaderText = "QUANTITY";
textColumn.ValueType = typeof(string);
textColumn.Visible = true;
columns.Add(textColumn);
textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "RATE";
textColumn.Name = "RATE";
textColumn.HeaderText = "RATE";
textColumn.ValueType = typeof(string);
textColumn.Visible = true;
columns.Add(textColumn);
textColumn = new DataGridViewTextBoxColumn();
textColumn.DataPropertyName = "AMOUNT";
textColumn.Name = "AMOUNT";
textColumn.HeaderText = "AMOUNT";
textColumn.ValueType = typeof(string);
textColumn.Visible = true;
columns.Add(textColumn);
}
private void combINVOICENO_SelectedIndexChanged(object sender, EventArgs e)
{
cmd = new OleDbCommand("SELECT * FROM tblSALEBILDETAILS where INVOICENO='" + combINVOICENO.Text + "'", con);
con.Open();
cmd.ExecuteNonQuery();
OleDbDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string InvoiceDate = (string)dr["INVOICEDATE"].ToString();
string InvoiceNo = (string)dr["INVOICENO"].ToString();
string CustomerName = (string)dr["CUSTOMERNAME"].ToString();
txtDATE.Text = InvoiceDate;
txtINVOICENO.Text = InvoiceNo;
combCUSTOMERNAME.Text = CustomerName;
}
con.Close();
{
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
string query = "SELECT PRODUCTNAME,QUANTITY,RATE FROM tblSALEITEMDETAILS where INVOICENO='" + combINVOICENO.Text + "'";
cmd.CommandText = query;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridITEMDETAILS.DataSource = dt;
PRODUCTNAME.DisplayMember = "PRODUCTNAME";
con.Close();
}
}
}