CODEWITHSUNDEEP
c#.netdatabasesearchms-access-2010
Niclas
Niclas

Reputation: 1262

How to create a search box?

Now I have spent several hours trying to work something out and googling around and reading forums, but I have not found my answer!

I need to make some kind of "business application" as a project in my school, where I need to keep overview over a company's customers. I am using Microsoft Visual C# 2010 Express and Access 2010 and writing in C# using OleDb.

My question is:

How do I create a search box/form for an application to search for information in an access-database (.accdb). I want to use a textbox where I write something from my database, for instance the company's name, and the push the search button. Now it should write all the information there is connected to the company's name, found in the database in a DataGrid.

Maybe it is too big a project, so if anyone got a less complicated search function doing something similar I would also appreciate that.

HERE IS THE CODE, GOT AN ERROR IN .Fill(dataset, "Food");. InvalidOperationException was unhandled. Fill: SelectCommand.Connection has not been initialized. Just testing a simple access database with one table named "Food" with FoodID, FoodName and Price inside.

private void button1_Click(object sender, RoutedEventArgs e)
    {
        GetCustomers(textBox1.Text);
    }
        // Load Data from the DataSet into the DataGridView
        private void GetCustomers(string searchTerm)
        {
            DataSet dataset = new DataSet();
            using (OleDbConnection connection = 
            new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Niclas\Desktop\Skole\Programmering\Database\Food.accdb;Persist Security Info=False;"))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(
                "select * from Food where FoodID like ('" + searchTerm + "', connection");
                adapter.Fill(dataset, "Food");
            }

        // Get the table from the data set and bind the data to the grid
        this.dataGrid1 = new System.Windows.Controls.DataGrid();

        dataGrid1.DataContext = dataset.Tables[0];

        }
     }

Upvotes: 1

Views: 44512

Answers (2)

James Toyer
James Toyer

Reputation: 405

I did something like this when I was working as an intern. The way I got around this was to create a form for each type of data I was searching on and then ran a filter on the BindingSource attached to the data grid view. Another neat touch I added was that this filter ran on key stroke, so it automatically as you were typing.

I had a method that was something like this (this has been converted from VB):

private void Search()
{
    string[] strSplitString = null;
    string strSearchString = "";
    strSplitString = Strings.Split(txtSearch.Text.Trim);

    // Check to see if there are any strings
    if (strSplitString.Count == 1) {
        // Construct the search string
        strSearchString = "FirstName Like '%" + strSplitString[0] + "%' Or MiddleName Like '%" + strSplitString[0] + "%' Or LastName Like '%" + strSplitString[0] + "%'";

    } else if (strSplitString.Count > 1) {
        // Construct the string
        strSearchString = "(FirstName Like '%" + strSplitString[0] + "%' Or MiddleName Like '%" + strSplitString[0] + "%' Or LastName Like '%" + strSplitString[0] + "%')";

        // For each word add it to the search string
        for (intWord = 1; intWord <= (strSplitString.Count - 1); intWord++) {
            strSearchString += " And (FirstName Like '%" + strSplitString[intWord] + "%' Or MiddleName Like '%" + strSplitString[intWord] + "%' Or LastName Like '%" + strSplitString[intWord] + "%')";
        }
    }

    // Filter the binding source
    BindingSource.Filter = strSearchString;
}

There may be better ways out there, but this project was also against a rather large access database and seemed to work quite well

Upvotes: 1

Jeremy Thompson
Jeremy Thompson

Reputation: 65554

Can you post the code that you have that doesn't work?

Essentially on the Search Button's click event you would call a function to populate a DataGridView, eg: 

GetCustomers(textBox1.Text);


// Load Data from the DataSet into the DataGridView
private void GetCustomers(string searchTerm)
{
DataSet dataset = new DataSet();
using (OleDbConnection connection = 
    new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;"))
  {
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    adapter.SelectCommand = new OleDbCommand(
        "select * from customer where name like %" + searchTerm + "%", connection);
    adapter.Fill(dataset, "Customers");
  }

    // Get the table from the data set and bind the data to the grid
    DataGridView.DataSource = dataset.Tables[0];
}

I dont have Visual Studio on this PC so there might be syntax errors, but this should get you started.

Upvotes: 1

Related Questions