How to Load Data From Database in a Combobox using C#

Submitted by joken on Tuesday, April 22, 2014 - 20:07.
This tutorial is a continuation of our last topic called “How to Create a Simple Record Navigation in C#”. At this time, I’m going to show you how to load data from Microsoft Access Database into a Combobox using C#. To start with this application, we need to open our project called “student_info” then we will apply the loading of data From Database in a combobox. To do this, change the input field of “Course :” from textbox to combobox. And this will look like as shown below. q1 Next, we will create a new sub procedure called “loadDatatoCombobox” that will do the loading if data from database into combobox item. And here’s the following code: 
  1. //create a new datatable
  2.             DataTable table = new DataTable();
  3.             //create our SQL SELECT statement
  4.             string sql = "Select course from tblstudent";
  5.             try
  6.             {
  7.                 conn.Open();
  8.                 //then we execute the SQL statement against the Connection using OleDBDataAdapter
  9.                 OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
  10.                 //we fill the result to dt which declared above as datatable
  11.                 da.Fill(table);
  12.                 //we add new entry to our datatable manually 
  13.                 //becuase Select Course is not Available in the Database
  14.                 table.Rows.Add("Select Course");
  15.                 //set the combobox datasource 
  16.                 comboBox1.DataSource = table;
  17.                 //choose the specific field to display
  18.                 comboBox1.DisplayMember = "course";
  19.                 comboBox1.ValueMember = "course";
  20.                 //set default selected value
  21.                 comboBox1.SelectedValue = "Select Course";
  22.             }
  23.             catch (Exception ex)
  24.             {
  25.                 //this will display some error message if something 
  26.                 //went wrong to our code above during execution
  27.                 MessageBox.Show(ex.ToString());
  28.             }
At this time, when the form load, we will need to call the sub procedure that we create while ago to finally populate the combobox dynamically. To do this double click the form and add a single line of code and the form load function will look like as shown below. 
  1. private void Form1_Load(object sender, EventArgs e)
  2.         {
  3.             loadDatatoCombobox();
  4.         }
And you can now test your application by pressing “F5” or click the start button. Then it should look like as shown below. q2 And here's e the new modified code used for this application. 
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9. using System.Data.OleDb;
  10. namespace student_info
  11. {
  12.     public partial class Form1 : Form
  13.     {
  14.         //declare new variable named dt as New Datatable
  15.         DataTable dt = new DataTable();
  16.         //this line of code used to connect to the server and locate the database (usermgt.mdb)
  17.         static string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application.StartupPath + "/studentdb.mdb";
  18.         OleDbConnection conn = new OleDbConnection(connection);
  19.  
  20.         int curRecord = 0;
  21.         int totRecord = 0;
  22.  
  23.         public Form1()
  24.         {
  25.             InitializeComponent();
  26.         }
  27.  
  28.         private void btnload_Click(object sender, EventArgs e)
  29.         {
  30.             //create a new datatable
  31.             dt = new DataTable();
  32.             //create our SQL SELECT statement
  33.             string sql = "Select * from tblstudent";
  34.             //then we execute the SQL statement against the Connection using OleDBDataAdapter
  35.             OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
  36.             //we fill the result to dt which declared above as datatable
  37.             da.Fill(dt);
  38.  
  39.             //set the curRecord to zero
  40.             curRecord = 0;
  41.             //get the total number of record available in the database and stored to totRecord Variable
  42.             totRecord = dt.Rows.Count;
  43.  
  44.  
  45.             //then we populate the datagridview by specifying the datasource equal to dt
  46.             dataGridView1.DataSource = dt;
  47.  
  48.         }
  49.  
  50.         private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
  51.         {
  52.             //it checks if the row index of the cell is greater than or equal to zero
  53.             if (e.RowIndex >= 0)
  54.             {
  55.                 //gets a collection that contains all the rows
  56.                 DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
  57.                 //populate the textbox from specific value of the coordinates of column and row.
  58.                 txtid.Text = row.Cells[0].Value.ToString();
  59.                 txtfname.Text = row.Cells[1].Value.ToString();
  60.                 txtlname.Text = row.Cells[2].Value.ToString();
  61.                 comboBox1.Text = row.Cells[3].Value.ToString();
  62.                 txtgender.Text = row.Cells[4].Value.ToString();
  63.                 txtaddress.Text = row.Cells[5].Value.ToString();
  64.  
  65.             }
  66.  
  67.         }
  68.  
  69.         private void Form1_Load(object sender, EventArgs e)
  70.         {
  71.             loadDatatoCombobox();
  72.         }
  73.         private void loadDatatoCombobox()
  74.         {
  75.  
  76.             //create a new datatable
  77.             DataTable table = new DataTable();
  78.             //create our SQL SELECT statement
  79.             string sql = "Select course from tblstudent";
  80.             try
  81.             {
  82.                 conn.Open();
  83.                 //then we execute the SQL statement against the Connection using OleDBDataAdapter
  84.                 OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
  85.                 //we fill the result to dt which declared above as datatable
  86.                 da.Fill(table);
  87.                 //we add new entry to our datatable manually 
  88.                 //becuase Select Course is not Available in the Database
  89.                 table.Rows.Add("Select Course");
  90.                 //set the combobox datasource 
  91.                 comboBox1.DataSource = table;
  92.                 //choose the specific field to display
  93.                 comboBox1.DisplayMember = "course";
  94.                 comboBox1.ValueMember = "course";
  95.                 //set default selected value
  96.                 comboBox1.SelectedValue = "Select Course";
  97.             }
  98.             catch (Exception ex)
  99.             {
  100.                 //this will display some error message if something 
  101.                 //went wrong to our code above during execution
  102.                 MessageBox.Show(ex.ToString());
  103.             }
  104.  
  105.         }
  106.  
  107.         private void Navigate()
  108.         {
  109.             txtid.Text = dt.Rows[curRecord][0].ToString();
  110.             txtfname.Text = dt.Rows[curRecord][1].ToString();
  111.             txtlname.Text = dt.Rows[curRecord][2].ToString();
  112.             comboBox1.Text = dt.Rows[curRecord][3].ToString();
  113.             txtgender.Text = dt.Rows[curRecord][4].ToString();
  114.             txtaddress.Text = dt.Rows[curRecord][5].ToString();
  115.         }
  116.  
  117.         private void btnprev_Click(object sender, EventArgs e)
  118.         {
  119.             //decrement the curRecord
  120.             curRecord--;
  121.             if (curRecord < 0)
  122.                curRecord = totRecord - 1;
  123.             //call subroutine
  124.             Navigate();
  125.  
  126.  
  127.         }
  128.  
  129.         private void btnnext_Click(object sender, EventArgs e)
  130.         {
  131.             curRecord++;
  132.             if (curRecord >= totRecord)
  133.                 curRecord = 0;
  134.             Navigate();
  135.         }
  136.  
  137.  
  138.  
  139.  
  140.     }
  141. }

Tags

Add new comment

About text formats
  • 1792 views