Click here to Skip to main content
15,889,992 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,
following my discussions with phil.o, I cannot finalize my code.
Here is this example:

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

   // ...
} 


I block on parameters, here is the error message.
unknown error
Insufficient parameters supplied to the command

Unable to display in my datagridview ... 
thanks in advance for your support.


What I have tried:

C#
using (DataTable dt_P4_Tble_cmd = new DataTable())
{
    SQLiteCommand command = Program.Connex_Bdd.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 = @Annee" +
       " AND (Reference IS NULL OR Reference LIKE @Ref)" +
       " AND (Designations IS NULL OR Designations LIKE @Article)" +
       " AND (Date_prise_compte_cmds IS NULL OR date(Date_prise_compte_cmds) = @DateCmds)" +
       " AND (Lieu_livraison IS NULL OR Lieu_livraison LIKE @Lieu)" +
       " AND (Imputation IS NULL OR Imputation LIKE @Imput)" +
       " AND (Type_cmds IS NULL OR Type_cmds LIKE @TypeCmds)";

    SQLiteParameter param = new SQLiteParameter();

    param.ParameterName = "@Annee";
    param.Value = $"%Cbo_Choix_Annee.Text%";

    param.ParameterName = "@Ref";
    param.Value = $"%Txt_P4_recherche_Ref.Text%";

    param.ParameterName = "@Article";
    param.Value = $"%Txt_P4_recherche_Article.Text%";

    param.ParameterName = "@DateCmds";
    param.Value = $"%Txt_P4_recherche_Date.Text%";

    param.ParameterName = "@Lieu";
    param.Value = $"%Cbo_P4_recherche_lieu.Text%";

    param.ParameterName = "@Imput";
    param.Value = $"%Cbo_P4_recherche_Imput.Text%";

    param.ParameterName = "@TypeCmds";
    param.Value = $"%Cbo_P4_recherche_TypeCmds.Text%";

    command.Parameters.Add(param);

    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command.CommandText, Program.Connex_Bdd))
    {
        adapter.Fill(dt_P4_Tble_cmd);
    }
    Datagridview.DataSource = dt_P4_Tble_cmd;
}
Posted
Updated 12-Mar-20 6:55am

1 solution

You have to create a new parameter object for each parameter. You just supposed you could get rid of redundant param = new SQLiteParameter(); lines, but you were wrong.
 
Share this answer
 
Comments
phil.o 12-Mar-20 12:25pm    
Sorry, comments are not meant to hold huge code blocks. Better improve your question and update the relevant code.

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