-1

I am inserting data in C# with a SQL query. At that time it shows the error i.e.

The parameterized query '(@customervechiclerefno nvarchar(1),@customervechicleregdate nva' expects the parameter '@customervechicleno', which was not supplied.

Here is my code:

command.CommandText = " insert into tbl_customervechicle " + System.Environment.NewLine;
command.CommandText += " (customervechiclerefno, customervechicleregdate, customervechiclebillno, customervechiclecustomername, " + System.Environment.NewLine;
command.CommandText += " customervechicleno, customervechicleamt, customervechiclecf, customervechiclecard," + System.Environment.NewLine;
command.CommandText += " customervechiclecerti, customervechicleop, customervechicleagentfee, customervechiclefile," + System.Environment.NewLine;
command.CommandText += " customervechiclebalta, customervechicleexpiry," + System.Environment.NewLine;
command.CommandText += " customervechicleguarantee, customervechiclehp, customervechiclediscount," + System.Environment.NewLine;
command.CommandText += " customervechicletotalpayment, customervechicledp, customervechicleloan, customervechicleper, " + System.Environment.NewLine;
command.CommandText += " customervechicleservicetax, customervechiclemonth, customervechicleinstallmentrs, customervechiclecashpayment, " + System.Environment.NewLine;
command.CommandText += " customervechiclechequepayment, customervechiclechequeno, customervechiclechequedate, customervechiclebankbranch, " + System.Environment.NewLine;
command.CommandText += " customervechicleistransferred, customervechicletransferredname, customervechicledescription, branchid, createduserid, " + System.Environment.NewLine;
command.CommandText += " createddatetime, customervechicleservicetaxrs) " + System.Environment.NewLine;

command.CommandText += " values(@customervechiclerefno, @customervechicleregdate, @customervechiclebillno, @customervechiclecustomername, " + System.Environment.NewLine;
command.CommandText += " @customervechicleno, @customervechicleamt, @customervechiclecf, @customervechiclecard," + System.Environment.NewLine;
command.CommandText += " @customervechiclecerti, @customervechicleop, @customervechicleagentfee, @customervechiclefile," + System.Environment.NewLine;
command.CommandText += " @customervechiclebalta, @customervechicleexpiry," + System.Environment.NewLine;
command.CommandText += " @customervechicleguarantee, @customervechiclehp, @customervechiclediscount," + System.Environment.NewLine;
command.CommandText += " @customervechicletotalpayment, @customervechicledp, @customervechicleloan, @customervechicleper, " + System.Environment.NewLine;
command.CommandText += " @customervechicleservicetax, @customervechiclemonth, @customervechicleinstallmentrs, @customervechiclecashpayment, " + System.Environment.NewLine;
command.CommandText += " @customervechiclechequepayment, @customervechiclechequeno, @customervechiclechequedate, @customervechiclebankbranch, " + System.Environment.NewLine;
command.CommandText += " @customervechicleistransferred, @customervechicletransferredname, @customervechicledescription, @branchid, @createduserid, " + System.Environment.NewLine;
command.CommandText += " @createddatetime, @customervechicleservicetaxrs)"; 

command.Parameters.AddWithValue("@customervechiclerefno", txt_regno.Text);
command.Parameters.AddWithValue("@customervechicleregdate", dtp_regdate.Text);
command.Parameters.AddWithValue("@customervechiclebillno", clsObjDataAccess.IfNullThen(txt_billno.Text, "-"));
command.Parameters.AddWithValue("@customervechiclecustomername", cmb_customername.SelectedValue);
command.Parameters.AddWithValue("@customervechicleno", cmb_searchvechicleno.SelectedValue);
command.Parameters.AddWithValue("@customervechicleamt", clsObjDataAccess.IfNullThen(txt_vechicleamt.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclecf", clsObjDataAccess.IfNullThen(txt_cf.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclecard", clsObjDataAccess.IfNullThen(txt_card.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclecerti", clsObjDataAccess.IfNullThen(txt_certi.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleop", clsObjDataAccess.IfNullThen(txt_op.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleagentfee", clsObjDataAccess.IfNullThen(txt_agentfee.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclefile", clsObjDataAccess.IfNullThen(txt_file.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclebalta", clsObjDataAccess.IfNullThen(txt_balta.Text, 0.0));

command.Parameters.AddWithValue("@customervechicleexpiry", clsObjDataAccess.IfNullThen(txt_expiry.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleguarantee", clsObjDataAccess.IfNullThen(txt_guarantee.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclehp", clsObjDataAccess.IfNullThen(txt_hp.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclediscount", clsObjDataAccess.IfNullThen(txt_discount.Text, 0.0));
command.Parameters.AddWithValue("@customervechicletotalpayment", clsObjDataAccess.IfNullThen(txt_totalpay.Text, 0.0));
command.Parameters.AddWithValue("@customervechicledp", clsObjDataAccess.IfNullThen(txt_dp.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleloan", clsObjDataAccess.IfNullThen(txt_loan.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleper", clsObjDataAccess.IfNullThen(txt_per.Text, 0.0));
command.Parameters.AddWithValue("@customervechicleservicetax", clsObjDataAccess.IfNullThen(txt_servicetax.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclemonth", clsObjDataAccess.IfNullThen(txt_hafta.Text, 1));
command.Parameters.AddWithValue("@customervechicleinstallmentrs", clsObjDataAccess.IfNullThen(txt_int.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclecashpayment", clsObjDataAccess.IfNullThen(txt_cashpayment.Text, 0.0));
command.Parameters.AddWithValue("@customervechiclechequepayment", clsObjDataAccess.IfNullThen(txt_chequepayment.Text, 0.0));

command.Parameters.AddWithValue("@customervechiclechequeno", clsObjDataAccess.IfNullThen(txt_chequeno.Text, "-"));
command.Parameters.AddWithValue("@customervechiclechequedate", dtp_chequedate.Text);
command.Parameters.AddWithValue("@customervechiclebankbranch", clsObjDataAccess.IfNullThen(txt_chequebankbranch.Text, "-"));
command.Parameters.AddWithValue("@customervechicleistransferred", chb_istransferred.Text);
command.Parameters.AddWithValue("@customervechicletransferredname", cmb_customername.SelectedValue);
command.Parameters.AddWithValue("@customervechicledescription", clsObjDataAccess.IfNullThen(txt_description.Text, "-"));
command.Parameters.AddWithValue("@branchid", lbl_branchid.Text);
command.Parameters.AddWithValue("@createduserid", lbl_userid.Text);
command.Parameters.AddWithValue("@createddatetime", Convert.ToDateTime(DateTime.Now.ToString("dd/MM/yyyy")));
command.Parameters.AddWithValue("@customervechicleservicetaxrs", txt_servicetaxrs.Text);

Res = clsObjDataAccess.ExecuteCommand(command);

if (Res > 0)
{
    MessageBox.Show("Successfully Saved...!!!", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
    reset();
}

Now, I am confuse that what is the field is showing in the error message that all field is right only in the query for my thinking but then also it showing the error.

halfer
  • 19,824
  • 17
  • 99
  • 186
s.k.Soni
  • 1,139
  • 1
  • 19
  • 37
  • Is the value you are passing null by any chance? – user6144226 Aug 29 '17 at 07:38
  • 2
    I guess `cmb_searchvechicleno.SelectedValue` is `null`. – MatSnow Aug 29 '17 at 07:38
  • 2
    The message is obvious: `@customervechicleno` is not supplied by value. Possibly `Environment.NewLine` messing up or `cmb_searchvechicleno.SelectedValue` contains null/empty value, try using string literal to build query string & fill the corresponding parameter with proper value. – Tetsuya Yamamoto Aug 29 '17 at 07:39
  • 1
    https://stackoverflow.com/questions/13451085/exception-when-addwithvalue-parameter-is-null – user6144226 Aug 29 '17 at 07:40
  • 1
    BTW, the `NewLine` after each line is not really of any use i think. And do you already know about [verbatim string literal](https://stackoverflow.com/questions/1100260/multiline-string-literal-in-c-sharp)? – MatSnow Aug 29 '17 at 07:42

1 Answers1

2

When passing parameters to DB command you should use DBNull for null values. In your case, you can use:

command.Parameters.AddWithValue("@customervechicleno", cmb_searchvechicleno.SelectedValue ?? DBNull.Value);

Yoy can see a detailed explanation in Exception when AddWithValue parameter is NULL

rualmar
  • 229
  • 2
  • 8