当插入SQL C#时，插入SQL datetimepickervalue转换失败 [英] Insert into SQL datetimepickervalue conversion failed when insert into SQL C#

问题描述

Datetimepicker value
 
Conversion failed when converting date and/or time from character string while inserting datetime

我尝试过的事情:

What I have tried:

string saveStaff = "INSERT into dbo.table (name, datum) VALUES (''" + nameTextBox.Text + "'', ''" + datumDateTimePicker.Value.Date + "'')";

推荐答案

从不尝试通过将控件中的值直接连接到SQL语句来保存数据.这将使您对SQL注入敞开大门，并引入转换问题等.

而是使用 SqlParameter类(System.Data.SqlClient) [ ^ ]

对于某些示例，请查看正确执行数据库操作 [

Never try to save the data by concatenating the value from the control directly to the SQL statement. This will leave you wide open to SQL injections and introduce conversion problems etc.

Instead use SqlParameter Class (System.Data.SqlClient)[^]

For some examples, have a look at Properly executing database operations[^]

您可能会遇到本地化问题. Date.ToString()的SQL格式不正确.如果只需要日期，则可以使用

You are probably experiencing problems with Localization. The Date.ToString() is not in an expected format for SQL. If you just want the Date, you can use

string saveStaff = "INSERT into dbo.table (name, datum) VALUES ('" + nameTextBox.Text + "', '" + datumDateTimePicker.Value.Date.ToString("yyyyMMdd") + "')";

但要当心:Wendelius制造他的回答非常重要.切勿在构建SQL语句时使用字符串连接.使用参数！

But beware: Wendelius made a very good point in his answer. Never use string concatenation in building an SQL statement. Use parameters!

报价:

插入时从字符串转换日期和/或时间时转换失败日期时间

Conversion failed when converting date and/or time from character string while inserting datetime

问题是datumDateTimePicker.Value.Date不是字符串，或者不能转换为字符串，或者不是SQL所期望的.
调试器可以帮助您了解什么是什么.

The problem is that datumDateTimePicker.Value.Date is not a string or can not be converted to string, or is not what SQL expected.
Debugger can help you to see what is what.

string saveStaff = "INSERT into dbo.table (name, datum) VALUES ('" + nameTextBox.Text + "', '" + datumDateTimePicker.Value.Date + "')";

不是您的问题的解决方案，而是您遇到的另一个问题.
切勿通过串联字符串来构建SQL查询.迟早，您将使用用户输入来执行此操作，这将打开一个名为"SQL注入"的漏洞的大门，这对您的数据库很危险，并且容易出错.
名称中的单引号会导致程序崩溃.如果用户输入的名称如"Brian O" Conner"可能会使您的应用程序崩溃，则这是一个SQL注入漏洞，而崩溃是最少的问题，这是恶意的用户输入，并且使用所有凭据将其提升为SQL命令.
SQL注入-Wikipedia [ ^ ]
SQL注入 [ ^ ]
SQL注入攻击示例 [ PHP:SQL注入-手册 [ SQL注入预防作弊表-OWASP [

Not 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[^]

这篇关于当插入SQL C#时，插入SQL datetimepickervalue转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！