一直用的是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 = model.channel_id;
parameters[1].Value = model.article_id;
parameters[2].Value = model.parent_id;
parameters[3].Value = model.user_id;
parameters[4].Value = model.user_name;
parameters[5].Value = model.user_ip;
parameters[6].Value = model.content;
parameters[7].Value = model.is_lock;
parameters[8].Value = model.add_time;
parameters[9].Value = model.is_reply;
parameters[10].Value = model.reply_content;
parameters[11].Value = model.reply_time;
parameters[12].Value = model.tel;
parameters[13].Value = model.zhengjian;
parameters[14].Value = model.zhengjianNum;
parameters[15].Value = model.workadd;
parameters[16].Value = model.address;
parameters[17].Value = model.youzhengbm;
parameters[18].Value = model.email;
parameters[19].Value = model.type;
parameters[20].Value = model.title;
parameters[21].Value = model.purpose;
parameters[22].Value = model.describe;
parameters[23].Value = model.newsway;
parameters[24].Value = 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 = model.channel_id;
parameters[1].Value = model.article_id;
parameters[2].Value = model.parent_id;
parameters[3].Value = model.user_id;
parameters[4].Value = model.user_name;
parameters[5].Value = model.user_ip;
parameters[6].Value = model.content;
parameters[7].Value = model.is_lock;
parameters[8].Value = model.add_time;
parameters[9].Value = model.is_reply;
parameters[10].Value = model.reply_content;
parameters[11].Value = model.reply_time;
parameters[12].Value = model.tel;
parameters[13].Value = model.zhengjian;
parameters[14].Value = model.zhengjianNum;
parameters[15].Value = model.workadd;
parameters[16].Value = model.address;
parameters[17].Value = model.youzhengbm;
parameters[18].Value = model.email;
parameters[19].Value = model.type;
parameters[20].Value = model.title;
parameters[21].Value = model.purpose;
parameters[22].Value = model.describe;
parameters[23].Value = model.newsway;
parameters[24].Value = 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语句插入内容要在字段名与表名加“[]”