错误：将数据类型varchar转换为日期 [英] Error: converting data type varchar to date

问题描述

大家好..

请帮助我完成我的项目。我已经创建了一个程序，我在我的aspx.cs文件中调用它。下面是我的代码的简短摘要。



我尝试了什么：



这是我的存储过程：

Hi Everyone..
please help me in my project. i have create a procedure which i call to it in my aspx.cs file bellow is brief summary of my code.

What I have tried:

Here is my stored procedure:

CREATE PROCEDURE PLACE_DEMAND
(
@ITEM_ID INT,
@EMP_ID INT,
@DEMAND_DATE DATE,
@DEMAND_QUANTITY INT,
@DEMAND_STATUS VARCHAR (50),
@STATUS_DATE DATE
)
AS
BEGIN
INSERT INTO Demands (Item_ID,Emp_ID,Demand_Date,Demand_Quantity,Demand_Status,Status_Date) VALUES (@ITEM_ID,@EMP_ID,@DEMAND_DATE,@DEMAND_QUANTITY,@DEMAND_STATUS,@STATUS_DATE)
END

而bellow是我执行程序的函数：

And bellow is my function which execute my procedure:

public static void exec_insert_proc(string proc, string[] param)
 {
     SqlCommand cmd = new SqlCommand(proc, DBConnection());
     cmd.CommandType = CommandType.StoredProcedure;
     int i;
     for (i = 0; i <= param.Length - 1; i++)
     {
         string[] nme;
         nme = param[i].Split(':');
         cmd.Parameters.Add(nme [0], SqlDbType.VarChar, 50).Value = nme [1];
     }
     cmd.ExecuteNonQuery(); // error occured here which is Error converting data type varchar to date
 }

最后我的功能是我传递的参数

And Finaly this my function to which i am passing parameters

string abc = Convert.ToString(DateTime.Now);
 string[] array = new string[]
 {
"@ITEM_ID:"+DDL_Select_Item.SelectedValue,"@EMP_ID:"+txt_Emp_ID.Text,"@DEMAND_DATE:"+txt_Date.Text,"@DEMAND_QUANTITY:"+txt_Quantity.Text,"@DEMAND_STATUS:"+txt_Status.Text,"@STATUS_DATE:"+abc 
};
 db.exec_insert_proc("dbo.PLACE_DEMAND", array);

推荐答案

简单：将DateTime值作为DateTime从C＃代码传递给SQL，而不是字符串值。

当你通过时SQL的一个字符串，并希望它插入到DATE列中，SQL将使用它的服务器的日期时间设置来转换它，而不是客户端，甚至是你的网络服务器。因此，如果我今天的日期为21/12/16，那对我来说非常有意义：2016年第21天，第12个月。但是，如果SQL服务器设置为默认的Windows语言环境，它预计为21个月，第12天，2016或ISO格式为2021，第12个月，第16天 - 它可能会开始导致转换错误，其中一些会导致您注意到的错误，因为它是无效的日期。此外，如果用户输入一个值，转换也将失败。



尽快将字符串转换为自然数据类型：整数值为 int ，使用 TryParse 与DateTime约会，并将用户文化和报告问题直接发送给他，而不是将数据发送到SQL。然后将这些值作为本机数据类型传递给SQL，因此不需要进一步转换。这样，您可以尽早发现问题，这样就可以解决问题而不是将无效数据插入到数据库中，并在发现不一致时导致大量问题进行排序。

Simple: pass DateTime values as DateTime from your C# code to SQL, not as a string value.
When you pass a string to SQL and expect it to be inserted into a DATE column, SQL will use the datetime settings of it's server to convert it, not those of your client, or even your webserver. So if I enter todays date as "21/12/16" that makes perfect sense to me: "21st Day, 12th Month, 2016". But if the SQL server is set to the default Windows locale it expects that to be "21st Month, 12th day, 2016" or in ISO format as "2021, 12th Month, 16th Day" - and it can start to cause conversion errors, some of which will cause the error you have noticed because it is an invalid date. Additionally, if the user miskeys a value, the conversion will also fail.

Convert your strings to "natural" datatypes as soon as you can: Integer values to int, dates to DateTime using TryParse and the user culture and report problems direct to him instead of sending the data to SQL. Then pass those values as native datatypes to SQL so no further conversion is required. That way, you catch problems early, so they can be solved instead of inserting invalid data to your DB and causing massive problems to sort it out later when you notice the inconsistencies.

你正在做一个大错误 - 两次......

首先你将二进制值作为字符串传递，这总是有问题...

想想有多少种方法可以约会表示为字符串...

第二个错误是将所有内容转换为字符串并将所有内容作为字符串传递。您可能会感到惊讶，但SQL可以处理非字符串值，.NET SQL库中的类也可以处理它们...

所以请自己帮忙并根据类型传递每个值。 ..



您可以创建一个接收对象数组的公共函数，并将其实际类型映射到SQL类型（如果必须...）

You are doing a big mistake - twice...
First of all you are passing binary values as string, which is always problem...
Just think about how many ways date can be represented as string...
The second mistake is converting everything to string and passing everything as string. You may be surprised but SQL can handle non-string values and the classes in .NET SQL library can handle them too...
So do yourself a favor and pass each and every value according the type...

You can create a common function that receives an array of objects and maps their real type to SQL types if you have to...

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