如何在C＃中将其转换为varchar值？ [英] How can I convert this into varchar value in C#?

问题描述

当我尝试执行此代码并突然填写文本框字段时，当我单击更新按钮时，出现了未处理的SqlException并说转换varchar值时转换失败System.Windows.Forms.TextBox，文本：12345'到数据类型int。并且cmd.ExecuteNonQuery（）;突出显示。我希望有人帮助我解决这个问题。我不知道我将要做什么。



SqlConnection sqlcon = new SqlConnection（Data Source = .\\SQLEXPRESS; AttachDbFilename = C：\\Users\\john \\Documents \\Visual Studio 2010 \\ \\\Projects \\Login \\Login \\dblogin.mdf; Integrated Security = True; Connect Timeout = 30; User Instance = True）;

sqlcon.Open （）;

SqlCommand cmd = new SqlCommand（update tbl_employee set Firstname ='+ textBox2 +'where Employee_ID ='+ textBox1 +'）;

cmd.Connection = sql con;

cmd.ExecuteNonQuery（）;

sqlcon.Close（）;

MessageBox.Show（记录已成功更新） ;



我尝试了什么：



我试图宣布cmd连接等于sqlcon但它还没有工作。它是如何工作的？

While I was trying to execute this code and suddenly fill up the textbox fields then when I clicked the update button, a SqlException was unhandled appeared and say "Conversion failed when converting the varchar value 'System.Windows.Forms.TextBox, Text:12345'to data type int. And cmd.ExecuteNonQuery(); was highlighted. I hope someone helped me solving this problem. I don't know what I am going to do.

SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\john\\Documents\\Visual Studio 2010\\Projects\\Login\\Login\\dblogin.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
sqlcon.Open();
SqlCommand cmd = new SqlCommand("update tbl_employee set Firstname='" + textBox2 + "' where Employee_ID='" + textBox1 + "'");
cmd.Connection = sqlcon;
cmd.ExecuteNonQuery();
sqlcon.Close();
MessageBox.Show("Record has been updated Successfully");

What I have tried:

I tried to declare the cmd connection which was equals to sqlcon but it is not yet working. How it works?

推荐答案

SqlCommand cmd = new SqlCommand("update tbl_employee set Firstname='" + textBox2 + "' where Employee_ID='" + textBox1 + "'");

没有必要解决你的问题，但你有另一个问题。

永远不要构建一个SQL通过连接字符串进行查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

我该怎么办？解释没有技术术语的SQL注入？ - 信息安全堆栈交换 [ ^ ]

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

另一个答案是可以的，但如果你这样做，我可以删除你的数据库。



一般来说，实体框架的生活更漂亮，但如果你必须这样工作，请阅读：



第06课：向命令添加参数 - C＃站 [ ^ ]



此外，您的SQLConnection impliments IDisposable并且应该在使用中声明

The other answer will WORK, but I can erase your database if you do that.

In general, life is prettier with Entity Framework, but if you must work this way, please read this:

Lesson 06: Adding Parameters to Commands - C# Station[^]

Also your SQLConnection impliments IDisposable and should be in a using statement

这是我如何重写它的快速模拟。完成了几项更改。



1.验证。检查以确保您的文本框已填满并包含有效数据

2. 使用块。这可以确保您的SqlConnection正确处理

3.参数化。这是避免SQL注入的最佳方法

4.评估。检查SQL命令实际影响了多少行

This is a quick mock up of how I would rewrite this. There are several changes done.

1. Validate. Check to make sure your text-boxes are filled and contain valid data
2. using block. This makes sure that your SqlConnection is disposed of properly
3. Parameterization. This is the best way to avoid SQL Injection
4. Evaluation. Check how many rows were actually affected by your SQL Command

int RowsAffected = -1;
string MessageboxContent = string.Empty;

int EmployeeID = -1;
string Firstname = textBox2.Trim();

// rudimentary validation to make sure you have valid values
if ((int.TryParse(textBox1, out EmployeeID) && (Firstname.length > 0)) { 

  using (SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\john\\Documents\\Visual Studio 2010\\Projects\\Login\\Login\\dblogin.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")) {
    sqlcon.Open();

    SqlCommand cmd = new SqlCommand("UPDATE tbl_employee SET Firstname= @Firstname WHERE Employee_ID= @EmployeeID", sqlcon);
    cmd.Parameters.AddWithValue("@Firstname", Firstname);
    cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);

    RowsAffected = cmd.ExecuteNonQuery();
    sqlcon.Close();
  }
}

switch (RowsAffected) {
  case -1: MessageboxContent = "Invalid data entereed"; break;
  case 0: MessageboxContent = "Employee not found, record not updated";
  case -1: MessageboxContent = "Record has been updated Successfully";
  default: MessageboxContent = string.Format("Error: {0} records updated", RowAffected); break;
}

MessageBox.Show(MessageboxContent);

这篇关于如何在C＃中将其转换为varchar值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！