ASP.NET C# 向 ACCESS 插入日期型数据 标准表达式中数据类型不匹配

 

public static string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        System.Web.HttpContext.Current.Server.MapPath("db.mdb");   

public int addMsg(string name, string sex, string web, string email, string words, string ip, string come, string qq)
    {//返回影响操作的行数--添加
        try
        {

//string sqlIns = "insert into 表名(name,nowDate) values('helloworld',#" + DateTime.Now.ToLocalTime() +"#)"
//注意表名和列名不能为关键字：如date,time等

            string QueryString = "insert into words(name,sex,web,email,words,Createdate,ip,come,qq) values(@name,@sex,@web,@email,@words,@Createdate,@ip,@come,@qq)";
            OleDbConnection conn = new OleDbConnection(connstring);
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(QueryString, conn);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@sex", sex);
            cmd.Parameters.AddWithValue("@web", web);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@words", words);
          

          //cmd.Parameters.AddWithValue("@Createdate", DateTime.Now.ToLocalTime());

            OleDbParameter parameter = new OleDbParameter();
            parameter.OleDbType = OleDbType.DBDate;
            parameter.Value = DateTime.Now.ToLocalTime();
            cmd.Parameters.Add(parameter);

            cmd.Parameters.AddWithValue("@ip", ip);
            cmd.Parameters.AddWithValue("@come", come);
            cmd.Parameters.AddWithValue("@qq", qq);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            return 0;
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

PS:

在向Access数据库中添加数据的时候，如果是日期字段，则会出现“标准表达式中数据类型不匹配。”的错误，这可能是C#中的日期类型无法直接转换成Access中的日期类型OleDbType.DBDate所致.

如下：

//cmd.Parameters.AddWithValue("@Createdate", time);

解决办法就是将上面语句中的 cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now); 转换成下面的语句即可：

解决办法就是将上面语句中的 cmd.Parameters.AddWithValue("@Createdate", DateTime.Now.ToLocalTime());转换成下面的语句即可：

if (commandParameters[i].Value is DateTime)
                    {
                        OleDbParameter parameter = new OleDbParameter();
                        parameter.ParameterName = commandParameters[i].ParameterName;
                        parameter.OleDbType = OleDbType.DBDate;
                        parameter.Value = commandParameters[i].Value;
                        cmd.Parameters.Add(parameter);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value);
                        cmd.Parameters[i].Direction = commandParameters[i].Direction;
                    }