How to bind Access database to DataTable?-CodePudding

I'm new to C# and Visual Studio.
I'm working on a project that is searching through a database of information.

I want to bind a database (Microsoft access file) to my datagridview but I want it to work with my preexisting code which utilizes a datatable converted into a dataview.

My database has a lot of information and I don't want to put it in manually. I've tried binding the information directly to the datagridview (through datasource in the properties) but then searching doesn't work**. I've looked into sql but im trying to avoid learning 2 languages at the same time.

My projects basic functionality contains:
1 combobox (idCbo) containing the search query's
1 datagridview for displaying the information

this setup is for searching one column only, im going to duplicate the code for the oher columns

The name of the column in the datagridview selects the column(id) for filtering then the combo box(idCbo) searches that column for matching characters in the datagridview and comboBox list.

the combo box contains the values 1-100 for searching the column

public partial class Form1 : Form
    {
  
        DataTable dt = new DataTable();
        DataView dataView;

        public Form1()
        {
            InitializeComponent();
            dt.Columns.Add("id", typeof(int));

            for (int i = 0; i < 100; i  )
            dt.Rows.Add(i);


            dataView = new DataView(dt);
            this.dataGridView1.DataSource = dataView;

            
        }

        private void idCbo_SelectedIndexChanged(object sender, EventArgs e)
        {
            string query = idCbo.Text;
            dataView.RowFilter = $"convert(id,'System.String') LIKE '%{query}%'";
           
        }
}

** Binding the database to the datagridview while using this code renders column titles but not the information and the code cannot access the database, columns or the rows System.Data.EvaluateException: 'Cannot find column ...

Thanks in advance

CodePudding user response：

If you have set up the grids data source in the designer “correctly” then using the DataView as you want can be simplified by using the existing BindingSource that is usually created when you set up the grid’s data source in the designer.

We can use the existing grid’s BindingSource and then use it’s Filter property as opposed to converting the BindingSource to a DataView to filter. This will allow us to set the filter in the grid WITHOUT having to “change” the grids data source.

Remove all the code you have in the form constructor obviously leaving the InitializeComponent(); and add the code below to the forms Load event. In the load event all we do is set up the combo box with the proper values. I added an “All” option to allow the user to “un-filter” the data in the grid.

private void Form1_Load(object sender, EventArgs e) {
  // TODO: This line of code loads data into the 'database1DataSet.EmployeeDT' table. You can move, or remove it, as needed.
  employeeDTTableAdapter.Fill(this.database1DataSet.EmployeeDT); // <- created by the designer
  idCbo.Items.Add("All");
  for (int i = 1; i < 100; i  ) {
    idCbo.Items.Add(i);
  }
  idCbo.SelectedIndex = 0;
}

Then in the combo boxes SelectedIndexChanged event... change the code as shown below. Cast the grids DataSource to a BindingSource and then use its Filter property.

private void idCbo_SelectedIndexChanged(object sender, EventArgs e) {
  string query = idCbo.Text;
  BindingSource GridBS = (BindingSource)dataGridView1.DataSource;
  if (GridBS != null) {
    if (query == "All") {
      GridBS.Filter = "";
    }
    else {
      GridBS.Filter = "EmpID LIKE '%"   query   "%'";
    }
  }
}

CodePudding user response：

Here's the tip:

On the form load, make an ajax call to the database and fetch only the required data columns. Return data will be in JSON that can be used as data for DataTable.

I used it in an MVC project recently and it works fine. If you would like I can share the detailed code and logic.

Not sharing the code since I'm not sure if you are on .Net MVC.