c#操作access查询更改简单问题请教
论文问答
1
如图access数据库表1表2，我想实现查询“Xid”在表2出现的次数以更改表1中对应的字段“出现次数”，现在的想法是将表1数据存在Datatable中（dt），遍历dt，查询字段Xid在表2出现的次数count，同时将count去update道表1中，代码实现写到了这里，请教一下接下来如何实现存count啊
private void Button_Click(object sender, RoutedEventArgs e)
{
string path = "D:\\data";
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\test.mdb";
OleDbConnection conn = new OleDbConnection(strConnect);
//OleDbCommand cmd = new OleDbCommand("select * from 表2", conn);
OleDbCommand acmd = new OleDbCommand("select * from 表1", conn);
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(acmd);
OleDbCommandBuilder odb = new OleDbCommandBuilder(da);
odb.QuotePrefix = "[";
odb.QuotePrefix = "]";
DataSet ads = new DataSet();
da.Fill(ads,"表1");
DataTable dt = ads.Tables["表1"];
for (int i = 0; i < dt.Rows.Count; i++)
{
string str = "select count(*) as count from 表2 where Xid =" + dt.Rows[i]["Xid"].ToString();
OleDbCommand c = new OleDbCommand(str, conn);
}
conn.Close();
}
-
Assess的联表更新比较麻烦，还是得分两次 先
select Xid, count(1) as cnt from表2 group by Xid，得到的表每一行是Xid和cnt，cnt就是Xid出现的次数 然后遍历结果表的每一行,，执行string sql = "update 表1 set 出现次数 = " + cnt + " where Xid = '" + xid + "'" -
update 表1 set 出现次数 = (case when cnt is null then 0 else cnt end) from 表1 a left join (select Xid, count(1) as cnt from 表2 group by Xid) b on a.Xid = b.Xid不用那么麻烦，执行这1个sql语句就可以啦
发表回复