Error converting data type varchar to numeric. Ins

问题:

This question already has answers here:

What are good ways to prevent SQL injection? [duplicate] (4 answers)

Closed 2 years ago.

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>");
        }
    }
}

回答1:

This answer will address 2 issues

1. Protecting from SQL injection using Parameterized Queries
2. 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)

回答2:

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.