Sqlite populate textbox from DateTimePicker

[Booney440]

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

Continue reading...