LSB71 Ответов: 1

Невозможно отобразить команду с параметрами в datagridview


Hello,
following my discussions with phil.o, I cannot finalize my code.
Here is this example:

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.


Что я уже пробовал:

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

1 Ответов

Рейтинг:
1

phil.o

Вы должен создайте новый объект параметра для каждого параметра. Вы просто предположили, что можете избавиться от лишнего param = new SQLiteParameter(); линии, но вы ошиблись.


phil.o

Извините, комментарии не предназначены для хранения огромных блоков кода. Лучше улучшите свой вопрос и обновите соответствующий код.