将日期时间插入 Access [英] Insert DateTime into Access

问题描述

问题:我正在尝试使用 C# 中的 Oledb 接口将日期时间插入到访问数据库中.

The problem: I'm trying to insert a date time into an access database using the Oledb interface in C#.

hacking 解决方案:在不使用 command.Properties 的情况下生成我的插入字符串

Hacking solution: Generate my on insert string without using command.Properties

我可以毫无问题地将文本插入到数据库中，但是在尝试 datetime 时，我最终遇到此错误:System.Data.OleDb.OleDbException {"条件表达式中的数据类型不匹配."}

I can insert text into the database with no problem, but when trying datetime, I end up with this error: System.Data.OleDb.OleDbException {"Data type mismatch in criteria expression."}

有几篇与此类似的帖子，但可惜没有可行的解决方案.

There are several posts similar to this but alas with no working solution.

这是我的代码:

void TransferData()
{
    string instCmd = Get_InsertCommand(0); // hard coded table 0 for testing

    Fill_ProductTable_ToInsert();

    con.Open();


    // It would be nice not to have to separate the date indexes
    int[] textIndex = { 0, 1, 2, 3, 4, 7 };
    int[] dateIndex = { 5, 6 };
    try
    {
        foreach (DataRow row in DataToStore.Tables[0].Rows)
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = con;

            command.CommandText = instCmd;

            foreach(int j in textIndex)
                command.Parameters.AddWithValue("@" + j, row[j]);
            foreach (int j in dateIndex)
            {

                // TESTING CODE
                ///////////////////////////////////////////////////////////////////////////

                string input = "#\'" +DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") +"\'#";
                command.Parameters.AddWithValue("@" + j,    input.ToString());
                Program.WriteLine(input.ToString());

                ///////////////////////////////////////////////////////////////////////////
            }


            command.ExecuteNonQuery();
        }
    }
    finally
    {
        con.Close();
    }
}

string Get_InsertCommand(int i)
{
    string sqlIns = "INSERT INTO " + DataToStore.Tables[0].TableName + " (";
    string temp = "VALUES (";
    for (int j = 0; j < expected_header[i].Length - 1; j++)
    {
        sqlIns += expected_header[i][j] + ", ";
        temp += "@" + j + ", ";
    }

    int lastIndex = expected_header[i].Length -1;
    sqlIns += expected_header[i][lastIndex] + ") ";
    temp += "@" + lastIndex + ")";
    sqlIns += temp;

    return sqlIns;
}

在标记为测试代码的区域内，我尝试了我能想到的所有日期时间排列.我用 # 和 ' 尝试了所有格式我尝试了这些格式:yyyy-MM-dd、yyyyMMdd、yyyy\MM\dd、yyyy/MM/dd我也试过 ToOADate()和 ToString(), ToShortDateString()

Inside the area labeled testing code, I have tried every permutation of date time I could think of. I tried every format with # and ' I tried these formats: yyyy-MM-dd, yyyyMMdd, yyyy\MM\dd, yyyy/MM/dd I also tried ToOADate() And ToString(), ToShortDateString()

我也尝试将数据库设置为接受 ANSI-92 Sql

I also tried setting the database to accept ANSI-92 Sql

我的想法快用完了.

注意:此代码设置为处理来自多个数据库的多个表，注意循环...

Note: This code is set up to deal with multiple tables from multiple databases, mind the loops...

推荐答案

正确使用参数，不要担心在查询中连接的日期时间值的格式.我不明白您为什么要将日期时间值转换为字符串值?

Use parameters properly, and don't worry about the format of the datetime value that you concatenate in your query. I don't understand why you want to convert the datetime value to a string value ?

DateTime theDate = new DateTime(2012,10,16);
var cmd = new OleDbCommand();
cmd.CommandText = "INSERT INTO sometable (column) VALUES (@p_bar)";
cmd.Parameters.Add ("@p_bar", OleDbType.DateTime).Value = theDate;

这篇关于将日期时间插入 Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！