In this tutorial, I will show you how to fill a Combobox with a Data from Microsoft Access Database. As well as how to search specific record from the database using the data in a combobox. To start building with this project, create a new C# project and design the user interface looks like as shown below.
Then save it as “ComboFilter” or whatever you want to name this application. After this step, let’s now create a new database in Microsoft Access named “studentdb”. Then make sure to save this database inside the bin folder of our project called “ComboFilter”. And the table design looks like as shown below.
And the figure below show the sample data for this table, and you can follow this also if you wanted to have a uniform output with me.
Since we have set our database, let us go back to our project called “ComboFilter”. Then we will add code to it so that it will be functional just like we expect. To do this, And add this “using System.Data.OleDb;” single line of code above “namespace WindowsFormsApplication2” and it should be look like as shown below.
The new added code is a .NET Framework Data Provider for OLE DB describes a collection of classes used to access an OLE DB data source in the managed space.
Next, we will add a code under the “ public partial class Form1 : Form ”, and here’s the following code:
//declare new variable named dt as New Datatable
DataTable dtable
= new DataTable
(); //this line of code used to connect to the server and locate the database (studentdb.mdb)
static string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application.StartupPath + "/studentdb.mdb";
OleDbConnection conn
= new OleDbConnection
(connection
);
After we set our database connection using our code above, let’s now proceed on adding a code on how to load data from the database into Combobox. We only have to do is to add the following code when the form is being loaded. And here’s the following code:
private void Form1_Load(object sender, EventArgs e)
{
//create a new datatable
DataTable dtable
= new DataTable
(); //create our SQL SELECT statement
string sql = "Select IDNO from tblstudent";
try
{
conn.Open();
//then we execute the SQL statement against the Connection using OleDBDataAdapter
OleDbDataAdapter da
= new OleDbDataAdapter
(sql, conn
); //we fill the result to dt which declared above as datatable
da.Fill(dtable);
//we add new entry to our datatable manually
//becuase Select Course is not Available in the Database
dtable.Rows.Add("Select IDNO");
//set the combobox datasource
ComboBox1.DataSource = dtable;
//choose the specific field to display
ComboBox1.DisplayMember = "IDNO";
ComboBox1.ValueMember = "IDNO";
//set default selected value
ComboBox1.SelectedValue = "Select IDNO";
}
catch (Exception ex)
{
//this will display some error message if something
//went wrong to our code above during execution
MessageBox.Show(ex.ToString());
}
conn.Close();
}
And if you try to run this application, you can directly observe that the Combobox is not empty and it is filled out with data from the database. And here’s how it looks like.
Next, when the user selects a specific ID Number of specific students, and the “Go” button is clicked. Then information about the student will then display in the text box provided. To do this, double click the “Go” button and add the following code:
private void btngo_Click(object sender, EventArgs e)
{
//create a new datatable
DataTable dtable
= new DataTable
(); //create our SQL SELECT statement
string sql = "Select * from tblstudent WHERE IDNO = '" + ComboBox1 .SelectedValue + "'";
try
{
conn.Open();
//then we execute the SQL statement against the Connection using OleDBDataAdapter
OleDbDataAdapter da
= new OleDbDataAdapter
(sql, conn
); //we fill the result to dt which declared above as datatable
da.Fill(dtable);
//load Data from DataTable to Specific Textboxes
txtFname.Text = dtable.Rows[0][2].ToString();
txtLname.Text = dtable.Rows[0][3].ToString();
txtcourse .Text = dtable.Rows [0][4].ToString();
txtGender.Text = dtable.Rows[0][5].ToString();
txtAddress.Text = dtable.Rows[0][6].ToString();
}
catch (Exception ex)
{
//this will display some error message if something
//went wrong to our code above during execution
MessageBox.Show(ex.ToString());
}
conn.Close();
}
And when you try to run this application, it will then look like as shown below.