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
– Steve, Commented Jan 20, 2018 at 23:44
Please take a look at Why do we always prefer using parameters in SQL statements? — maccettura
– maccettura, Commented Jan 20, 2018 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
– Sartorialist, Commented Jan 21, 2018 at 0:08

CodingYoshi · Accepted Answer · 2018-01-21 00:18:16Z

2

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.

edited Jan 21, 2018 at 0:18

answered Jan 21, 2018 at 0:12

CodingYoshi

27.3k7 gold badges68 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related