Error converting data type nvarchar to decimal.

Question

2.00/5 (1 vote)

See more:

i have kept rate_km,ac_rate_km as decimal(6,2) and allowed null but when i submit the textbox as null it shows error of

" Error converting data type nvarchar to decimal. "

my code is as above:

C#

cmd.CommandText = "usp_vehicleinsert";
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@vehicle_name",txtvname.Text);
          cmd.Parameters.AddWithValue("@vehicle_type",ddlvtype.SelectedValue);
          cmd.Parameters.AddWithValue("@rate_km",txtrate_km.Text);
          cmd.Parameters.AddWithValue("@ac_rate_km", txtacrate_km.Text);
          cmd.Parameters.AddWithValue("@rate_day", txtrate_day.Text);
          cmd.Parameters.AddWithValue("@ac_rate_day",txtacrate_day.Text);
          cmd.Parameters.AddWithValue("@quantity",txtquantity.Text);
          int check = 0;
          cmd.Connection = con;
          con.Open();
          check = cmd.ExecuteNonQuery();
          con.Close();
          if (check == 1)
          {
              lblmsg.Visible = true;
              lblmsg.Text = "Vehicle registered";
          }
          else
          {
              lblmsg.Visible = true;
              lblmsg.Text = "error";
          }

Posted 12-Mar-13 4:40am

Member 9671810

Updated 12-Mar-13 5:29am

Sandeep Mewara

v2

Add a Solution

2 solutions

Solution 1

I think you should pass DBNull.Value instead of null.

Posted 12-Mar-13 4:43am

kisc85

Comments

Member 9671810 12-Mar-13 10:51am

i have not passed null i have checked allow null box in sql

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pheonyx · Accepted Answer · 2013-03-12T04:44:00

Solution 2

You need to wrap it up in a parser.

If you change to code to be

C#

Decimal.Parse(txtrate_km.Text)

Then you should achieve what you are after.
The problem is caused because the Parameter object does not know how to convert from one type to another.

In order to handle the null value you could put something like this in place:

C#

txtrate_km.Text.IsNullOrEmpty() ? DBnull.value : Decimal.Parse(txtrate_km.Text)

What this will do is check if the text box is empty/null and send the correct dbnull value, else it will try and parse the entered text and send that decimal value.

I would also add validation code to ensure only numbers have been entered into the text box.

Posted 12-Mar-13 4:44am

Pheonyx

Updated 12-Mar-13 4:47am

v2

Comments

Member 9671810 12-Mar-13 10:50am

if i do the above

cmd.Parameters.AddWithValue("@rate_km",Decimal.Parse(txtrate_km.Text));
cmd.Parameters.AddWithValue("@ac_rate_km", Decimal.Parse(txtacrate_km.Text));
cmd.Parameters.AddWithValue("@rate_day", Decimal.Parse(txtrate_day.Text));
cmd.Parameters.AddWithValue("@ac_rate_day", Decimal.Parse(txtacrate_day.Text));

i get error as

Input string was not in a correct format.

Pheonyx 12-Mar-13 10:53am

Try adding in the test for the "null" as the Decimal.Parse might be erroring when it receives a null value.

Sharp Robin 12-Mar-13 11:49am

You should try using Decimal.TryParse instead and assign value according to success of your parsing.

Happy coding.

Member 9671810 12-Mar-13 11:13am

i didn't get you.

i am a stater so.... :P

Pheonyx 12-Mar-13 11:19am

Try putting

cmd.Parameters.AddWithValue("@rate_km",
String.IsNullOrEmpty(txtrate_km.Text) ? DBNull.Value : decimal.Parse(txtrate_km.Text));

Member 9671810 12-Mar-13 11:42am

hey der is red underline under....

DBNull.Value : decimal.Parse(txtrate_km.Text));

Pheonyx 12-Mar-13 11:44am

When you hover over the line do you get any errors?
If you try and compile do you get any errors?

Member 9671810 12-Mar-13 11:48am

compilation error sir..

Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'decimal'

Pheonyx 12-Mar-13 11:56am

Could you paste in one of the complete "cmd.Parameters....." lines that you have in your code please.

Member 9671810 12-Mar-13 11:58am

sure..

cmd.Parameters.AddWithValue("@ac_rate_km", String.IsNullOrEmpty(txtacrate_km.Text) ? DBNull.Value : decimal.Parse(txtrate_km.Text));

Pheonyx 12-Mar-13 12:17pm

Put (object) just before the decimal.Parse so it looks like this:

(object)decimal.Parse(txtrate_km.Text)

That appears to resolve the issue for me.

However, it might not work when the stored procedure runs. so you will need to test that part.

Pheonyx 12-Mar-13 12:25pm

Also,

If that fails try this:

if (string.IsNullOrEmpty(textBox1.Text))
{
cmd.Parameters.AddWithValue(" @vehicle_name", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(" @vehicle_name", Decimal.Parse(textBox1.Text));
}

Member 9671810 12-Mar-13 12:27pm

its done...
thanx a lot sir for spending ur precious time......

"CODE PROJECT ROCKS"

Pheonyx 12-Mar-13 12:30pm

no problem, pleased it now works for you.