获取错误，当尝试插入GETDATE（）到数据库 [英] Getting error when try to insert GetDate() into database

问题描述

我试图插入今天的日期，并尝试递归地增加日期。但我正在逐渐转换错误消息。

I am trying to insert today's date and try increment the date recursively. but I am getting conversion error message.

private void InsertTimesheetWeek(string timeSheetID)
{
    int row = GViewTimeSheet.Rows.Count;//get the row count
    int counter = 0;

    string[] txtDate = new string[row];// date column

    foreach (GridViewRow gRow in GViewTimeSheet.Rows)
    {

        txtDate[counter] = "GetDATE()+"+counter;

        counter++;
    }

    //Intializing sql statement
    string fields = "(TimeSheetID, Date)";
    string parm = "(@TimeSheetID, @Date)";
    string sqlStatement = "insert into TimeSheetWeeks" + fields + "Values" + parm;

    SqlCommand comm = new SqlCommand();
    comm.CommandText = sqlStatement;//assing sql statement as command
    SqlConnection connection = DataAccess.getConnection();
    comm.Connection = connection;

    try
    {
        connection.Open();
        for (int i = 0; i < row; i++)
        {
            comm.Parameters.AddWithValue("@TimeSheetID", timeSheetID);
            comm.Parameters.AddWithValue("@Date", txtDate[i]);

            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }

    }
    catch (Exception ex)
    {
        Utilities.LogError(ex);
        throw ex;
    }
    finally
    {
        if (connection.State == ConnectionState.Open)// if the connection opened then 
        {
            connection.Close();//just close the connection in any way
        }
    }
}

为什么它会导致一个错误？

Why it causes an error?

推荐答案

该错误是由路过造成了GETDATE（）+ 1，GETDATE（）+ 2等作为参数，SQL无法转换这一个日期。

The error is caused by passing "GetDATE()+1", "GetDATE()+2", etc. as a parameter and SQL can't convert this to a date.

发送到SQL之前，不要在code中的日期计算：

Do the date calculations in code before sending to SQL:

txtDate[counter] = DateTime.Now.AddDays(counter);

如果你正在构建SQL语句作为文字的每一行你所采用的方法是可行的。结果
例如：*

The approach you've taken would work if you were building the SQL statement as a literal for each row.
ex:*

for (int i = 0; i < row; i++)
{
    string fields = "(TimeSheetID, Date)";
    string parm = String.Format"({0}, GetDATE() + {1})", timeSheetID, i);
    string sqlStatement = "insert into TimeSheetWeeks" + fields + "Values" + parm;

    // ....

* N.B。不要使用上述code - 我提供了它作为唯一的一个例子。始终使用SQL参数，如果可能的话。参数是类型安全和减轻SQL注入的风险。 如何：防止SQL注入在ASP.NET

*N.B. Don't use the above code - I've provided it as an example only. Always use SQL parameters if possible. Parameters are type-safe and mitigate the risk of SQL injection. How To: Protect From SQL Injection in ASP.NET

这篇关于获取错误，当尝试插入GETDATE（）到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！