C # SQL condition WHERE with a date

Question

1.00/5 (1 vote)

See more:

<pre lang="text">
Hello,
I have some concerns in an SQL request.
A la base j’ai une base de donnes Sqlite avec des dates au format texte comme ceci (21/02/2020).
J’affiche mes résultat dans un Datagridview avec cette requête :
Basically I have a Sqlite database with dates in text format like this (02/12/2020).
I display my results in a Datagridview with this query:

C#

string SQL_Tbl_P4_Cmde = "SELECT * FROM Tble_Commande INNER JOIN Tble_Demande ON" + 
                        " (Tble_Demande.dmd_ID = Tble_Commande.cmd_ID) WHERE Tble_Commande.Annee = '" + Annee_en_Cour + "'" +  
                    "AND (Reference LIKE '%" + Txt_P4_recherche_Ref.Text + "%' OR Reference IS NULL)" +
                    "AND (Designations LIKE '%" + Txt_P4_recherche_Article.Text + "%' OR Designations IS NULL)" +
"AND (Date_prise_compte_cmds LIKE '% + Txt_P4_recherche_Date.Text + "%' OR Date_prise_compte_cmds IS NULL)" +

                    "AND (Lieu_livraison LIKE '%" + Cbo_P4_recherche_lieu.Text + "%' OR Lieu_livraison IS NULL)" +
                    "AND (Imputation LIKE '%" + Cbo_P4_recherche_Imput.Text + "%' OR Imputation IS NULL)" +
                    "AND (Type_cmds LIKE '%" + Cbo_P4_recherche_TypeCmds.Text + "%' OR Type_cmds IS NULL)";

Recently I changed the date format of my columns like this (2020-02-12)
I encountered problems with this line of code:

C#

"AND (Date_prise_compte_cmds LIKE '% + Txt_P4_recherche_Date.Text + "%' OR Date_prise_compte_cmds IS NULL)" +

I modified by this new line like this:
(To find the error I replaced my textbox with a concrete date.)

C#

"AND (Date_prise_compte_cmds LIKE '2020-02-12%' OR Date_prise_compte_cmds IS NULL)" +

This is the problem, if I note the complete date as ('2020-02-12%') it does not work.

Si je note la date comme ('2020-02-1%' ) ça fonctionne un peu !
Here is the error message :
The index was out of range. It must not be negative and must be less than the size of the collection.
Parameter name: index

Who can help me ?
Thank you

What I have tried:

"AND (Date_prise_compte_cmds LIKE '2020-02-12%' OR Date_prise_compte_cmds IS NULL)" +

Posted 10-Mar-20 7:09am

LSB71

Updated 10-Mar-20 8:45am

Add a Solution

Comments

phil.o 10-Mar-20 13:13pm

Why are you storing dates as strings? There is the real issue you should tackle asap.
Always use proper datatype; or be prepared for poor performance and frequent debugging nightmares.

LSB71 10-Mar-20 13:24pm

How should I store them in my Sqlite database?

Richard MacCutchan 10-Mar-20 13:27pm

Use one of the Date types for SQLite, as explained in the documentation.

3 solutions

Solution 1

Before you even start looking at the problem you have found, you have a much more important problem to sort out. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Then when your app is fixed throughout, start thinking about the code you have there, and why your dates are stored as strings at all ...

Posted 10-Mar-20 7:28am

OriginalGriff

Solution 2

SQL

"AND (Date_prise_compte_cmds LIKE '% + Txt_P4_recherche_Date.Text + "%' OR Date_prise_compte_cmds IS NULL)" +

You should change the order of this command so that it first checks Date_prise_compte_cmds IS NULL. Otherwise the first part of the expression could cause an exception if that field is null.

Posted 10-Mar-20 7:31am

Richard MacCutchan

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2020-03-10T08:45:00

Ok, SQLite. You should have tagged it so, since it does not have a true datetime type.
SQLite Date & Time - How To Handle Date and Time in SQLite[^]

See solution 1 and use parameterized queries; never build SQL scripts by concatenating strings, OriginalGriff has explained why perfectly.

Using your example, this would give

C#

using (SQLiteCommand command = m_conn.CreateCommand())
{
   command.CommandType = CommandType.Text;
   command.CommandText =
      "SELECT * FROM Tble_Commande INNER JOIN Tble_Demande ON" + 
      " (Tble_Demande.dmd_ID = Tble_Commande.cmd_ID)" +
      " WHERE Tble_Commande.Annee = ?" +  
      " AND (Reference IS NULL OR Reference LIKE ?)" +
      " AND (Designations IS NULL OR Designations LIKE ?)" +
      " AND (Date_prise_compte_cmds IS NULL OR date(Date_prise_compte_cmds) = ?)" +
      " AND (Lieu_livraison IS NULL OR Lieu_livraison LIKE ?)" +
      " AND (Imputation IS NULL OR Imputation LIKE ?)" +
      " AND (Type_cmds IS NULL OR Type_cmds LIKE ?)";
   SQLiteParameter param;

   param = new SQLiteParameter();
   param.Value = Annee_en_Cour;
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = $"%{Txt_P4_recherche_Ref.Text}%";
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = $"%{Txt_P4_recherche_Article.Text}%";
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = Txt_P4_recherche_Date.Text;
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = $"%{Cbo_P4_recherche_lieu.Text}%";
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = $"%{Cbo_P4_recherche_Imput.Text}%";
   command.Parameters.Add(param);

   param = new SQLiteParameter();
   param.Value = $"%{Cbo_P4_recherche_TypeCmds.Text}%";
   command.Parameters.Add(param);

   // ...
}

Note the usage of the date()[^] function to only select the date part of stored value. You have to make sure that the date in the textbox has yyyy-MM-dd format; it may be suitable to use a DateTimePicker instead of a TextBox, and format its value properly when assigning the parameter.

C # SQL condition WHERE with a date

3 solutions

Solution 1

Solution 4

Solution 2

Add your solution here

Preview 0

C # SQL condition WHERE with a date

3 solutions

Solution 1

Solution 4

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us