How to combobox select from sqlite database

Article
2020-03-20

Question

_{Friday, March 20, 2020 7:12 PM}

I have a winform connected to a sqlite database, the database has 2 tables (DataBase and Teams). I have a combobox when selected need to show whats in the Teams database. I cant figure out how to show. My code is below, what am i doing wrong? I get no errors just a blank combobox.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Collections;
using System.Data.SqlClient;

namespace Game_Schedule
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }

      //  private int i = 0;

        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter DB;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadData();
        //    fillcomobo();
            
        }


        // combobox to select from sub table
        protected void FillCombobox()
        {
            sql_con = new SQLiteConnection
                ("Data Source=GameDay.db;Version=3;new=False;Compress=True;");
            SQLiteConnection conn = new SQLiteConnection(sql_con);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string CommandText = "select Teams from Team_Names ";
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                DB = new SQLiteDataAdapter(CommandText, sql_con);
                DS.Reset();
                da.SelectCommand = sql_cmd;
                DB.Fill(DS);
                cb_Home.DisplayMember = "Team_Names";
              //  cb_Home.ValueMember = "ID";
                cb_Home.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                //Exception Message
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

                   

       

        //Set Connection
        private void SetConnection()
        {
            sql_con = new SQLiteConnection
                ("Data Source=GameDay.db;Version=3;new=False;Compress=True;");
        }

        //set executequery
        private void ExcuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();

        }

        //set loadDB
        private void LoadData()
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            string CommandText = "select * from DataBase";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            dataGridView1.DataSource = DT;
            sql_con.Close();
        }

        #region Team Information

        private void teamToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Teams t = new Teams();
            t.Show();
            this.Visible = true;
        }

               

private void btn_Save_Click(object sender, EventArgs e)
        {
            
            string txtQuery = "insert into DataBase (Date,Time,Home,Away,Location,Win)values('" + tb_Date.Text + "','" +tb_Time.Text + "','" + cb_Home.Text + "', '"+ cb_Away.Text + "', '" + tb_Location.Text + "', '" + tb_Win.Text + "')";
            ExcuteQuery(txtQuery);
            LoadData();
            tb_Home.Clear();
            tb_Away.Clear();
           // tb_Location.Clear();
           // tb_Win.Clear();
        }
    }
}
#endregion

Booney440

All replies (3)

_{Friday, March 20, 2020 10:49 PM ✅Answered}

Hello,

The following uses SQL-Server, does not change how the end result as the focus is on DataTable objects, a BindingSource and SelectedIndexChanged event of the primary ComboBox.

Form code

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

namespace ComboBoxToComboBoxSqlServer
{
    public partial class Form1 : Form
    {
        readonly BindingSource _productBindingSource =
            new BindingSource();
        public Form1()
        {
            InitializeComponent();
            CategoryComboBox.SelectedIndexChanged += CategoryComboBox_SelectedIndexChanged;
        }

        private void CategoryComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (CategoryComboBox.SelectedItem == null) return;

            // set the filter to the current category
            var categoryIdentifier = ((DataRowView)CategoryComboBox.SelectedItem)
                .Row.Field<int>("CategoryId");

            _productBindingSource.Filter = $"CategoryId = {categoryIdentifier}";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            var ops = new DataOperations();
            CategoryComboBox.DisplayMember = "CategoryName";

            // load DataTable
            CategoryComboBox.DataSource = ops.CategoryDataTable();

            ProductComboBox.DisplayMember = "ProductName";

            // load DataTable
            _productBindingSource.DataSource = ops.ProductDataTable();
            ProductComboBox.DataSource = _productBindingSource;
        }
    }
}

The following is irrelevant, simply shows I've loaded data into DataTable containers.

using System.Data;
using System.Data.SqlClient;
using BaseConnectionLibrary.ConnectionClasses;

namespace ComboBoxToComboBoxSqlServer
{
    public class DataOperations : SqlServerConnection
    {
        public DataOperations()
        {
            DatabaseServer = "KARENS-PC";
            DefaultCatalog = "NorthWindAzure";
        }

        public DataTable CategoryDataTable()
        {
            DataTable dt = new DataTable();

            using (var cn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand() {Connection = cn})
                {
                    var selectStatement = 
                        "SELECT CategoryId, CategoryName " + 
                        "FROM Categories ORDER BY CategoryName";

                    cmd.CommandText = selectStatement;
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
            }

            return dt;
        }
        public DataTable ProductDataTable()
        {
            DataTable dt = new DataTable();

            using (var cn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand() { Connection = cn })
                {
                    var selectStatement = 
                        "SELECT ProductId,CategoryId, ProductName " + 
                        "FROM Products ORDER BY ProductName";

                    cmd.CommandText = selectStatement;
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
            }

            return dt;
        }
    }
}

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Saturday, March 21, 2020 5:04 PM}

This populate the combobox , thank you for the help Karen.

protected void FillCombobox()
{
sql_con = new SQLiteConnection
("Data Source=GameDay.db;Version=3;new=False;Compress=True;");

SetConnection();
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
string CommandText = "select * from Teams";
DB = new SQLiteDataAdapter(CommandText, sql_con);
DS.Reset();
DB.Fill(DS);
DT = DS.Tables[0];
cb_Home.DisplayMember = "Team_Names";
cb_Home.ValueMember = "Team_Names";
cb_Home.DataSource = DS.Tables[0];
sql_con.Close();

}

Booney440

_{Sunday, March 22, 2020 3:55 PM}

Happy to assist :-)

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Share via

How to combobox select from sqlite database

Question

All replies (3)

Additional resources