SQLite and Windows 2017 C# query of database file

L Hazlewood

In this section of code I select a range of dates to use to get the rows from a SQLite database file "Mytable1 table name "Date". The SELECT command I am using works correctly when using a SQLite database manager program but using C# in a windows form the dates returned are not between the date range requested.

Anyone run into this issue before?

I use a DataView Grid to display the return data and a button to SELECT the table and "Date coloum. A DataBase file (.zip) and a few lines of code I am using.

using System.Data.SQLite;
//SQL connection, Adaptor and data table
SQLiteConnection myConn;
SQLiteDataAdapter myAdapter;
DataTable myTable;
// Loads page and opens data base
private void Reports_Load_1(object sender, EventArgs e) // add event load
{
// check if file exists
if (File.Exists(Application.StartupPath + "/mytable1.db3")) // change back to mytables when testing is done
{
string myDbPath = Application.StartupPath + "/mytable1.db3"; // change back to mytables when testing is done
// if DB does not exist, it will create it.
myConn = new SQLiteConnection("Data Source=" + myDbPath);
myConn.Open(); // open DB file
}
else
{
MessageBox.Show("Data Base not found:" + Environment.NewLine + // addes a new line
" Check file location and name");
}
}
private void BtnGenerate_Click(object sender, EventArgs e)
{
myAdapter = new SQLiteDataAdapter ("SELECT DISTINCT * FROM Transaction_Table WHERE Date >= "+2018/01/01+" AND Date <= "+2018/01/04+";" , myConn); // this works in SQLite database browser. In visualstudio 2015 C# gives all dates regardless of the date rang you change it to.
// myAdapter = new SQLiteDataAdapter("SELECT DISTINCT * FROM Transaction_Table WHERE date BETWEEN " + 2018/01/01 + " AND " + 2018/01/04 + ";", myConn); // this also gets all dates
myTable = new DataTable();
// myReport = new DataTable(); // Don't forget initialize!
myAdapter.Fill(myTable); // once you get the data
// try this change
// bindingSource1.DataSource = myTable;
// SQLiteCommandBuilder authomatically generates
// neccessary INSERT, UPDATE, DELETE SQL queries.
new SQLiteCommandBuilder(myAdapter);
// ----------- Binding DataTable To DataGridView -----------
// ---------------------------------------------------------
// DataGridView visualizes DataTable's data in the window.
// ! THIS CODE IS REQUIRED (if we want DataGridView)
DataGridView1.DataSource = myTable;
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
With the code like that, will it not be 2018 divided by 1 divided 4? What do you think?

Why are you ending the string and starting it again? I just googled and the correct syntax is as follows:
myAdapter = new SQLiteDataAdapter ("SELECT DISTINCT * FROM Transaction_Table WHERE Date >= date('2018-01-01') AND Date <= date('2018-01-04');" , myConn);
Also as a very general guideline NEVER use reserved words for business purpose. Use a bit of imagination and give some proper names, for example, TransactionDate, etc.
L Hazlewood8y ago
Thanks Nitin, It was a syntax issue. Below is the line of code I ended up testing with. I also change the name of the table as suggested. To TransDate.

myAdapter = new SQLiteDataAdapter("SELECT DISTINCT * FROM Transaction_Table WHERE TransDate >= ('2018/01/01') AND TransDate <= ('2018/01/04');", myConn);

Now my next issue is how to use varables, (startDate and endDate) instead of the strings ('2018/01/01') and ('2018/01/04') .
I am presently using MonthCalander and get the dates as stings

string startDate = monthCalendar1.SelectionRange.Start.ToString("yyyy/MM/dd");
string endDate = monthCalendar1.SelectionRange.End.ToString("yyyy/MM/dd");

my new SQl select command looks like this but it throughs a syntax error and I can not see why.?

myAdapter = new SQLiteDataAdapter("SELECT DISTINCT * FROM Transaction_Table WHERE TransDate between "startDate" AND "endDate;", myConn);