之前我写好了一个数据访问层(oledb),可以用来操作oracle和sqlServer,都没问题.可是我昨天用它来操作Access数据库就有问题了.查询Access数据库是没有问题的,只是我想用adapter1.Update(updateDataSet, tableName),老是提醒我说是insert into的语法有错误,怎么可能呢!我先前用它操作oracle和sqlServer是没有问题的,而且我也跟踪了一下insert into语句的具体格式,是没有问题的!!请高手解答.....在线等! 非常感谢
如果写Select * from table where 1=2 是可以查询的,
可是写成select field1,field2 from table where 1=2 就有问题了
现在问题找到了.是我要插入的字段中有日期类型,可我不知道怎么改?
+"'"+DateTime.Now.Date+"'"日期如上保存试试看。
//cmdBuilder.RefreshSchema();
OleDbCommand insertCommand = cmdBuilder.GetInsertCommand();
OleDbCommand updateCommand = cmdBuilder.GetUpdateCommand();
OleDbCommand deleteCommand = cmdBuilder.GetDeleteCommand(); OleDbCommand insertCommand1 = new OleDbCommand();
OleDbCommand updateCommand1 = new OleDbCommand();
OleDbCommand deleteCommand1 = new OleDbCommand(); int columnCount = ds.Tables[tableName].Columns.Count; string insertText = insertCommand.CommandText; string str1 = updateCommand.CommandText;
string updateText = "";
int indexOfWHERE1 = str1.IndexOf(" WHERE ");
int indexOfOkAND1 = str1.IndexOf(" AND ",indexOfWHERE1);
if (indexOfOkAND1 >-1)
updateText = str1.Substring(0, indexOfOkAND1) + " )";
else
updateText = str1;
//string updateText = str1.Substring(0, indexOfOkAND1) + " )"; string str2 = deleteCommand.CommandText;
// int indexOfWHERE2 = str2.IndexOf(" WHERE ");
// int indexOfOkAND2 = str2.IndexOf(" AND ",indexOfWHERE2);
// string deleteText = str2.Substring(0, indexOfOkAND2) + " )";
string deleteText;
int indexOfWHERE2 = str2.IndexOf(" WHERE ");
int indexOfOkAND2 = str2.IndexOf(" AND ",indexOfWHERE2);
if (indexOfOkAND2 > -1)
deleteText = str2.Substring(0, indexOfOkAND2) + " )";
else
deleteText = str2;
//string deleteText = str2; insertCommand1.CommandText= insertText;
updateCommand1.CommandText= updateText;
deleteCommand1.CommandText= deleteText; insertCommand1.Connection = insertCommand.Connection;
updateCommand1.Connection = insertCommand.Connection;
deleteCommand1.Connection = insertCommand.Connection; foreach(OleDbParameter parm in insertCommand.Parameters)
{
insertCommand1.Parameters.Add( (OleDbParameter)((ICloneable)parm).Clone() );
} for(int i=0; i<=columnCount; i++)
{
updateCommand1.Parameters.Add( (OleDbParameter)((ICloneable)updateCommand.Parameters[i]).Clone() ); } deleteCommand1.Parameters.Add( (OleDbParameter)((ICloneable)deleteCommand.Parameters[0]).Clone() ); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.DeleteCommand = deleteCommand1;
adapter.InsertCommand = insertCommand1;
adapter.UpdateCommand = updateCommand1; if(deleteCommand.Connection.State != ConnectionState.Open)
deleteCommand.Connection.Open(); return adapter.Update(ds, tableName);
string strConn = "...";
string strCmd = "insert into yourtable(cl1, cl2, cl3 ) values(?,?,?)"; OledbConnection conn = new OledbConnection( strConn );
OledbCommand comm = new OledbCommand( strCmd, conn );comm.Parameters.Add( "@cl1", DateTime.Now.ToString() );
comm.Parameters.Add( "@cl2", 插入的值 );
comm.Parameters.Add( "@cl3", 插入的值 );conn.Open();
comm.ExecuteNonQuery();
conn.Close();
values('D000000043', '4400000000002356', '护照', #2006-8-25 10:38:23#,#2008-8-25 0:00:00#, '已发证', '', '', #2006-8-26 9:51:28#, '', '', '')请大家帮我检查一下
insert into ZhengJ(Pass_num,Per_num,Type,Iss_date,Exp_date,Status,iss_offi,Note,Chg_date,Place,State_date,State)
values('D000000043', '4400000000002356', '护照', '#2006-8-25 10:38:23#','#2008-8-25 0:00:00#', '已发证', '', '', '#2006-8-26 9:51:28#', '', '', '')试试看
要看是什么类型的adapter,要看是什么类型的数据库
对不同的数据库,最好自己写不同的sql语句
在sql server能支持的语句,有部分是Access不支持的
修改时在字段前加上表名;如update table1 set table1.name='test2' where id=1
values('D000000043', '4400000000002356', '护照', #2006-8-25 10:38:23#,#2008-8-25 0:00:00#, '已发证', '', '', #2006-8-26 9:51:28#, '', '', '')改为insert into ZhengJ([Pass_num],[Per_num],[Type],[Iss_date],[Exp_date],[Status],[iss_offi],[Note],[Chg_date],[Place],[State_date],[State])
values('D000000043', '4400000000002356', '护照', #2006-8-25 10:38:23#,#2008-8-25 0:00:00#, '已发证', '', '', #2006-8-26 9:51:28#, '', '', '')