Ashfaque Baig

Ashfaque Baig

  • NA
  • 66
  • 2.9k

Reload Data to Datagridview from database for editing, using

Jan 9 2019 7:06 AM
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
  1. using System;  
  2. using System.Collections;  
  3. using System.Collections.Generic;  
  4. using System.ComponentModel;  
  5. using System.Data;  
  6. using System.Drawing;  
  7. using System.Linq;  
  8. using System.Text;  
  9. using System.Threading.Tasks;  
  10. using System.Windows.Forms;  
  11. using System.Configuration;  
  12. using System.Data.OleDb;  
  13. namespace invoiceedit   
  14. {   
  15.     public partial class SALEINVOICE : Form   
  16.     {   
  17.         static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashfaque Baig\Documents\MyAccessDb\MyDb.accdb;Persist Security Info=False;";   
  18.         OleDbConnection con = new OleDbConnection(conString);   
  19.         OleDbCommand cmd = new OleDbCommand();   
  20.         DataTable dtProduct = new DataTable();   
  21.         DataTable dtCustomer = new DataTable();   
  22.         DataTable dtInvoiceNo = new DataTable();   
  23.         DataGridViewComboBoxColumn PRODUCTNAME = new DataGridViewComboBoxColumn();   
  24.         public SALEINVOICE()   
  25.         {   
  26.             InitializeComponent();   
  27.         }   
  28.         private void SALEINVOICE_Load(object sender, EventArgs e)   
  29.         {   
  30.             CreatDataGridColumns();   
  31.             CUSTOMERLIST();   
  32.             combCUSTOMERNAME.DataSource = dtCustomer;   
  33.             combCUSTOMERNAME.DisplayMember = "CUSTOMERNAME";   
  34.             INVOICENOLIST();   
  35.             combINVOICENO.DataSource = dtInvoiceNo;   
  36.             combINVOICENO.DisplayMember = "INVOICENO";   
  37.         }   
  38.         private DataSet CUSTOMERLIST()   
  39.         {   
  40.             OleDbDataAdapter adapter = new OleDbDataAdapter();   
  41.             OleDbCommand cmd;   
  42.             DataSet dsCustomer = new DataSet();   
  43.             //SELECT STMTS string tblCustomer = "SELECT * FROM tblCUSTOMER";   
  44.             //PRODUCTTABLE cmd = new OleDbCommand(tblCustomer, con);   
  45.             adapter.SelectCommand = cmd;   
  46.             adapter.SelectCommand.CommandText = tblCustomer;   
  47.             adapter.Fill(dsCustomer, "CUSTOMERNAME");   
  48.             dtCustomer = dsCustomer.Tables[0]; return dsCustomer;   
  49.         }   
  50.         private DataSet PRODUCTLIST()   
  51.         {   
  52.             OleDbDataAdapter adapter = new OleDbDataAdapter();   
  53.             OleDbCommand cmd;   
  54.             DataSet dsProduct = new DataSet();   
  55.             //SELECT STMTS string tblPRODUCT = "SELECT * FROM tblPRODUCT";   
  56.             //PRODUCTTABLE cmd = new OleDbCommand(tblPRODUCT, con);   
  57.             adapter.SelectCommand = cmd;   
  58.             adapter.SelectCommand.CommandText = tblPRODUCT;   
  59.             adapter.Fill(dsProduct, "PRODUCTNAME");  
  60.             dtInvoiceNo = dsProduct.Tables[0];   
  61.             return dsProduct;   
  62.         }   
  63.         private DataSet INVOICENOLIST()   
  64.         {   
  65.             OleDbDataAdapter adapter = new OleDbDataAdapter();   
  66.             OleDbCommand cmd;   
  67.             DataSet dsPurchaseGrnList = new DataSet();   
  68.             //SELECT STMTS string tblSALEBILDETAILS = "SELECT * FROM tblSALEBILDETAILS";   
  69.             //PRODUCTTABLE cmd = new OleDbCommand(tblSALEBILDETAILS, con);   
  70.             adapter.SelectCommand = cmd;   
  71.             adapter.SelectCommand.CommandText = tblSALEBILDETAILS;   
  72.             adapter.Fill(dsPurchaseGrnList, "INVOICENO");   
  73.             dtInvoiceNo = dsPurchaseGrnList.Tables[0];   
  74.             return dsPurchaseGrnList;   
  75.         }   
  76.         public void CreatDataGridColumns()   
  77.         {   
  78.             //ADD COLUMNS DataGridViewComboBoxColumn PRODUCTNAME = new DataGridViewComboBoxColumn();   
  79.             PRODUCTNAME.HeaderText = "PRODUCTNAME";   
  80.             PRODUCTNAME.Name = "PRODUCTNAME";   
  81.             PRODUCTNAME.AutoComplete = true;   
  82.             PRODUCTNAME.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;   
  83.             PRODUCTNAME.DisplayMember = "PRODUCTNAME";   
  84.             PRODUCTNAME.Width = 200;   
  85.             ArrayList row = new ArrayList();   
  86.             //FILL ARRAYLIST FROM DATATABLE   
  87.             foreach (DataRow dr in dtProduct.Rows)   
  88.             {   
  89.                 row.Add(dr["PRODUCTNAME"].ToString());   
  90.             }   
  91.             //ADD TO COMBO   
  92.             PRODUCTNAME.Items.AddRange(row.ToArray());   
  93.             //ADD THE COMBO TO DATAGRIDVIEW   
  94.             dataGridITEMDETAILS.Columns.Add(PRODUCTNAME);   
  95.             PRODUCTNAME.DataSource = PRODUCTLIST().Tables[0];   
  96.             DataGridViewColumnCollection columns = dataGridITEMDETAILS.Columns;   
  97.             DataGridViewTextBoxColumn textColumn = new DataGridViewTextBoxColumn();   
  98.             textColumn.DataPropertyName = "QUANTITY";   
  99.             textColumn.Name = "QUANTITY";   
  100.             textColumn.HeaderText = "QUANTITY";   
  101.             textColumn.ValueType = typeof(string);   
  102.             textColumn.Visible = true;   
  103.             columns.Add(textColumn);   
  104.             textColumn = new DataGridViewTextBoxColumn();   
  105.             textColumn.DataPropertyName = "RATE";   
  106.             textColumn.Name = "RATE";   
  107.             textColumn.HeaderText = "RATE";   
  108.             textColumn.ValueType = typeof(string);   
  109.             textColumn.Visible = true;   
  110.             columns.Add(textColumn);   
  111.             textColumn = new DataGridViewTextBoxColumn();   
  112.             textColumn.DataPropertyName = "AMOUNT";   
  113.             textColumn.Name = "AMOUNT";  
  114.             textColumn.HeaderText = "AMOUNT";   
  115.             textColumn.ValueType = typeof(string);   
  116.             textColumn.Visible = true;   
  117.             columns.Add(textColumn);   
  118.         }   
  119.         private void combINVOICENO_SelectedIndexChanged(object sender, EventArgs e)   
  120.         {   
  121.             cmd = new OleDbCommand("SELECT * FROM tblSALEBILDETAILS where INVOICENO='" + combINVOICENO.Text + "'", con);   
  122.             con.Open();   
  123.             cmd.ExecuteNonQuery();   
  124.             OleDbDataReader dr;   
  125.             dr = cmd.ExecuteReader();   
  126.             while (dr.Read())   
  127.             {   
  128.                 string InvoiceDate = (string)dr["INVOICEDATE"].ToString();  
  129.                 string InvoiceNo = (string)dr["INVOICENO"].ToString();   
  130.                 string CustomerName = (string)dr["CUSTOMERNAME"].ToString();   
  131.                 txtDATE.Text = InvoiceDate;  
  132.                 txtINVOICENO.Text = InvoiceNo;   
  133.                 combCUSTOMERNAME.Text = CustomerName;   
  134.             }   
  135.             con.Close();   
  136.             {   
  137.                 con.Open();   
  138.                 OleDbCommand cmd = new OleDbCommand();   
  139.                 cmd.Connection = con;   
  140.                 string query = "SELECT PRODUCTNAME,QUANTITY,RATE FROM tblSALEITEMDETAILS where INVOICENO='" + combINVOICENO.Text + "'";   
  141.                 cmd.CommandText = query;   
  142.                 OleDbDataAdapter da = new OleDbDataAdapter(cmd);   
  143.                 DataTable dt = new DataTable();   
  144.                 da.Fill(dt);  
  145.                 dataGridITEMDETAILS.DataSource = dt;   
  146.                 PRODUCTNAME.DisplayMember = "PRODUCTNAME";   
  147.                 con.Close();   
  148.             }   
  149.         }   
  150.     }

Answers (2)