Throws exception when using parameterized query in c#

Question

I used the Parametarized Query to save the data to sql server. When executing it throws below error.

The parameterized query '(@CustomerID int,@SerialNo nvarchar(2),@ModelNo nvarchar(2),@Sal' expects the parameter '@CreatedBy', which was not supplied.

here is my code.

   public static bool SaveSales(int custid, string model, string serial, DateTime salesdate, decimal price, string mainservice, string comments, DateTime createddate, string createdby, DateTime modifieddate, string modifiedby)
    {
        bool saved = false;

        try
        {
            using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "INSERT INTO tbl_Sales VALUES(@CustomerID, @SerialNo, @ModelNo, @SalesDate, @Price, @MaintanenceService, @Comments, @CreatedDate, @CreatedBy, @ModifiedDate, @ModifiedBy)";
                    command.CommandType = CommandType.Text;

                    command.Parameters.AddWithValue("@CustomerID", custid);
                    command.Parameters.AddWithValue("@SerialNo", serial);
                    command.Parameters.AddWithValue("@ModelNo", model);
                    command.Parameters.AddWithValue("@SalesDate", salesdate);
                    command.Parameters.AddWithValue("@Price", price);
                    command.Parameters.AddWithValue("@MaintanenceService", mainservice);
                    command.Parameters.AddWithValue("@Comments", comments);
                    command.Parameters.AddWithValue("@CreatedDate", createddate);
                    command.Parameters.AddWithValue("@CreatedBy", createdby);
                    command.Parameters.AddWithValue("@ModifiedDate", modifieddate);
                    command.Parameters.AddWithValue("@ModifiedBy", modifiedby);

                    command.ExecuteNonQuery();
                }
                conn.Close();
            }

            saved = true;
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            return saved;
        }

        return saved;
    }

What is the wrong with my query ? I used paramerized query is to save the salesdate correctly. Any help would be much appreciated.

Sounds like you're not mentioning column names in your INSERT query (e.g. `INSERT INTO tbl_Sales (Column1, Column2, ...) VALUES (@Param1, @Param2, ...)`). — Tetsuya Yamamoto, Feb 13 '19 at 07:55
Please make sure createdBy is not null. change your code as follows createdby == null ? "" : createdBy — Taimur Khan, Feb 13 '19 at 07:55
Can you check if this solves your problem? https://stackoverflow.com/questions/3865982/the-parameterized-query-expects-the-parameter-which-was-not-supplied — Lasse V. Karlsen, Feb 13 '19 at 07:56
@TetsuyaYamamoto That query is ok, if you are inserting for all columns, you do not need to specify column names, however OP should make sure, that the order of the values is in the same order as the columns in the table. — SᴇM, Feb 13 '19 at 07:59
*Don't* use `AddWithValue`. That method has to *guess* what the actual type and size is from the value you pass. If the value is null, it's impossible to guess what the correct type is. — Panagiotis Kanavos, Feb 13 '19 at 08:14
As a side note: it is generally a bad idea to use `INSERT` without explicitly citing the insert column order; it is surprisingly easy to end up with columns in different orders on different installations / platforms, especially when one is green-field and one is brown-field — Marc Gravell, Feb 13 '19 at 08:17

Marc Gravell · Accepted Answer · 2019-02-13T08:15:42.557

I suspect that createdby is null, which means it won't get sent - it needs to be DBNull instead.

Try:

command.Parameters.AddWithValue("@CreatedBy", createdby ?? (object)DBNull.Value);

Or: try using a tool like Dapper that will a: make it easier in general, and b: get it right; for example:

using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
{
    conn.Execute("INSERT INTO tbl_Sales VALUES(@CustomerID, @SerialNo, @ModelNo, @SalesDate, @Price, @MaintanenceService, @Comments, @CreatedDate, @CreatedBy, @ModifiedDate, @ModifiedBy)",
      new {
        CustomerID = custid, SerialNo = serial,
        ModelNo = model, SalesDate = salesdate,
        Price = price, MaintanenceService = mainservice,
        Comments = comments, CreatedDate = createddate,
        CreatedBy = createdby, ModifiedDate = modifieddate,
        ModifiedBy = modifiedby });
}

or even simpler:

using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
{
    conn.Execute("INSERT INTO tbl_Sales VALUES(@custid, @serial, @model, @salesdate, @price, @mainservice, @comments, @createddate, @createdby, @modifieddate, @modifiedby )",
      new {
        custid, serial, model, salesdate,
        price, mainservice, comments, createddate,
        createdby, modifieddate, modifiedby });
}

Throws exception when using parameterized query in c#

1 Answers1