SQLite and Windows 2017 C# query of database file

Question

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; }

Nitin Sontakke · 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 , 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 Hazlewood · Answer

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 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);