Input string was not in a correct format

Question

User-1499457942 posted

Hi

  In below code i get above error

SqlCommand cmdContribution = new SqlCommand("Select Sum(Amount) from [Data] where Location = @Location", con);
                                                cmdContribution.Parameters.AddWithValue("@Location", (dt.Rows[i]["Location"]).ToString());

                                                SqlDataAdapter adContribution = new SqlDataAdapter(sqlCommand);
                                                DataTable dt = new DataTable();
                                                adC.Fill(dt);
                                                if (dt.Rows.Count > 0)
                                                {
                                                    m_Achieved = Convert.ToDecimal(dt.Rows[0][0].ToString());
                                                }

Thanks

Answer 1

User475983607 posted

Most likely the line of code below because dt.Row[0][0] does not contain a decimal and you have not added any validation checks.  

m_Achieved = Convert.ToDecimal(dt.Rows[0][0].ToString());

There's other code smells too like the following.... Seems to indicate a loop but there is not enough code to guess.

cmdContribution.Parameters.AddWithValue("@Location", (dt.Rows[i]["Location"]).ToString());

Set a breakpoint and step through your code.

Answer 2

User-1499457942 posted

Hi

  I want to get the Sum of Amount. How it can be done in the fastest way

Thanks

Answer 3

User475983607 posted

JagjitSingh

I want to get the Sum of Amount. How it can be done in the fastest way

Hold on... Let me look into my crystal ball... Ah yes... Set a breakpoint and check the value of  dt.Rows[0][0].

Also, it looks like you have a logical bug as dt is defined twice?  It could be the error happens one the second loop.  This would be very obvious if you made even a slight effort to debug.

SqlCommand cmdContribution = new SqlCommand("Select Sum(Amount) from [Data] where Location = @Location", con);
cmdContribution.Parameters.AddWithValue("@Location", (dt.Rows[i]["Location"]).ToString());

SqlDataAdapter adContribution = new SqlDataAdapter(sqlCommand);
DataTable dt = new DataTable();
adC.Fill(dt);
if (dt.Rows.Count > 0)
{
	m_Achieved = Convert.ToDecimal(dt.Rows[0][0].ToString());
}

Lastly, use ExecuteScalar to return a single value.  It's a waste to invoke an SqlDataAdapter to fill a DataTable with one value.  you've been given this advice in the past...

Are you trying to get the Sum of every location?

Answer 4

User753101303 posted

Hi,

You really can get 0 row rather than 1 ? If you expect always one row, one column you could use ExecuteScalar.

SqlCommand cmdContribution = new SqlCommand("Select Sum(Amount) from [Data] where Location = @Location", con);
                                                cmdContribution.Parameters.AddWithValue("@Location", (dt.Rows[i]["Location"]).ToString());
m_Achieved=(decimal)cmd.ExecuteScalar();

I assume m_Achieved is a deicmal. To get back at your source error, when it happens you should start by looking at the string you are trying to convert. Also you convert the column value to a string and then back to its type. Instead you could cast it or use https://docs.microsoft.com/en-us/dotnet/api/system.data.datarowextensions?view=netframework-4.7.2 which allows to read the value directly using :

var value=dt.Rows[i].Field<decimal>("MyColumn");

Answer 5

User-1716253493 posted

Try this

m_Achieved = Convert.ToDecimal(dt.Rows[0][0]);