c# access 标准表达式中数据类型不匹配。
//下面是保存按钮的代码
private void save_button_Click(object sender, EventArgs e)
{
dataGridView1.EndEdit();
string sqlText ="select ckid,步序,程控时间,[01],[02],[03],[04],[05],[06] from ckasd where ckid= 1 order by 步序"；
ds = new ConnDb().updataDs(ds, "ckasd",sqlText);
dataGridView1.DataSource = ds.Tables["ckasd"];
//不显示新增行
dataGridView1.AllowUserToAddRows = false;
//显示各列
dataGridView1.AutoGenerateColumns = true;
}
public DataSet updataDs(DataSet changedDs, string tablename,string sql)
{
try
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
this.cmdbuilder = new OleDbCommandBuilder(da);
da.Update(changedDs, tablename);
changedDs.AcceptChanges();
connection.Close();
MessageBox.Show("数据更新成功");
return changedDs;//返回更新了的数据表
}
catch
{
connection.Close();
MessageBox.Show( "数据更新失败");
throw;
}
}
数据库为access,字段01,02,03,04,05,06均为文本型，字段大小为1，默认值设置为" ";当填充" "空格的时候，能正常保存。字段01,02,03,04,05,06填充"+"，不包括引号的+的时候，出现错误，提示是“标准表达式中数据类型不匹配.”
另外调试中的updata实际执行的句子是
执行 NonQuery“UPDATE ckasd SET 02 = ? WHERE ((CKID = ?) AND (步序 = ?) AND ((? = 1 AND 程控时间 IS NULL) OR (程控时间 = ?)) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))” 命令文本 = "UPDATE ckasd SET 02 = ? WHERE ((CKID = ?) AND (步序 = ?) AND ((? = 1 AND 程控时间 IS NULL) OR (程控时间 = ?)) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))",
这个问题出在哪儿了？
[最优解释]
数字作为字段名，有点不好。
[其他解释]
有中文的地方全加上[]，试试
string sqlText ="select ckid,[步序],[程控时间],[01],[02],[03],[04],[05],[06] from ckasd where ckid= 1 order by [步序]"；
[其他解释]
文本型字段在添加的时候得用"'+'"，而不是"+"
[其他解释]
这和 类型无关吧
[其他解释]
应该不是这个原因，我用字符作为字段名，利用datagridview的自动更新也会出现问题
[其他解释]
sqlText = String.Format("UPDATE ckasd SET [01]='+',[02]='+', [03]='+',[04]='+', [05]='+',[06]='+' WHERE ckid ={0} and 步序= 6 ", f1.str_main_ckid);
用上面的sql更新正常。
sqlText = String.Format("Insert into ckasd (ckid,步序,程控时间,[01],[02],[03],[04],[05],[06]) values ({0},365,200,'+',' ','+',' ','+',' ')", f1.str_main_ckid);
用上面的sql插入也正常。
那为什么会出现 标准表达式中数据类型不匹配 的问题呢
[其他解释]
还有，datagridview1新增行里面，新增加行，然后编辑，这时候更新能成功，那就说明自动生成的Insert into ckasd应该是正确的。
[其他解释]
错误提示：$exception{"标准表达式中数据类型不匹配。"}System.Exception {System.Data.OleDb.OleDbException}
+执行 NonQuery“UPDATE ckasd SET 01 = ? WHERE ((ckid = ?) AND (步序 = ?) AND (程控时间 = ?) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))”命令文本 = "UPDATE ckasd SET 01 = ? WHERE ((ckid = ?) AND (步序 = ?) AND (程控时间 = ?) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))", 连接字符串 = "Provider=Microsoft.Jet.OleDb.4.0;Data Source='G:\\测试\\yypqApplication\\yypqApplication\\db3 - 副本.mdb'"
+
[其他解释]
public DataSet updataDs(DataSet changedDs, string tablename,string sql)
{
try
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
this.cmdbuilder = new OleDbCommandBuilder(da);
da.Update(changedDs, tablename);
changedDs.AcceptChanges();
connection.Close();
MessageBox.Show("数据更新成功");
return changedDs;//返回更新了的数据表
}
catch
{
connection.Close();
MessageBox.Show( "数据更新失败");
throw;
}
}
上面是更新代码
下面是数据库字段
[其他解释]
也就是INSERT INTO ckasd ([01]) VALUES("+");就相当于INSERT INTO ckasd ([01]) VALUES(+);这时候的 + 是连接符，而不是字符 '+'
[其他解释]
如果只是新增行，而不修改其他行的情况下，更新是正常的。那就是说生成自动插入语句是正常的。那时候INSERT INTO ckasd 没有什么问题的
问题出现在修改原来行的时候，以及删除行的时候。
[其他解释]
还是DENQH 说的对，居然还是字段的问题。
执行 NonQuery“UPDATE ckasd SET 02 = ? WHERE ((CKID = ?) AND (步序 = ?) AND ((? = 1 AND 程控时间 IS NULL) OR (程控时间 = ?)) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))” 命令文本 = "UPDATE ckasd SET 02 = ? WHERE ((CKID = ?) AND (步序 = ?) AND ((? = 1 AND 程控时间 IS NULL) OR (程控时间 = ?)) AND ((? = 1 AND 01 IS NULL) OR (01 = ?)) AND ((? = 1 AND 02 IS NULL) OR (02 = ?)) AND ((? = 1 AND 03 IS NULL) OR (03 = ?)) AND ((? = 1 AND 04 IS NULL) OR (04 = ?)) AND ((? = 1 AND 05 IS NULL) OR (05 = ?)) AND ((? = 1 AND 06 IS NULL) OR (06 = ?)))",
((? = 1 AND 01 IS NULL) OR (01 = ?)) 估计是这里面出了问题。也许应该是((? = 1 AND 01 IS NULL) OR ([01] = ?))?
