0

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
3
Views
5 Years
Discussion Span
Last Post by g1skyfire
0

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();
        }
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.