无法在ACCDB文件中的日期/时间字段中插入/查询日期/时间值 [英] Cannot insert/query date/time value to date/time field in ACCDB file

问题描述

大家好，

我对ACCDB有一个愚蠢的问题。我使用oledb创建连接。
我尝试使用以下代码将数据插入表：

int result;
int userID = 0;
int clientID = 0;
DateTime startTime = DateTime.Now;
OleDbConnection con = new OleDbConnection（）;
con.ConnectionString =" Provider = Microsoft。 ACE.OLEDB.12.0;数据源= my.accdb; Jet OLEDB：数据库密码=密码;" ;;
con.Open（）;

string sql =" INSERT INTO oscTimesheet"
+" （userID，clientID，startTime）"
+" VALUES（@ userID，@ clientID，@ startTime）" ;;

OleDbCommand cmd = new OleDbCommand（）;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;

OleDbParameter pUserID = new OleDbParameter（" @ userID"，userID）;
OleDbParameter pClientID = new OleDbParameter（" @ clientID"，clientID）;
OleDbParameter pStartTime = new OleDbParameter（" @ startTime" ;，DbType.Date）;
pStartTime.Value = startTime;

cmd.Parameters.Add（pUserID）;
cmd.Parameters.Add（pClientID）;
cmd.Parameters。添加（pStartTime）;

cmd.Connec tion = con;
result = cmd.ExecuteNonQuery（）;

注意：userID和clientID是数字字段，startTime是ACCDB文件中的日期/时间。

它始终将结果显示为"条件表达式中的数据类型不匹配"。在我追踪之后，我发现"startTime"是导致我这个问题的专栏。

你能告诉我这个问题的解决方案吗？

谢谢

Hi All,

I have a stupid problem with ACCDB. I have create connection using oledb.
I tried to insert data to table using the following code:

                int result;
                int userID = 0;
                int clientID = 0;
                DateTime startTime = DateTime.Now;
                OleDbConnection con = new OleDbConnection();
                con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=my.accdb;Jet OLEDB:Database Password=Password;";
                con.Open();

                string sql = " INSERT INTO oscTimesheet "
                           + " ( userID, clientID, startTime ) "
                           + " VALUES (@userID, @clientID, @startTime) ";
               
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                OleDbParameter pUserID = new OleDbParameter("@userID", userID);
                OleDbParameter pClientID = new OleDbParameter("@clientID", clientID);
                OleDbParameter pStartTime = new OleDbParameter("@startTime", DbType.Date);
                pStartTime.Value = startTime;
               
                cmd.Parameters.Add(pUserID);
                cmd.Parameters.Add(pClientID);
                cmd.Parameters.Add(pStartTime);
               
                cmd.Connection = con;
                result = cmd.ExecuteNonQuery();

NOTE: userID and clientID is Numeric field, startTime is Date/Time in ACCDB file.

It always shows result as "Data type mismatch in criteria expression.". After I trace, I found that "startTime" is the column which cause me the problem.

Can you, please suggest me solution for this matter?

Thanks

推荐答案

我认为ACE提供程序不支持命名参数，所以你的INSERT SQL语句看起来应该像

string sql =" INSERT INTO oscTimesheet"
+" （userID，clientID，startTime）"
+" VALUES（？，？，？）" ;;

我还建议明确提供@userID和@clientID参数的数据类型。只要保持参数的相同顺序，其余代码应保持不变

I do not think  ACE provider supports named parameters, so your INSERT SQL statement should look like

string sql = " INSERT INTO oscTimesheet "
                           + " ( userID, clientID, startTime ) "
                           + " VALUES (?, ?, ?) ";

I would also recommend to provide datatype for the @userID and @clientID parameters explicitly. Rest of the code should stay the same as long as you keep same order of the parameters
               

这篇关于无法在ACCDB文件中的日期/时间字段中插入/查询日期/时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！