Error converting data type varchar to numeric. Insert statement

Question

I am getting this error

Error converting data type varchar to numeric

and I think the problem is with the dropdown lists because for example when the user select the name is saving the id. That is my code and I am attaching a screenshot as well

Screenshot after I run the code in Visual Studio

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName.Equals("AddNew"))
    {
        TextBox txtActivity = (TextBox)GridView1.FooterRow.FindControl("ftxtActivity");
        TextBox ftxtDate = (TextBox)GridView1.FooterRow.FindControl("ftxtDate");
        TextBox ftxtQno = (TextBox)GridView1.FooterRow.FindControl("ftxtQno");
        DropDownList fddlCName = GridView1.FooterRow.FindControl("fddlCName") as DropDownList;
        DropDownList fddlMmodel = GridView1.FooterRow.FindControl("fddlMmodel") as DropDownList;
        TextBox ftxtQuantity = (TextBox)GridView1.FooterRow.FindControl("ftxtQuantity");
        TextBox ftxtvalueGBR = (TextBox)GridView1.FooterRow.FindControl("ftxtvalueGBR");
        TextBox ftxtvalueEUR = (TextBox)GridView1.FooterRow.FindControl("ftxtvalueEUR");
        TextBox ftxtRate = (TextBox)GridView1.FooterRow.FindControl("ftxtRate");
        TextBox ftxtweightedValue = (TextBox)GridView1.FooterRow.FindControl("ftxtweightedValue");
        DropDownList fddlStatus = GridView1.FooterRow.FindControl("fddlStatus") as DropDownList;
        TextBox ftxtestDecisionDate = (TextBox)GridView1.FooterRow.FindControl("ftxtestDecisionDate");
        TextBox ftxtPromisedDeliveryDate = (TextBox)GridView1.FooterRow.FindControl("ftxtPromisedDeliveryDate");

        con.Open();          
        SqlCommand cmd = new SqlCommand("INSERT INTO SalesActivity(Activity_ID, Date, Quatation_Number, Customer_ID, Product_ID, Quantity, valueGBR, valueEUR, Rate, weightedValue, Status_ID, estDecisionDate, PromisedDeliveryDate) values('" + txtActivity.Text + "','" + ftxtDate.Text + "','" + ftxtQno.Text + "','" + fddlCName.SelectedItem.Value + "','" + fddlMmodel.SelectedItem.Value + "','" + ftxtQuantity.Text + "','" + ftxtvalueGBR.Text + "','" + ftxtvalueEUR.Text + "','" + ftxtweightedValue.Text + "','" + ftxtRate.Text + "','" + fddlStatus.SelectedItem.Value +  "','" + ftxtestDecisionDate.Text + "','" + ftxtPromisedDeliveryDate.Text + "')", con);

        int result = cmd.ExecuteNonQuery();

        con.Close();

        if (result == 1)
        {
            userSales();
            Response.Write("<script language=javascript>alert('" + txtActivity.Text + "'+'Sale Details inserted successfully');</script>");
        }
        else
        {
            Response.Write("<script language=javascript>alert('" + txtActivity.Text + "'+' Sale Details not inserted');</script>");
        }
    }
}

After you've dynamically created the SQL statement, capture it in your debugger. Try executing the captured query directly. I'm sure you'll see that you've misaligned the values you're attempting to insert, or perhaps have quotes around something that doesn't need it. — STLDev, Nov 28 '17 at 00:40
use [SqlCommandParameters](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx). — jegtugado, Nov 28 '17 at 00:40
if the database value is numeric (int, etc.) Getting the value of the dropdown is NOT Enough. You have to convert explicitly using C# convert function e.g.: "Conver.ToInt32(...value) — DaniDev, Nov 28 '17 at 00:50
@DaniDev where i need to use that in the insert statemnt ? my id from the dropdown list is varchar — jorenceto, Nov 28 '17 at 00:53
@JohnEphraimTugado hi i tried with sqlcommand parameters but is not working again — jorenceto, Nov 28 '17 at 00:54
I provided a answer which will address both the issues (if implemented correctly). You will need to follow the example for each of your parameters and convert to Int 32 as necessary — DaniDev, Nov 28 '17 at 01:22
@jorenceto then it means you are either wrong in setting the parameter name or you are not setting the correct data type in your sqlcommand parameters. — jegtugado, Nov 28 '17 at 01:35
Please review [MCVE] guidance to make sure your other questions are easier to read and have chance to be useful for future visitors. — Alexei Levenkov, Nov 28 '17 at 07:04

score 5 · Answer 1 · edited Nov 28 '17 at 05:34

This answer will address 2 issues

Protecting from SQL injection using Parameterized Queries
Converting to numeric values when required

1+2. (Please note that for expedience I did not code for all your parameters )

In your Code Behind:

using (SqlConnection conn = new SqlConnection(connStr))
{
   SqlCommand cmd = new SqlCommand();
   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text;
   cmd.CommandText =  "INSERT INTO SalesActivity(Activity_ID, Date, Quatation_Number, Customer_ID, Product_ID, Quantity, valueGBR, valueEUR, Rate, weightedValue, Status_ID, estDecisionDate, PromisedDeliveryDate) values(@Activity,@Date, @param3 ,@param4,@param5,@param6,@param7,@param8,etc................... )";                }
   cmd.Parameters.AddWithValue("@Activity", Convert.ToInt32(txtActivity.Text));
}

....Do this for all your parameters (convert to Int32 as required)

I would recommend along with this answer that that OP learn how to stored procedures to eliminate any potential SQL Injection and cleaner code / readability — MethodMan, Nov 28 '17 at 05:35

score 0 · Answer 2 · answered Nov 28 '17 at 06:23

In addition to @DaniDev answers, if you are not sure content is a valid int then safer option is

int val = 0;
Int32.TryParse( TextBox1.Text, out val );

This will provide you with some default value you can use. Int32.TryParse also returns a boolean value indicating whether it was able to parse or not, so you can even use it as the condition of an if statement.

See Int32.TryParse Method (String, Int32) for more detailed information.

Error converting data type varchar to numeric. Insert statement

2 Answers2