将数据类型varchar转换为数字时出错。插入声明[重复]

This question already has an answer here:

这个问题在这里已有答案:

What are good ways to prevent SQL injection? [duplicate] 5 answers

什么是防止SQL注入的好方法? [重复] 5个答案

I am getting this error

我收到了这个错误

Error converting data type varchar to numeric

将数据类型varchar转换为数字时出错

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

我认为问题在于下拉列表,因为例如当用户选择名称时正在保存id。这是我的代码,我也附上了截图

Screenshot after I run the code in Visual Studio

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

2 个解决方案

#1

This answer will address 2 issues

这个答案将解决2个问题

Protecting from SQL injection using Parameterized Queries

使用参数化查询保护SQL注入

Converting to numeric values when required

需要时转换为数值

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

1 + 2。 (请注意,为方便起见,我没有为您的所有参数编码)

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)

....对所有参数执行此操作(根据需要转换为Int32)

#2

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

除了@DaniDev答案之外,如果您不确定内容是否为有效的int,那么更安全的选项是

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.

这将为您提供一些可以使用的默认值。 Int32.TryParse还返回一个布尔值,指示它是否能够解析,因此您甚至可以将其用作if语句的条件。

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

有关更多详细信息,请参见Int32.TryParse方法(String,Int32)。

#1