将数据类型varchar转换为数字时出错。 [英] Error converting data type varchar to numeric.

问题描述

当Reader执行数据到Lable时显示错误，这样，我该怎么办



我给出了错误图像的链接在这里：

图片在这里 [ ^ ]

When the Reader Execute the Data to the Lable is shows the Error like this, What can i do

I given the Link of the Error Image Here :
Image is here[^]

错误消息：

SqlException未处理

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

SqlException was unhandled
Error converting data type varchar to numeric.

private void balance_enery_Load(object sender, EventArgs e)
{
    label3.Visible = false;
    //SqlConnection con = new SqlConnection("data source=CLIENT-07\\SQLEXPRESS;integrated security=true;initial catalog=ATM;");
    con.Open();
    SqlCommand cmd = new SqlCommand("select  * from transactions where account_no='" + label3.Text + "'", con);
    SqlDataReader rdr= cmd.ExecuteReader();
    if (rdr.Read()) // Error is thrown on this line
    {
        label2.Text = rdr["total"].ToString();
    }
    rdr.Close();
    rdr.Dispose();
    con.Close();
}

我的尝试：



我试图在行中加入.ToString（），但它仍显示错误

What I have tried:

I tried to Join .ToString() in the line but still it shows the Error

推荐答案

不要这样做。永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

Don't do it like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？



假设你的账号是数字，你应该使用 int.TryParse [ ^ ]将其转换为数字 - 向用户报告任何问题而不是继续 - 并将转换后的值作为参数传递：

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Assuming your account number is numeric, you should use int.TryParse[^] to convert it to a number - reporting any problems to the user instead of continuing - and pass the converted value as a parameter:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Description FROM myTable WHERE ID = @ID", con))
        {
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            cmd.Parameters.AddWithValue("@ID", accountNo);
            if (reader.Read())
                {
                string desc = (string) reader["description"];
                Console.WriteLine("ID: {0}\n    {1}", accountNo, desc);
                }
            }
        }
    }

OriginalGriff的 SQL注入正确答案;我不打算重复。



由于我的雇主的防火墙，我也无法查看你的图像，所以我不知道你得到的错误是什么。



我要做的是为你提供我将如何编写该方法的基础知识，希望它可以提供帮助，我的实施说明

1.我不知道为什么你的连接被注释掉了，所以我重新启用它。

2. SQL类包含在USING块中以正确处理/释放资源。

3.实现了一个TRY块，这样你就可以得到一些反馈（至少是为了调试）。

4.增加了变量，TryParse成了一个廉价的验证器。 />
5. SQL语句被削减到你要求的唯一项目。

6. ExecuteScalar（）是使用的方法，因为只有一个值。开销减少。

7.如果没有找到记录，则添加反馈

OriginalGriff's answer is right on with SQL Injection; I am not going to rehash.

I also was not able to view your image due to my employer's firewall, so I have no clue what the error you were getting is.

What I am going to do is provide you with the basics of how I would be coding that method in hopes that it can help, with my implementation notes
1. I have no clue why your connection is commented out, so I re-enabled it.
2. SQL classes were wrapped in USING blocks to properly dispose/release resources.
3. A TRY block was implemented so you can get some feedback (at least for debugging).
4. Variables were added and TryParse made a cheap validator.
5. SQL statement was pared down to the one and only item you are asking for.
6. ExecuteScalar() was the method used as there is only one value. Less overhead.
7. Feedback added if no records found

label3.Visible = false;
int AccountNo = -1;
string OutputMessage = string.Empty;

try {
    if (int.TryParse(label3.Text, out AccountNo)) {
        using (SqlConnection con = new SqlConnection("data source=CLIENT-07\\SQLEXPRESS;integrated security=true;initial catalog=ATM;")) {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT total FROM transactions WHERE account_no=@AccountNo", con)) {
                cmd.Parameters.AddWithValue("@AccountNo", AccountNo);
                var SRV = cmd.ExecuteScalar();
                if (!SRV == null) {
                    OutputMessage = SRV.ToString();
                }
                else {
                    Output = "No Results Found";
                }
            }
        }
    }
    else {
        OutputMessage = "Label 3 was not a valid";
    }
    label2.Text = OutputMessage;
}
catch (Exception ex) { label2.Text = ex.Message; }

这篇关于将数据类型varchar转换为数字时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！