Finding certain cells in a datatable and adding the values.

Question

g1skyfire 0 Newbie Poster

12 Years Ago

Here's a sample of the excel data:
column A:
partnumber
AL-01-BLT
AL-01-ATV
AL-01-BRS
AL-01-BAR

column B:
price
20
21
40
45

When the user selects the partnumber in combobox1 and combobox2, it should add the total price and output it in label1.. For some reason I'm getting an error now in line

MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';");

Here's the sample code I have so far.

public void GetData(string excelFile)
        {
            System.Data.DataSet DtSet;            
            System.Data.OleDb.OleDbConnection MyConnection;            
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';");
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
            MyCommand.TableMappings.Add("Table", "");
            DtSet = new System.Data.DataSet();
            DataTable dt = new DataTable();
            DtSet.Tables.Add(dt);
            MyCommand.Fill(DtSet);
            MyCommand.Fill(dt);
            dataGridView1.DataSource = DtSet.Tables[0];
            string cb1 = "[partnumber] = '" + comboBox1.Text +"'";
            string cb2 = "[partnumber] = '" + comboBox2.Text + "'";
            DataRow[] rcb1 = dt.Select(cb1);
            DataRow[] rcb2 = dt.Select(cb2);
            label1.Text = string.Empty;
            decimal sumprice = 0;
            label1.Text += rcb1[0].ToString() + " " + rcb1[1].ToString() + "\n";
            label1.Text += rcb2[0].ToString() + " " + rcb2[1].ToString() + "\n";
            sumprice = Convert.ToDecimal(rcb1[1].ToString()) + Convert.ToDecimal(rcb2[1].ToString());            
            label1.Text += "\nTotal: " + sumprice;
            MyConnection.Close();
        }

1 Contributor
1 Reply
158 Views
8 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by g1skyfire

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

g1skyfire 0 Newbie Poster · Answer 1 · 2011-12-16T04:01:25+00:00

Oooh nevermind! I fixed it! lol

public void GetData(string excelFile)
        {
            System.Data.DataSet DtSet;            
            System.Data.OleDb.OleDbConnection MyConnection;            
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';");
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
            MyCommand.TableMappings.Add("Table", "");
            DtSet = new System.Data.DataSet();
            DataTable dt = new DataTable();
            DtSet.Tables.Add(dt);
            MyCommand.Fill(DtSet);
            MyCommand.Fill(dt);
            dataGridView1.DataSource = DtSet.Tables[0];
            string cb1 = "[partnumber] = '" + comboBox1.Text +"'";
            string cb2 = "[partnumber] = '" + comboBox2.Text + "'";
            DataRow[] rcb1 = dt.Select(cb1);
            DataRow[] rcb2 = dt.Select(cb2);
            label1.Text = string.Empty;
            decimal sumprice = 0, cbp1 = 0, cbp2 = 0;
            foreach (DataRow row in rcb1)
            {
                label1.Text += row[0].ToString() + " " + row[1].ToString() + "\n";
                cbp1 = Convert.ToDecimal(row[1].ToString());
            }
            foreach (DataRow row in rcb2)
            {
                label1.Text += row[0].ToString() + " " + row[1].ToString() + "\n";
                cbp2 = Convert.ToDecimal(row[1].ToString());
            }
            sumprice = cbp1 + cbp2;
            label1.Text += "\nTotal: " + sumprice;
            MyConnection.Close();
        }