How to bind Access database to DataTable?

Question

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 ...

Big thanks to Johng for assisting me with the code :)

CURRENT WORKING CODE

        public Form1()
        {
            InitializeComponent();
        }
        public static BindingSource gridBindingSource;

        private void idCbo_SelectedIndexChanged(object sender, EventArgs e)
        {
            string query = idCbo.Text;
            gridBindingSource = (BindingSource)dataGridView1.DataSource;
            if (gridBindingSource != null)
            {
                if (query == "All")
                {
                    gridBindingSource.Filter = "";
                }
                else
                {
                    gridBindingSource.Filter = "convert(id,'System.String') LIKE '%" + query + "%'";
                }
            }
            
        }
private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the '_InfoFinalv_2___CopyDataSet.Info' table. You can move, or remove it, as needed.
            infoTableAdapter.Fill(this._InfoFinalv_2___CopyDataSet.Info);
            idCbo.Items.Add("All");
            for (int i = 1; i < 100; i++)
            {
                idCbo.Items.Add(i);
            }
            idCbo.SelectedIndex = -1;

        }
        private void idReset_Click(object sender, EventArgs e)
        {
            idCbo.SelectedIndex = -1;
        }

Using LIKE '%{query}% with string “numbers” may not give you the results you are wanting. Example; selecting “1” will produce results with “1, 11, 12, 13, 14…21, 31, 41… etc”. — JohnG
– JohnG, Commented Jun 6, 2022 at 17:50
i have yet to find undesirable results from the LIKE '%{query}% all that line is doing is matching numbers from the combo box to the datagridview and then excludes characters in-front and behind the query (this is the purpose of the % symbol) this prevents undesirable results like only numbers with 1 in the front 1 10 11 12 13 14 etc or only 1 as the last character 1 11 21 31 41 51 etc — Sam Wallin
– Sam Wallin, Commented Jun 6, 2022 at 17:58
You state that... "but then searching doesn't work." ... ? ... then what "doesn't work"? — JohnG
– JohnG, Commented Jun 6, 2022 at 18:02
i should probably be more clear on that. 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 — Sam Wallin
– Sam Wallin, Commented Jun 6, 2022 at 18:07
when i change the column titles i get the same thing even when i use the design name from the datagridview (iDDataGridViewTextBoxColumn) — Sam Wallin
– Sam Wallin, Commented Jun 6, 2022 at 18:15

JohnG · Accepted Answer · 2022-06-06 20:11:01Z

2

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 + "%'";
    }
  }
}

edited Jun 6, 2022 at 20:11

answered Jun 6, 2022 at 19:56

JohnG

9,4442 gold badges23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Wallin Over a year ago

so i ran into a few errors on my end but i got it working i changed GridBS.Filter = "EmpID LIKE '%" + query + "%'"; to gridBindingSource.Filter = "convert(id,'System.String') LIKE '%" + query + "%'"; because of a cannot perform like on system int and string error and then i changed idCbo.SelectedIndex = 0; to -1. i also put gridviewBindingSource into public static and changed BindingSource GridBS = (BindingSource)dataGridView1.DataSource; to gridBindingSource = (BindingSource)dataGridView1.DataSource; current code is now in my post at the bottom

WarSam · Accepted Answer · 2022-06-06 20:19:20Z

0

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.

answered Jun 6, 2022 at 20:19

WarSam

271 silver badge5 bronze badges

1 Comment

Sam Wallin Over a year ago

i dont think i am I started the project as a winforms .net framework

Collectives™ on Stack Overflow

How to bind Access database to DataTable?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related