C# 错误：输入字符串的格式不正确

C# error : Input string was not in a correct format

提问by user2874217

I was getting this error: "Input string was not in a correct format."

我收到此错误：“输入字符串的格式不正确。”

Here is my Code:

这是我的代码：

    private void UpdatePOdetailBalance(int Qty)
    {
        int newbal;

        SqlCommand com = new SqlCommand();

        com.Connection = cn;

        newbal = Convert.ToInt16(txtQtyOrdered.Text) - Qty;
        com.CommandText =
            "UPDATE PODetail SET BalanceQty="+ newbal +" WHERE OrderID=" +
             Convert.ToInt16(txtPONumber.Text) + "AND ItemID=" +
             Convert.ToInt16(txtItemNo.Text);


        com.ExecuteNonQuery();

    }

    private void btnOK_Click(object sender, EventArgs e)
    {

            UpdatePOdetailBalance(Convert.ToInt16(txtQuantity.Text));

    }

I want to compute the newbal which is equal to txtQtyOrdered minus Qty but i'm getting this error please help me with this. Thanks.

我想计算等于 txtQtyOrdered 减去 Qty 的 newbal，但我收到此错误，请帮我解决这个问题。谢谢。

采纳答案by Steve

The problem stated by your error message is probably on one of the lines that try to convert the value in the textboxes to a short integer. Without any check, the value typed by your user could be anything but a number and you get this error message (for example, if you user leaves the textboxes empty).

您的错误消息所述的问题可能出在尝试将文本框中的值转换为短整数的行之一上。如果没有任何检查，您的用户键入的值可能不是数字，并且您会收到此错误消息（例如，如果您的用户将文本框留空）。

You should try to check if the textboxes content could be converted to a valid short integer using TryParsebefore attempting to execute the query

在尝试执行查询之前，您应该尝试检查文本框内容是否可以使用TryParse转换为有效的短整数

int ordered;
if(!int16.TryParse(txtQtyOrdered.Text, out ordered))
{
    MessageBox.Show("Invalid number for Ordered quantity");
    return;
}
int orderID;
if(!int16.TryParse(txtPONumber.Text, out orderID))
{
    MessageBox.Show("Invalid number for OrderId");
    return;
}
int itemID;
if(!int16.TryParse(txtItemNo.Text, out itemID))
{
    MessageBox.Show("Invalid number for ItemID");
    return;
}

At this point you could execute your calculation using the converted short integers and then write your query in this way (adding a space before the AND)

此时，您可以使用转换后的短整数执行计算，然后以这种方式编写查询（在 AND 之前添加一个空格）

  com.CommandText =
        "UPDATE PODetail SET BalanceQty="+ newbal.ToString() +
        " WHERE OrderID=" + orderID.ToString() + 
        " AND ItemID=" + itemID.ToString();

But the string concatenation of query text and user input is never advised as a good practice (in your case is harmless because if the conversion is successful you don't have to worry about Sql Injection, but don't take the habit to do it).
So the perfect way to write this query is through the use of a parametrized query

但是从不建议将查询文本和用户输入的字符串连接作为一种好的做法（在您的情况下是无害的，因为如果转换成功您不必担心 Sql Injection，但不要习惯于这样做）。
因此，编写此查询的完美方法是使用参数化查询

  com.CommandText =
        "UPDATE PODetail SET BalanceQty=@newbal " +
        " WHERE OrderID=@orderID " + 
        " AND ItemID= @itemID"

  com.Parameters.AddWithValue("@newbal", newBal);
  com.Parameters.AddWithValue("@orderID", orderID);
  com.Parameters.AddWithValue("@itemID", itemID);
  com.ExecuteNonQuery();

As a good article on Parameterized query and why to use them, I suggest to read these old wordsfrom Jeff Atwood

作为关于参数化查询以及为什么使用它们的好文章，我建议阅读Jeff Atwood 的这些旧词

回答by David Yancey

You need to put a space before your "AND" and that you are trying to convert a string to an integer that isn't an integer.

您需要在“AND”之前放置一个空格，并且您正在尝试将字符串转换为不是整数的整数。

回答by gregjer

That error means that the string you're trying to convert is not an integer. Try to use int.TryParse

该错误意味着您尝试转换的字符串不是整数。尝试使用 int.TryParse

int newbal;

if(int.TryParse(txtQtyOrdered.Text, out newbal))
    newbal = newbal - Qty;

the same with other texts you are trying to convert

与您尝试转换的其他文本相同

... and add space before " AND which will generate next error

...并在“ AND 之前添加空格，这将产生下一个错误

回答by Yosi Dahari

First - You are missing a space before "AND"

首先 -您在“AND”之前缺少一个空格

1. You should tryto parse the values before the update statement.
2. You should decide what you want to do in case the input from the textbox wasn't in the correct format rather then just get an exception when you try to update.
3. This isn't the right way to format strings, You should use string.Format

1. 您应该尝试在更新语句之前解析值。
2. 如果文本框的输入格式不正确，您应该决定要做什么，而不是在尝试更新时出现异常。
3. 这不是格式化字符串的正确方法，您应该使用 string.Format

回答by taha ahmed

I think you need to debug your code. During debugging copy your query from "com.CommandText" and paste in SQL Server you find the error

我认为你需要调试你的代码。在调试期间从“com.CommandText”复制您的查询并粘贴到 SQL Server 中，您会发现错误

There is only a query error nothing else... May be txtQtyOrdered value is not integer, there is also need blank space "AND ItemID=" to " AND ItemID="

只有一个查询错误没有别的......可能是txtQtyOrdered值不是整数，也需要空格“AND ItemID =”到“ AND ItemID =”

Thanks,

谢谢，

Taha

塔哈

回答by paegun

I'd recommend making changes according to the following code review suggestions based on the code (listed in order of value (cost/benefit of "fixing")):

我建议根据以下基于代码的代码建议进行更改（按价值顺序列出（“修复”的成本/收益））：

1. This method, which is accessing a database should not be reading controls to get its values. Instead there should be an event handler, such as a button click, that parses the values of other controls, using TryParse, as gregjer answered. By segregating the UI and Data code, the data access layer is easier to test and by parsing at the surface (the UI layer) exceptions dealing with bad user input will be caught as soon as possible.
2. Dynamic SQL via strings in the database or in the data access layer w/i .NET is open to SQL injection. You are resolving that issue by parsing the text, so awesome job by you. BUT, this was already handled by the .NET team by providing parameterized commands. Refer to the MSDN SqlCommand.Parameters or see here for a brief, including how a consuming developer groks this topic: When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?
3. Variable naming. Instead of Qty, standard .NET naming conventions would call for quantity, camelCased since it is a parameter and the full human language name, not a shorthand or abbreviation, especially for publicly visible bits. IntelliSense makes long variable names not a problem. Since .NET is unwieldy using just Notepad, it should be assumed that other developers are using an IDE such as VisualStudio or SharpDevelop, so use meaningful names.
4. Stored procedures should be used. Every time this SQL is executed, SQL Server needs to check its command cache minimally, but if the command has been flushed from cache, the SQL command needs to be interpreted and encached (put into cache). This as well as the fact that using a stored procedure requires "shipping" less bytes on every call to the database.

1. 这种访问数据库的方法不应该通过读取控件来获取其值。相反，应该有一个事件处理程序，例如按钮单击，它使用 TryParse 解析其他控件的值，正如 gregjer 所回答的那样。通过分离 UI 和数据代码，数据访问层更容易测试，并且通过在表面（UI 层）解析处理不良用户输入的异常将尽快被捕获。
2. 通过数据库中的字符串或在数据访问层 w/i .NET 中的动态 SQL 对 SQL 注入是开放的。您正在通过解析文本来解决该问题，您的工作真棒。但是，这已经由 .NET 团队通过提供参数化命令来处理。请参阅 MSDN SqlCommand.Parameters 或参见此处的简要说明，包括消费开发人员如何理解此主题：添加 SqlCommand 参数时何时应使用“SqlDbType”和“大小”？
3. 变量命名。标准 .NET 命名约定将要求数量而不是数量，camelCased 因为它是一个参数和完整的人类语言名称，而不是速记或缩写，尤其是对于公开​​可见的位。IntelliSense 使长变量名不成问题。由于 .NET 仅使用记事本很笨拙，因此应该假设其他开发人员正在使用 VisualStudio 或 SharpDevelop 等 IDE，因此请使用有意义的名称。
4. 应该使用存储过程。每次执行此 SQL 时，SQL Server 都需要最低限度地检查其命令缓存，但如果该命令已从缓存中刷新，则该 SQL 命令需要被解释和缓存（放入缓存中）。这以及使用存储过程需要在每次调用数据库时“传送”较少字节的事实。

回答by Eric Schneider

you can sometimes run into this problem when you have multiple parameters and are using Oracleor DB2databases. They dont's support named parameters or it's not turned on.

当您有多个参数并使用Oracle或DB2数据库时，有时会遇到此问题。他们不支持命名参数或者它没有打开。

Oracle:

甲骨文：

    Dim cmd As OracleCommand = DirectCast(connection.CreateCommand, OracleCommand)
    cmd.BindByName = True

Make sure you parameters are added to the command object in the same order as the sql statement

确保您的参数以与 sql 语句相同的顺序添加到命令对象中