Click here to Skip to main content
14,770,588 members
Please Sign up or sign in to vote.
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:

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:

"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.)

"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
Updated 10-Mar-20 9:45am
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.

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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
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 ...
   
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
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.
   
v2
Comments
LSB71 10-Mar-20 15:48pm
   
Thank you so much,
I will work on the subject and improve my code ... thank you all.
phil.o 10-Mar-20 15:54pm
   
You're welcome.
Maciej Los 11-Mar-20 16:30pm
   
5ed!
"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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900