Binding DataSource to ComboBox in C#

Question

I am having way too many issues with this. Here is the code snippet below.

using System;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace Control_Creation_Build_001
{
    public partial class Form1 : Form
    {

        int TopControl = 10;
        private int i;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }


        private void CreateComboBoxButton_Click(object sender, EventArgs e)
        {

            //This code below needs to be fixed.

            string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/POS/[DataBaseFile].mdb";
            var ConnStrX = new SqlConnection(connection);
            string sql = "Select DeterminersWithA.[Consonant Determiner Required] from DeterminersWithA";
            ConnStrX.Open();
            ComboBox cb = new ComboBox();
            this.Controls.Add(cb);
            SqlCommand sc = new SqlCommand(sql, ConnStrX);
            SqlDataReader rd = sc.ExecuteReader();
            cb.Top = TopControl;
            cb.Left = 10;
            TopControl += 20;

            try
            { 
                while (rd.Read())
                {
                    cb.Items.Add(rd.GetValue(1).ToString());
                }
                sc.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to bind data to Combo box");
            }

        }
    }
}

I am hoping users to fix this issue soon.

Regards,

JohnCTX

Answer 1

Hello @JohnCTX

I created a simple code sample that a) keeps code in your form clean from data operations b) simplified all code.

Form code

using System;  
using System.Windows.Forms;  
  
namespace Access_mdb  
{  
    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
        }  
  
        private void LoadDataButton_Click(object sender, EventArgs e)  
        {  
            var cb = new ComboBox()  
            {  
                Left = 10,   
                Top = 10,   
                DataSource = DataOperations.Read()  
            };  
              
            Controls.Add(cb);  
        }  
    }  
}  

Data class

using System;  
using System.Collections.Generic;  
using System.Data.OleDb;  
using System.IO;  
  
namespace Access_mdb  
{  
    public class DataOperations  
    {  
        public static string _connectionString =   
            $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db1.mdb")};";  
  
        public static List<string> Read()  
        {  
              
            var list = new List<string>();  
              
            using (var cn = new OleDbConnection() {ConnectionString = _connectionString})  
            {  
                using (var cmd = new OleDbCommand() {Connection = cn})  
                {  
                    cmd.CommandText = "SELECT [Consonant Determiner Required] FROM DeterminersWithA;";  
                      
                    cn.Open();  
  
                    var reader = cmd.ExecuteReader();  
                    while (reader.Read())  
                    {  
                        list.Add(reader.GetString(0));  
                    }  
                }  
            }  
  
            return list;  
        }  
    }  
}  

Answer 2

You need to use OleDbConnection, OleDbCommand, and OleDbDataReader instead of the SQL versions if you are getting data from an Access Database.

         string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/POS/[DataBaseFile].mdb";
         var ConnStrX = new OleDbConnection(connection);
         string sql = "Select DeterminersWithA.[Consonant Determiner Required] from DeterminersWithA";
         ConnStrX.Open();
         ComboBox cb = new ComboBox();
         this.Controls.Add(cb);
         OleDbCommand sc = new OleDbCommand(sql, ConnStrX);
         OleDbDataReader rd = sc.ExecuteReader();