0

I have a project and it need to choose database list on Combobox and show it in Datagridview. Here is my code :

 private void Form2_Load(object sender, EventArgs e)
            {
                OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + @"data source= D:\Database\đồ án\Đồ án.mdb");
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                OleDbDataAdapter da = new OleDbDataAdapter("Select * from Sheet1", con);
                da.Fill(dt);
                da.Dispose();

                comboBox1.DataSource = dt;
                comboBox1.DisplayMember = "Tên đề tài";
                comboBox1.ValueMember = "Mã đề tài";
            }

 private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + @"data source= D:\Database\đồ án\Đồ án.mdb");

            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            OleDbDataAdapter da = new OleDbDataAdapter("Select * from Sheet11 Where Mã đề tài = "+comboBox1.SelectedValue, con);
            da.Fill(dt);
            da.Dispose();
            dataGridView1.DataSource = dt;
        }

When I run this program and click on Combobox, it shows an error:

https://i.stack.imgur.com/C7DdZ.png

At first, I thought 2 sheets (tables) in database was not showing and didn't relationship but when I make it done and tried to start again, it shows an error again. How can I fix it ?

RBK
  • 558
  • 5
  • 16
Matatabi2309
  • 9
  • 3
  • [Does the column name contain spaces?](https://stackoverflow.com/questions/14190798/how-to-select-a-column-name-with-space-between-in-mysql-on-liunx-ubuntu) – TaW May 10 '18 at 10:02
  • Yes, the column name has a spaces – Matatabi2309 May 10 '18 at 10:07
  • So they need to be enclosed by either apostophes or brackets. (The comment was not a question really, but a link) – TaW May 10 '18 at 10:08

2 Answers2

2

2 important notes:

  1. Use parameters not string concatenation
  2. It is bad practice to use spaces on column names. If you do, use brackets

Considering the above, try:

string query="Select * from Sheet11 Where [Mã đề tài] = @param";
OleDbCommand command = new OleDbCommand(query, con);
cmd.Parameters.Add(new OleDbParameter("@param", comboBox1.SelectedValue))
apomene
  • 14,282
  • 9
  • 46
  • 72
1

In addition to the 'spaces in column names' error you also got the order of the binding wrong when you set up the ComboBox.

You need to first set the members and only then the datasource. So change

comboBox1.DataSource = dt;
comboBox1.DisplayMember = "Tên đề tài";
comboBox1.ValueMember = "Mã đề tài";

to:

comboBox1.DisplayMember = "Tên đề tài";
comboBox1.ValueMember = "Mã đề tài";
comboBox1.DataSource = dt;

Now not the whole table is bound but only the columns you want.

Before, the SelectedValue was not a single value but a whole DataRowView from which you would have to select the right item/column..

And, since it was a reference object, casting it to string (either implicitly with the + operator or explicitly with a ToString call) only resulted in the class name System.Data.DataRowView instead of a data value.

Further notes:

  • Avoid spaces in names of any kind; some moderen systems may work with them, but it will make the code less robust. The same goes for non-ascii characters.

  • While the values in your query here are only coming from the database, it is recommended to only use parameterized queries to avoid any sql injection.

  • Since you already have loaded the data into the DataTable you could just as well filter them without going back to the data base again. For this create a BindingSource or a DataView with a RowFilter and bind the DGV to it. For this, make the DataTable a class variable.

TaW
  • 53,122
  • 8
  • 69
  • 111