更新excel：UPDATE语句中的语法错误 [英] Update excel : syntax error in UPDATE statement

问题描述

大家好，



我正在尝试更新excel文件。我使用OLEDB连接从excel读取数据，现在我想用它来更新数据。



我可以将数据从excel导入到datagridview。因此，如果无论如何都要将datagridview中的所有数据更新为excel，那就太棒了。我愿意接受任何建议。



这是我的代码：

Hello everyone,

I'm trying to update an excel file. I used OLEDB connection for read data from excel and now i want to use it for update the data.

I can import data from excel to datagridview. So if there is anyway to update all data from datagridview to excel, it would be great. I'm open for any suggestion.

Here is my code :

OleDbConnection baglan = new OleDbConnection();
                            baglan.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + yeniDosya + "; Extended Properties = 'Excel 8.0;IMEX=1;'";
                            baglan.Open();
                            string sit = sheetadi;
                            sit = sit.TrimEnd('\'');
                            sit = sit.TrimStart('\'');
                            string shtsrg = sit + "K" + i + ":K" + i;
                            
                            string excel = String.Format("UPDATE {0} SET F1=" + dataGridView1.Rows[i].Cells[9].Value.ToString() + "", shtsrg);
                            MessageBox.Show(excel);
                            OleDbCommand cmd = new OleDbCommand(excel, baglan);
                            cmd.ExecuteNonQuery();

让我简单解释一下：



yenidosya：是excel文件名

sheetadi：是床单名称（我是从其他地方带的）

在sheetadi我有'开始和结束。所以我修剪它

i是我的列号，它可以改变参数

with shtsrg我想合并sheetname和列号（它也是我的表名）

我使用excel字符串制作表格参数



我正在尝试使用这种方法：



如何使用oledb连接更新Excel工作表的单个单元格ASP.NET论坛 [ ^ ]



但它给了我一个错误：

Let me explain simply :

yenidosya : is excel files name
sheetadi : is sheets name ( i am taking it from somewhere else )
In sheetadi i have ' at start and end. so i trim it
i is my column number and it can change parametric
with shtsrg i want to merge sheetname and column number ( it's also my table name )
I used excel string for make table name parametric

I'm trying to use this method :

How to update single cell of excel sheet using oledb connection, | The ASP.NET Forums[^]

But it gives me an error :

Syntax error in UPDATE statement 

您对Syntax错误在哪里或哪个块有问题有什么建议吗？



我尝试过：



如何使用oledb连接更新Excel工作表的单个单元格，| ASP.NET论坛 [ ^ ]

推荐答案

Nope。而且我们不能 - 我们不知道您的UPDATE命令是什么样的，因为您正在构建它（很糟糕，但我会谈到）来自我们无法使用的部分：DataGridView单元格内容和变量。



所以，它取决于你。

在函数的第一行放一个断点，然后运行你的代码通过调试器。然后查看您的代码，并查看您的数据并找出手动应该发生的事情。然后单步执行每一行检查您预期发生的情况正是如此。如果不是，那就是当你遇到问题时，你可以回溯（或者再次运行并仔细观察）以找出原因。


对不起，但我们不能为你做到这一点 - 时间让你学习一种新的（非常非常有用的）技能：调试！



但不要这样做！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

Nope. And we can't - we have no idea what your UPDATE command looks like, because you are building it (badly, but I'll come to that) from parts which are not available to us: A DataGridView cell content, and a variable.

So, its going to be up to you.
Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

But don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

这篇关于更新excel：UPDATE语句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！