ASP.net MVC: Input String Was Not In Correct Format

Question

I'm trying to write to a database and am getting the "Input String Was Not In Correct Format" error. I'm assuming it's the data types on the last two columns but I'm not sure how to change. In SQL Server, they are both of the money datatype. Code below:

string query = null;
                    for (int i = 0; i < result.Tables[0].Rows.Count; i++)
                    {
                        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
                        query = "INSERT INTO Upload(Email, TimeStamp, EmployeeId, Name, Title, Department, Race, Gender, AnnualizedBase, AnnualizedTCC) VALUES ('" 
                            + System.Web.HttpContext.Current.User.Identity.GetUserId() + "', "
                            + " '" + DateTime.Now + "', "
                            + " '" + result.Tables[0].Rows[i][0].ToString() + "', "
                            + " '" + result.Tables[0].Rows[i][1].ToString() + "', "
                            + " '" + result.Tables[0].Rows[i][2].ToString() + "', "
                            + " '" + result.Tables[0].Rows[i][3].ToString() + "', "
                            + " '" + result.Tables[0].Rows[i][4].ToString() + "', "
                            + " '" + result.Tables[0].Rows[i][5].ToString() + "', "
                            + Convert.ToInt32(result.Tables[0].Rows[i][6]) + ", "
                            + Convert.ToInt32(result.Tables[0].Rows[i][7])
                            + ")";
                        con.Open();
                       SqlCommand cmd = new SqlCommand(query, con);
                       cmd.ExecuteNonQuery();
                        con.Close();
                    }

Please look ASAP how to write parameterized queries. You will save yourself from these errors, from this mess of code and from Sql Injection — Steve, Jan 20 '18 at 23:44
Please take a look at [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — maccettura, Jan 20 '18 at 23:57
Thanks for the comments. If I'm understanding correctly, my insert query will look something like INSERT INTO TABLE VALUES (@email, @field2, @field3), and each field will be defined like this: var emailparam = new SqlParameter("Email", SqlDbType.Text); emailparam.Value = System.Web.HttpContext.Current.User.Identity.GetUserId(); -- Is that correct? — Sartorialist, Jan 21 '18 at 00:08

CodingYoshi · Accepted Answer · 2018-01-21T00:18:16.580

The error Input string was not in correct format is most likely caused by one of the values in your column not being convertible to an int. If the datatype in SQL is money then you should try and convert to a decimal and not an int. Try this for each row:

decimal num;
if (decimal.TryParse(result.Tables[0].Rows[i][6], out num))
{
    // use num because it is indeed a decimal (money in SQL)
}
else
{
    // What do you want to do? Log it and continue to next row?
}

Also please read Bobby Tales and example of paratmetrized query.

ASP.net MVC: Input String Was Not In Correct Format

1 Answers1