为什么以下代码在从字符串转换为datetime时出错 [英] why is the following code giving error when converting from string to datetime

问题描述

我有以下代码，说'无法打开SQL连接System.Data.SqlClient.SqlException（0x80131904）：将varchar数据类型转换为日期时间数据类型导致了一个不合格的数据
范围值。'输入日期'13 -01-2013'时会发生这种情况。尽管我将其指定为日期，但是月份需要13个月。代码如下。请指教。

I have this following code which is saying 'cannot open SQL connection System.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-
range value.' This is happening when the date '13-01-2013' is entered. It is taking 13 as month though I specify it as date. Code is as follows. Please advice.

for (int i = 1; i < 13; i++)
{
   for (int j = 1; j < 31; j++)
   {
      for (int k = 0; k < 24; k++)
      {
         for(int l=0;l<60;l=l+10)
         {
            DateTime myDate = DateTime.ParseExact("2013-" + i.ToString("00") + "-" + j.ToString("00") + " "+k.ToString("00")+":" + l.ToString("00") + ":00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            string query = "INSERT INTO GCMemory(AgentID,GCTitle,GCProcessName,GCWindowHandle,GCMemory,DTime)" + "VALUES(1,'" + "Inbox (312) - [e-mail] - Gmail - Google Chrome" + "','" + "chrome" + "','" + r.Next(1, 184467440).ToString() + "','" + r.Next(1, 1000000000).ToString() + "','" + myDate + "')";
            SqlCommand insertQuery = new SqlCommand(query, myConnection);
            insertQuery.ExecuteNonQuery();
         }
         Console.WriteLine("one day done");
      }
      Console.WriteLine("one month done");
   }
}

推荐答案

第一个也是最明显的问题是你正在使用字符串连接构建您的查询。在此特定实例中，由于您只处理已生成的已知值，因此这不是一个大问题。但是，如果您在其他任何地方使用此模式，特别是在用户提供参数的情况下，它将容易受到 SQL注入 [ ^ ]。



更改代码以使用参数化查询。它不仅会通过传递日期参数来解决问题，而且通过不再使用字符串连接来构建查询的习惯，以后当Bobby Tables [ ^ ]使用您的应用。 :)



第二个问题是你的循环假设每个月31天。一旦你到了二月，你的 DateTime.ParseExact 调用将失败。



尝试这样的事情：

The first, and most obvious, problem is that you're using string concatenation to build your query. In this particular instance, since you're only dealing with known values that you've generated, it's not a huge problem. However, if you're using this pattern anywhere else, particularly where the parameters are provided by the user, it will be susceptible to SQL Injection[^].

Change your code to use a parameterized query. Not only will it fix the problem with passing a date parameter, but by getting out of the habit of using string concatenation to build your queries, you won't have a nasty surprise later on when Bobby Tables[^] uses your application. :)

The second problem is that your loop assumes 31 days for every month. As soon as you get to February, your DateTime.ParseExact call will fail.

Try something like this:

using (SqlCommand insertQuery = new SqlCommand("INSERT INTO GCMemory(AgentID, GCTitle, GCProcessName, GCWindowHandle, GCMemory, DTime) VALUES (@AgentID, @GCTitle, @GCProcessName, @GCWindowHandle, @GCMemory, @DTime)", myConnection))
{
    insertQuery.Parameters.AddWithValue("@AgentID", 1);
    insertQuery.Parameters.AddWithValue("@GCTitle", "Inbox (312) - [e-mail] - Gmail - Google Chrome");
    insertQuery.Parameters.AddWithValue("@GCProcessName", "chrome");
    
    var GCWindowHandle = insertQuery.Parameters.Add("@GCWindowHandle", SqlDbType.Int);
    var GCMemory = insertQuery.Parameters.Add("@GCMemory", SqlDbType.Int);
    var DTime = insertQuery.Parameters.Add("@DTime", SqlDbType.DateTime);

    DateTime theDate = new DateTime(2013, 1, 1);
    while (theDate.Year == 2013)
    {
        GCWindowHandle.Value = r.Next(1, 184467440);
        GCMemory.Value = r.Next(1, 1000000000);
        DTime.Value = theDate;
        insertQuery.ExecuteNonQuery();
        
        DateTime nextDate = theDate.AddMinutes(1);
        if (nextDate.Month != theDate.Month)
        {
            Console.WriteLine("one month done");
        }
        else if (nextDate.Day != theDate.Day)
        {
            Console.WriteLine("one day done");
        }
        
        theDate = nextDate;
    }
}

简单解决方案：将日期的更改按日，月，年更改。 />
例如convert（datetime，mydate，103）。

Simple solution: change your contatenation of the date to order by day, then month, then year.
For example convert(datetime, mydate, 103).

这篇关于为什么以下代码在从字符串转换为datetime时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！