locked
Sqlite populate textbox from DateTimePicker RRS feed

  • Question

  • I have a SQlite database with data when I select a date from the DateTimePicker I need it to populate the data from that date.

    I am just starting with 2 textbox's, when I select the date nothing happens. I am not using DataGridView, this is new territory for me. 

    Please help.

    Thank you

    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;
    
    namespace ShiftEnd_2020
    {
        public partial class Attendance : Form
        {
            public Attendance()
            {
                InitializeComponent();
    
    
            }
    
            private void btn_Save_Click(object sender, EventArgs e)
            {
                var data = new Data();
                data.AttendanceData(
                    dateTimePicker1.Value,
                    tb_Leader_Present.Text,
                    tb_Leader_Online.Text);
            }
             
    
            private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=Account.db");
                con.Open();
    
                if (tb_Leader_Present.Text != "")
                {
                    SQLiteCommand cmd = new SQLiteCommand("SELECT Leader_Present ," +
                        " Leader_Online from Attendance where Production_Date = @Production_Date", con);
                    cmd.Parameters.AddWithValue("@Production_Date",(tb_Leader_Present.Text));
                    SQLiteDataReader da = cmd.ExecuteReader();
                    while (da.Read())
                    {
                        dateTimePicker1.Text = da.GetValue(0).ToString();
                        tb_Leader_Present.Text = da.GetValue(1).ToString();
                        tb_Leader_Online.Text = da.GetValue(2).ToString();
                    }
    
                }
            }
        }
    }


    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.SQLite;
    
    namespace ShiftEnd_2020
    {
    
    
        public class Data
        {
            public string connectionString { get; set; }
            string connection;
    
            public Data()
            {
                createDataBase();
                getConnection();
            }
            public void getConnection()
            {
                connection = @"Data Source=Account.db; Version=3";
                connectionString = connection;
            }
    
            public void createDataBase()
            {
                if (!File.Exists("Account.db"))
                {
                    try
                    {
                        File.Create("Account.db");
    
                        createUserTable();
                        createAttendanceTable(); /// attendance table
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
                else
                {
                    createUserTable();
                    createAttendanceTable(); /// attendance table
                }
            }
    
            private void createUserTable()
            {
                try
                {
                    getConnection();
                    using (SQLiteConnection con = new SQLiteConnection(connection))
                    {
                        con.Open();
                        SQLiteCommand cmd = new SQLiteCommand();
    
    
                        string query = @"CREATE TABLE Booney ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Username Text(25), Password Text(25))";
                        cmd.CommandText = query;
                        cmd.Connection = con;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
    
            }
            private void createAttendanceTable()  /// Attendance
            {
                try
                {
                    getConnection();
                    using (SQLiteConnection con = new SQLiteConnection(connection))
                    {
                        con.Open();
                        SQLiteCommand cmd = new SQLiteCommand();
    
                        string query = @"CREATE TABLE Attendance ( Production_Date DATETIME PRIMARY KEY, Leader_Present Text(25), Leader_Online Text(25))";
                        cmd.CommandText = query;
                        cmd.Connection = con;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
    
            }
    
            public int checkAccount(string username)
            {
                using (SQLiteConnection con = new SQLiteConnection(connectionString))
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    con.Open();
    
                    int count = 0;
                    string query = @"Select * FROM Booney WHERE Username='" + username + "'";
                    cmd.CommandText = query;
                    cmd.Connection = con;
    
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        count++;
                    }
                    return count;
                }
            }
    
            public void insertData(string username, string password)
            {
                using (SQLiteConnection con = new SQLiteConnection(connectionString))
                {
                    con.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    string query = @"INSERT INTO Booney(Username, Password) VALUES(@username, @password)";
                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.Parameters.Add(new SQLiteParameter("@username", username));
                    cmd.Parameters.Add(new SQLiteParameter("@password", password));
                    cmd.ExecuteNonQuery();
                }
            }
    
            public int checkAttendance(string LeaderPresent)
            {
                using (SQLiteConnection con = new SQLiteConnection(connectionString))
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    con.Open();
    
                    int count = 0;
                    string query = @"Select * FROM Attendance WHERE Leader_Present='" + LeaderPresent + "'";
                    cmd.CommandText = query;
                    cmd.Connection = con;
    
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        count++;
                    }
                    return count;
                }
            }
    
            public void AttendanceData(DateTime Production_Date, string Leader_Present, string Leader_Online)
            {
                using (SQLiteConnection con = new SQLiteConnection(connectionString))
                {
                    con.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    string query = @"INSERT INTO Attendance(Production_Date,Leader_Present, Leader_Online) VALUES(@Production_Date,@Leader_Present, @Leader_Online)";
                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.Parameters.Add(new SQLiteParameter("@Production_Date", Production_Date.Date));
                    cmd.Parameters.Add(new SQLiteParameter("@Leader_Present", Leader_Present));
                    cmd.Parameters.Add(new SQLiteParameter("@Leader_Online", Leader_Online));
                    cmd.ExecuteNonQuery();
                }
            }
    
        }
    
    }


    Booney440

    Friday, November 27, 2020 1:24 PM

Answers

  • Try something like this:

    private void dateTimePicker1_ValueChanged(object sender, EventArgs e)

    {

       using( SQLiteConnection con = new SQLiteConnection(@"Data Source=Account.db"))

       {

          con.Open();

          SQLiteCommand cmd = new SQLiteCommand("SELECT Leader_Present ," +

                        " Leader_Online from Attendance where Production_Date = @Production_Date", con);

          cmd.Parameters.AddWithValue("@Production_Date", dateTimePicker1.Value.Date);

          SQLiteDataReader dr = cmd.ExecuteReader();

          if (dr.Read())

          {

             tb_Leader_Present.Text = dr.GetString(0);

             tb_Leader_Online.Text = dr.GetString(1);

          }

          else

          {

             tb_Leader_Present.Text = "";

             tb_Leader_Online.Text = "";

          }

       }

    }

    This code needs some adjustments if Production_Date column contains irrelevant time part.





    • Edited by Viorel_MVP Saturday, November 28, 2020 9:25 AM
    • Marked as answer by Booney440 Saturday, November 28, 2020 1:45 PM
    Saturday, November 28, 2020 9:20 AM