5 Replies - 339 Views - Last Post: 11 December 2019 - 01:05 PM Rate Topic: -----

#1 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 12:22 PM

I am using the following method to load an excel file into a datagridview:

 private void XLtoDGV()

        {
            
            try

            {

                // Location path variables

                string sourcePath = @"C:\TEST\AppFILES";
                string targetPath = @"C:\";

                // source and destination file variables with paths

                string destFile = Path.Combine(targetPath, "Test.xls");
                string sourceFile = Path.Combine(sourcePath, "Test.xls");

                // To copy a folder's contents to a new location:
                // Create a new target folder, if necessary.
                if (!Directory.Exists(targetPath))
                {
                    Directory.CreateDirectory(targetPath);
                }

                // To copy a file to another location and 
                // overwrite the destination file if it already exists.
                File.Copy(sourceFile, destFile, true);

                String name = "Test";
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                    "C:\\Test.xls" +
                                    ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

                    OleDbConnection con = new OleDbConnection(constr);
                    OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                    con.Open();

                    OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                    System.Data.DataTable data = new DataTable();

                    sda.Fill(data);

                    // Remove space from header fields in datatable . 
                    // Replace " ", with "_"

                    foreach (DataColumn column in data.Columns)
                    {
                        column.ColumnName = column.ColumnName.Replace(" ", "_");
                    }

                    sda.Fill(data);
                    dataGridView1.DataSource = data;

                    txtTotalItem.Text = dataGridView1.Rows.Count.ToString();  // Get total row count of Datagridview


                    cboColumn.Items.Clear(); // Clear combo Box Data    


                    string[] ColNameList = data.Columns.OfType<DataColumn>().Select(x => x.ColumnName).ToArray();

                    // Adding Column Names in ComoBox List

                    cboColumn.Items.AddRange(ColNameList);
                    if (cboColumn.Items.Count > 0) cboColumn.SelectedIndex = 5;

              
                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
           

        }




The problem I am having is the file loads into the datagridview but with double the records initially and the count shows it as well. So for example if the excel file contains 45 records the datagridview is showing 90 initially when loaded, until I use the search button which applies a filter.

The btn_Search_Click code is below:

    private void btnSearch_Click(object sender, EventArgs e)
        {
            //  Search datagridview using Filter

            try
            {

                ((DataTable)dataGridView1.DataSource).DefaultView.RowFilter = string.Format("" + cboColumn.Text + " like '%{0}%'", txtSearch.Text.Trim().Replace("'", "''"));
                txtTotalItem.Text = (dataGridView1.Rows.Count).ToString();
            }

            //Error Handle

            catch (Exception ex) { MessageBox.Show(ex.ToString()); }
        }




After clicking this, the count goes to 45 records and the datagridview displays the correct amount as well.

I cannot determine why this is happening. I check the excel file and it always contains half the records that are initally being loaded into the DGV because they are doubled in DGV at first until the Search button is clicked, so I do not think the issue is with the excel file.

Any insight as to what could be causing this would be greatly appreciated. Thank you.

If you would like me to attach a sample of the excel file being used , I can do that , just let me know.

Is This A Good Question/Topic? 0
  • +

Replies To: Datagridview showing double records when using Excel Source file

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 12:43 PM

Lines 42 and 52 both indicate you are filling the data table twice.

sda.Fill(data);


You can verify this by putting a breakpoint on line 42, putting the 'data' object in a watch window, and stepping through the coded to see the size increase.
Was This Post Helpful? 0
  • +
  • -

#3 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 12:48 PM

How do you put the data object in a watch window?
Was This Post Helpful? 0
  • +
  • -

#4 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 12:57 PM

Great catch. I should have seen that I was using the

sda.Fill(data);




twice. That totally explains it. Thank you. Sometimes another set of eyes is all you need :)

Thanks again modi123_1.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 01:03 PM

View PostNarflak, on 11 December 2019 - 01:48 PM, said:

How do you put the data object in a watch window?

Highlight and drag to the window, or type in the variable name/variable+property/line to evaluate.

https://docs.microso...ws?view=vs-2019
Was This Post Helpful? 0
  • +
  • -

#6 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Datagridview showing double records when using Excel Source file

Posted 11 December 2019 - 01:05 PM

Thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1