在使用日期时间在INSERT的语法错误的语句 [英] Syntax error in INSERT INTO statement while using DateTime

问题描述

我收到的插入语句中的语法错误，但我想不出为什么。

I am getting a "Syntax error in Insert INTO statement but I cannot figure out why.

我比别人我做这项工作找到，而且找不到任何结构性差异的声明。唯一的区别是，在这其中，我想保存日期时间在我的MS Access数据库。

I compared the statement to others I have made that work find, and can find no structural difference. The only difference is that in this one, I am trying to save a DateTime in my MS Access database.

我在MS访问我的数据库字段，日期/时间设置与一般的格式，它是MM / DD / YY HH：MM：SS AM（或PM）。我有我的DateTime格式设置相同。

I have my database field in MS Access set for Date/Time with a general format, which is MM/DD/YY HH:MM:SS AM (or PM). I have my DateTime format set the same.

当我设置一个破发点，并按照事情的经过，我可以看到DateTime格式传递正确并与在MS Access的一般格式。

When I set a break point and follow it through, I can see that the DateTime format being passed is correct and matches the general format in MS Access.

我敢肯定，这个问题就在fromt我，但我会该死如果我能看到它。这是我的code：

I'm sure the problem is right in fromt of me but I'll be darned if I can see it. Here is my code:

//method to save user input to database
public static void SaveData(ProgramLoginBOL busObject)
{
    try
    {
        String sSQLCommand = "INSERT INTO ProgramLogin ( " +
            "UserName, DateTime) VALUES ('" + busObject.UserName + 
            "','" + busObject.DtDate + "')";

        if (aConnection.State == ConnectionState.Closed)
        {
            aConnection.Open();
        }

        OleDbCommand cmd = aConnection.CreateCommand();
        cmd.CommandText = sSQLCommand;
        // Execute the SQL command
        cmd.ExecuteNonQuery();
        aConnection.Close();

        MessageBox.Show("Data Saved");                 
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        MessageBox.Show("Error! Data was not saved.");
    }
}

正如我所说，调试说明 busObject.DtDate 的MS访问日期/时间格式相匹配

As I stated, debugging shows that busObject.DtDate matches the MS Access date/time format

推荐答案

解决这个问题的方法之一是使用参数化查询来代替。这样做保留这些给供应商担心关于类型，你不必担心使用单引号分隔的字符串值（有助于像奥唐奈名称），或者您的日期值与＃。作为奖励它避免了SQL注入攻击。

One way to solve the problem is to use a Parameterized query instead. Doing this leaves it up to the provider to worry about about types, and you don't have to worry about delimiting your string values with single quotes (helps with names like O'Donnel) or you date values with #. As a bonus it avoids any SQL injection attacks.

要做到这一点用呢？占位符参数

To do this use ? placeholders for the parameters

   string sSQLCommand = "INSERT INTO ProgramLogin ( " +
            "UserName, DateTime) VALUES (?,?)" 

然后再添加参数

then later add the parameters

   cmd.Parameters.AddWithValue("?", busObject.UserName);
   cmd.Parameters.AddWithValue("?", busObject.DtDate);

这篇关于在使用日期时间在INSERT的语法错误的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！