none
How to combobox select from sqlite database RRS feed

  • Question

  • 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


    • Edited by Booney440 Friday, March 20, 2020 8:45 PM update
    Friday, March 20, 2020 7:12 PM

Answers

  • 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
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Booney440 Sunday, March 22, 2020 3:18 PM
    Friday, March 20, 2020 10:49 PM
    Moderator

All replies

  • 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
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Booney440 Sunday, March 22, 2020 3:18 PM
    Friday, March 20, 2020 10:49 PM
    Moderator
  • 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


    • Edited by Booney440 Sunday, March 22, 2020 3:18 PM update
    Saturday, March 21, 2020 5:04 PM
  • Happy to assist :-)

    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
    profile for Karen Payne on Stack Exchange

    Sunday, March 22, 2020 3:55 PM
    Moderator