update 更新EXCEL 报错

[cc_fq86]

报错原因:
语法错误(数据丢失)
代码:
update = "UPDATE [sheet1$] SET WH ='" + zc + "' , PDS ='" + za + "' WHERE　XH ='" + zd + "'";
OleDbCommand xrdliming = new OleDbCommand(update, cn);
xrdliming.ExecuteNonQuery()

[j45kp]

cmd.Parameters.AddWithValue("@w",ff);

[cc_fq86]

[Quote=引用 18 楼 J45KP 的回复:]
cmd.Parameters.AddWithValue("@w",ff);
[/Quote]

能不能只把更新的所有代码,全部写出来一下好吗

[j45kp]

//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + "\\" + timeFlag + ".xls" + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;

conn.Open();
string sqlStr = "UPDATE [sheet1$] SET WH =@w , PDS =@p WHERE　XH =@z";

OleDbCommand rcmd = new OleDbCommand();
rcmd.Connection = conn;
rcmd.CommandText = sqlStr;
rcmd.CommandType = CommandType.Text;
rcmd.Parameters.AddWithValue("@w", 11);
rcmd.Parameters.AddWithValue("@p", 11);
rcmd.Parameters.AddWithValue("@z", 11);
rcmd.ExecuteNonQuery();

[cc_fq86]

[Quote=引用 20 楼 J45KP 的回复:]


//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + "\\" + timeFlag + ".xls" + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbCommand cmd = new OleDbCommand();
cmd.Connect…
[/Quote]
还是出语法错误呢(操作符丢失呢)

[cc_fq86]

[Quote=引用 20 楼 J45KP 的回复:]


//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + "\\" + timeFlag + ".xls" + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbCommand cmd = new OleDbCommand();
cmd.Connect…
[/Quote]

语法错误 (操作符丢失) 在查询表达式 '@p WHERE XH =@z' 中。

[EveryCase]

顶

[wuyq11]

应先把EXCEL导入到dataset里再修改数据

[oec2003]

要将数据导出修改
参考

[ximi82878]

还能这么用的？学习了

[cc_fq86]

[Quote=引用 3 楼 wuyq11 的回复:]
应先把EXCEL导入到dataset里再修改数据
[/Quote]

导进去了

[cc_fq86]

[Quote=引用 3 楼 wuyq11 的回复:]
应先把EXCEL导入到dataset里再修改数据
[/Quote]

能不能就在EXCEL,里面修改呢

[cc_fq86]

补充
导入LISTVIEW
列表
OpenFileDialog open = new OpenFileDialog();
open.DefaultExt = "*.xls";
open.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
open.RestoreDirectory = true;
open.FilterIndex = 1;
if (open.ShowDialog() == DialogResult.OK)
{
Filname = open.FileName;
try
{
this.pdslistView.Items.Clear();
this.pdslistView.Refresh();
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = Filname;
connectStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");

using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
OleDbCommand cmdLiming = new OleDbCommand("SELECT * FROM [Sheet1$]", cn);
cn.Open();
OleDbDataReader drLiming = cmdLiming.ExecuteReader();
while (drLiming.Read())
{

wwww = drLiming["PDS"].ToString();
xgcomboBox.Items.Add(drLiming["XH"].ToString());
pdssltextBox.Text=drLiming["SL"].ToString();
pdsxftextBox.Text = drLiming["XF"].ToString();
a = wwww.Substring(0, 6);
a = a.ToUpper();
//加载
ListViewItem li = new ListViewItem();
li.SubItems.Clear();
li.SubItems[0].Text = qqqq = drLiming["XH"].ToString();
li.SubItems.Add(tttt = drLiming["WH"].ToString());
li.SubItems.Add(aaa);
li.SubItems.Add(wwww);
li.SubItems.Add(pdspes);
li.SubItems.Add("");
li.SubItems.Add("");
li.SubItems.Add(q);
li.SubItems.Add(PESq);
li.SubItems.Add("");
li.SubItems.Add("");
li.SubItems.Add(rrrr = drLiming["SL"].ToString());
li.SubItems.Add(pdsxftextBox.Text);
li.SubItems.Add(r);
li.SubItems.Add(PESr);
li.SubItems.Add("");
li.SubItems.Add("");
li.SubItems.Add("");

pdslistView.Items.Add(li);


}
cn.Close();
}

[chuxue1342]

Excel不允许执行修改语句呀!

[jiang_jiajia10]

[Quote=引用 7 楼 c284980697 的回复:]
引用 3 楼 wuyq11 的回复:
应先把EXCEL导入到dataset里再修改数据


能不能就在EXCEL,里面修改呢
[/Quote]
肯定要导入到数据集里面修改了

[RedRain0220]

帮项了

[skeljy]

mark

[cc_fq86]

[Quote=引用 10 楼 jiang_jiajia10 的回复:]
引用 7 楼 c284980697 的回复:
引用 3 楼 wuyq11 的回复:
应先把EXCEL导入到dataset里再修改数据


能不能就在EXCEL,里面修改呢

肯定要导入到数据集里面修改了
[/Quot

我是这样做的,先导入EXCEL

在运行过程中计算型号,如果出现错误,修改过后执行,更新EXCEL,在读一次EXCEL表里的型号,重新计算价格;

现在怎样做呢e]

[j45kp]

可以用参数形式
"UPDATE [sheet1$] SET WH =@w , PDS =@p WHERE　XH =@z;
cmd.add.paramter(@w,ff);
cmd.add.paramter(@p,ff);
cmd.add.paramter(@z,ff);
cmd.ExecuteNonQuery();

[cc_fq86]

补充一下:
全部代码
Filname:指EXCEL路径
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = Filname;
connectStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");

using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
cn.Open();
//OleDbCommand xrdliming = new OleDbCommand("UPDATE [Sheet1$] SET 型号='" + za + "' where　序号='" + xgcomboBox.Text + "'", cn);
update = "UPDATE [sheet1$] SET WH ='" + zc + "' , PDS ='" + za + "' WHERE　XH ='" + zd + "'";
OleDbCommand xrdliming = new OleDbCommand(update, cn);
xrdliming.ExecuteNonQuery();
cn.Close();
}

[cc_fq86]

谢谢各位