首页 > 代码库 > ACCESS的System.Data.OleDb.OleDbException: INSERT INTO 语句的语法错误

ACCESS的System.Data.OleDb.OleDbException: INSERT INTO 语句的语法错误

一直用的是SQL 数据库,突然改用Access了,使用起来就是没有SQL 顺畅,老是出来些意想不到的错误。今天用Access做的网站程序进行添加数据,调试了一下午,总是异常……

提示ACCESS的System.Data.OleDb.OleDbException: INSERT INTO 语句的语法错误,刚才才在网络上找到了解决的方法,觉得有点不可思议~~在网络上看了看,也是学习ADO.net的人常犯的错误。所以写此日志,以提醒自己

    /// <summary>        /// 增加一条数据        /// </summary>        public int Add(Model.article_comment model)        {            int newId;            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))            {                conn.Open();                using (OleDbTransaction trans = conn.BeginTransaction())                {                    try                    {                        StringBuilder strSql = new StringBuilder();                        strSql.Append("insert into " + databaseprefix + "article_comment(");                        strSql.Append("channel_id,article_id,parent_id,user_id,user_name,user_ip,content,is_lock,add_time,is_reply,reply_content,reply_time,tel,zhengjian,zhengjianNum,workadd,address,youzhengbm,email,type,title,purpose,describe,newsway,getway)");                        strSql.Append(" values (");                        strSql.Append("@channel_id,@article_id,@parent_id,@user_id,@user_name,@user_ip,@content,@is_lock,@add_time,@is_reply,@reply_content,@reply_time,@tel,@zhengjian,@zhengjianNum,@workadd,@address,@youzhengbm,@email,@type,@title,@purpose,@describe,@newsway,@getway)");                                    OleDbParameter[] parameters = {                                new OleDbParameter("@channel_id", OleDbType.Integer,4),                                new OleDbParameter("@article_id", OleDbType.Integer,4),                                new OleDbParameter("@parent_id", OleDbType.Integer,4),                                new OleDbParameter("@user_id", OleDbType.Integer,4),                                new OleDbParameter("@user_name", OleDbType.VarChar,100),                                new OleDbParameter("@user_ip", OleDbType.VarChar,255),                                new OleDbParameter("@content", OleDbType.VarChar),                                new OleDbParameter("@is_lock", OleDbType.Integer,4),                                new OleDbParameter("@add_time", OleDbType.Date),                                new OleDbParameter("@is_reply", OleDbType.Integer,4),                                new OleDbParameter("@reply_content", OleDbType.VarChar),                                new OleDbParameter("@reply_time", OleDbType.Date),                                new OleDbParameter("@tel", OleDbType.VarChar,30),                                 new OleDbParameter("@zhengjian", OleDbType.VarChar,30),                                new OleDbParameter("@zhengjianNum", OleDbType.VarChar,50),                                new OleDbParameter("@workadd", OleDbType.VarChar,30),                                new OleDbParameter("@address", OleDbType.VarChar,30),                                new OleDbParameter("@youzhengbm", OleDbType.VarChar,30),                                new OleDbParameter("@email", OleDbType.VarChar,30),                                new OleDbParameter("@type", OleDbType.VarChar,30),                                new OleDbParameter("@title", OleDbType.VarChar,50),                                                      new OleDbParameter("@purpose", OleDbType.VarChar,100),                                new OleDbParameter("@describe", OleDbType.VarChar,255),                                 new OleDbParameter("@newsway", OleDbType.VarChar,30),                                 new OleDbParameter("@getway", OleDbType.VarChar,30)};                        parameters[0].Value =http://www.mamicode.com/ model.channel_id;                        parameters[1].Value =http://www.mamicode.com/ model.article_id;                        parameters[2].Value =http://www.mamicode.com/ model.parent_id;                        parameters[3].Value =http://www.mamicode.com/ model.user_id;                        parameters[4].Value =http://www.mamicode.com/ model.user_name;                        parameters[5].Value =http://www.mamicode.com/ model.user_ip;                        parameters[6].Value =http://www.mamicode.com/ model.content;                        parameters[7].Value =http://www.mamicode.com/ model.is_lock;                        parameters[8].Value =http://www.mamicode.com/ model.add_time;                        parameters[9].Value =http://www.mamicode.com/ model.is_reply;                        parameters[10].Value =http://www.mamicode.com/ model.reply_content;                        parameters[11].Value =http://www.mamicode.com/ model.reply_time;                        parameters[12].Value =http://www.mamicode.com/ model.tel;                        parameters[13].Value =http://www.mamicode.com/ model.zhengjian;                        parameters[14].Value =http://www.mamicode.com/ model.zhengjianNum;                        parameters[15].Value =http://www.mamicode.com/ model.workadd;                        parameters[16].Value =http://www.mamicode.com/ model.address;                        parameters[17].Value =http://www.mamicode.com/ model.youzhengbm;                        parameters[18].Value =http://www.mamicode.com/ model.email;                        parameters[19].Value =http://www.mamicode.com/ model.type;                        parameters[20].Value =http://www.mamicode.com/ model.title;                        parameters[21].Value =http://www.mamicode.com/ model.purpose;                        parameters[22].Value =http://www.mamicode.com/ model.describe;                        parameters[23].Value =http://www.mamicode.com/ model.newsway;                        parameters[24].Value =http://www.mamicode.com/ model.getway ;                        DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);                        //取得新插入的ID                        newId = GetMaxId(conn, trans);                        trans.Commit();                    }                    catch                    {                        trans.Rollback();                        return -1;                    }                }            }            return newId;        }

修改后的代码如下:

    /// <summary>        /// 增加一条数据        /// </summary>        public int Add(Model.article_comment model)        {            int newId;            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))            {                conn.Open();                using (OleDbTransaction trans = conn.BeginTransaction())                {                    try                    {                        StringBuilder strSql = new StringBuilder();                        strSql.Append("insert into " + databaseprefix + "article_comment(");                        strSql.Append("[channel_id],[article_id],[parent_id],[user_id],[user_name],[user_ip],[content],[is_lock],[add_time],[is_reply],[reply_content],[reply_time],[tel],[zhengjian],[zhengjianNum],[workadd],[address],[youzhengbm],[email],[type],[title],[purpose],[describe],[newsway],[getway])");                        strSql.Append(" values (");                        strSql.Append("@channel_id,@article_id,@parent_id,@user_id,@user_name,@user_ip,@content,@is_lock,@add_time,@is_reply,@reply_content,@reply_time,@tel,@zhengjian,@zhengjianNum,@workadd,@address,@youzhengbm,@email,@type,@title,@purpose,@describe,@newsway,@getway)");                                    OleDbParameter[] parameters = {                                new OleDbParameter("@channel_id", OleDbType.Integer,4),                                new OleDbParameter("@article_id", OleDbType.Integer,4),                                new OleDbParameter("@parent_id", OleDbType.Integer,4),                                new OleDbParameter("@user_id", OleDbType.Integer,4),                                new OleDbParameter("@user_name", OleDbType.VarChar,100),                                new OleDbParameter("@user_ip", OleDbType.VarChar,255),                                new OleDbParameter("@content", OleDbType.VarChar),                                new OleDbParameter("@is_lock", OleDbType.Integer,4),                                new OleDbParameter("@add_time", OleDbType.Date),                                new OleDbParameter("@is_reply", OleDbType.Integer,4),                                new OleDbParameter("@reply_content", OleDbType.VarChar),                                new OleDbParameter("@reply_time", OleDbType.Date),                                new OleDbParameter("@tel", OleDbType.VarChar,30),                                 new OleDbParameter("@zhengjian", OleDbType.VarChar,30),                                new OleDbParameter("@zhengjianNum", OleDbType.VarChar,50),                                new OleDbParameter("@workadd", OleDbType.VarChar,30),                                new OleDbParameter("@address", OleDbType.VarChar,30),                                new OleDbParameter("@youzhengbm", OleDbType.VarChar,30),                                new OleDbParameter("@email", OleDbType.VarChar,30),                                new OleDbParameter("@type", OleDbType.VarChar,30),                                new OleDbParameter("@title", OleDbType.VarChar,50),                                                      new OleDbParameter("@purpose", OleDbType.VarChar,100),                                new OleDbParameter("@describe", OleDbType.VarChar,255),                                 new OleDbParameter("@newsway", OleDbType.VarChar,30),                                 new OleDbParameter("@getway", OleDbType.VarChar,30)};                        parameters[0].Value =http://www.mamicode.com/ model.channel_id;                        parameters[1].Value =http://www.mamicode.com/ model.article_id;                        parameters[2].Value =http://www.mamicode.com/ model.parent_id;                        parameters[3].Value =http://www.mamicode.com/ model.user_id;                        parameters[4].Value =http://www.mamicode.com/ model.user_name;                        parameters[5].Value =http://www.mamicode.com/ model.user_ip;                        parameters[6].Value =http://www.mamicode.com/ model.content;                        parameters[7].Value =http://www.mamicode.com/ model.is_lock;                        parameters[8].Value =http://www.mamicode.com/ model.add_time;                        parameters[9].Value =http://www.mamicode.com/ model.is_reply;                        parameters[10].Value =http://www.mamicode.com/ model.reply_content;                        parameters[11].Value =http://www.mamicode.com/ model.reply_time;                        parameters[12].Value =http://www.mamicode.com/ model.tel;                        parameters[13].Value =http://www.mamicode.com/ model.zhengjian;                        parameters[14].Value =http://www.mamicode.com/ model.zhengjianNum;                        parameters[15].Value =http://www.mamicode.com/ model.workadd;                        parameters[16].Value =http://www.mamicode.com/ model.address;                        parameters[17].Value =http://www.mamicode.com/ model.youzhengbm;                        parameters[18].Value =http://www.mamicode.com/ model.email;                        parameters[19].Value =http://www.mamicode.com/ model.type;                        parameters[20].Value =http://www.mamicode.com/ model.title;                        parameters[21].Value =http://www.mamicode.com/ model.purpose;                        parameters[22].Value =http://www.mamicode.com/ model.describe;                        parameters[23].Value =http://www.mamicode.com/ model.newsway;                        parameters[24].Value =http://www.mamicode.com/ model.getway ;                        DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);                        //取得新插入的ID                        newId = GetMaxId(conn, trans);                        trans.Commit();                    }                    catch                    {                        trans.Rollback();                        return -1;                    }                }            }            return newId;        }

总结:在System.Data.oleDb 命名空间下使用SQL语句插入内容要在字段名与表名加“[]”

ACCESS的System.Data.OleDb.OleDbException: INSERT INTO 语句的语法错误