using variables in Sqlite query c#

shay a

4y1.7kSQL

Hello,

I am trying get the values of a table in Sqlite from the last 2 days, this is my code:

string dt = localDate.ToString("dd-MM-yyyy HH:mm");
string NewDate = localDate.AddDays(-2).ToString("dd-MM-yyyy HH:mm");

string query = "SELECT * FROM Offline WHERE Date <= '$dt' AND Date >= '$NewDate'";
SQLiteCommand myCommand = new SQLiteCommand(query, databaseObject.MyConnection);
databaseObject.OpenConnection();
SQLiteDataReader result = myCommand.ExecuteReader();

when I write the actual date and time in the query i get a result, but when i use the variables i get nothing, not even an error.

can you please help?

thank you.

3 Replies

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

Nitin Sontakke4y ago
Accepted answer
In that case, may be you should accept my post as an answer.

That formally closes this thread.
shay a4y ago
Thank you very much, using concatenate values solved my problems.
Nitin Sontakke4y ago
Disclaimer first, I know NOTHING about SQLite.

Having said that there are couple of things to note here.

1. When dealing with DateTime data type on any platform it is a best practice to format dates in universally understood format, which is by the way yyyy-mm-dd HH:mm:ss. Please consider formatting your dates this way.

2. When you are prepating a query in a string variable, why not just concatenate values while assigning to the query variable? As in:
string query = "SELECT * FROM Offline WHERE Date <= '" + dt + "' AND Date >= '" + NewDate + "'";
Furthermore, please refrain from using generic words like 'Date' as a column name. Doesn't convey much to the future developer. Column name should ideally tell what business function it is serving.
Hope it helps!