无法将Delphi ADOQuery更新或插入Excel工作表

I am currently working on a program which function is to read data from a Excel Spreadsheet, where the user should be able to edit the amount of stock and generate a separate report of only stock used, this I have done. My problem occurs when trying to update the original spreadsheet show the stock gets less/more as the user adds/removes on the program.

我目前正在开发一个程序，其功能是从Excel电子表格中读取数据，用户应该能够编辑库存量并生成仅使用库存的单独报告，我已经这样做了。尝试更新原始电子表格时出现问题，显示当用户在程序上添加/删除时，股票变得越来越少。

I have been able to show the change in stock in the DBGrid but no changes are made on the actual spreadsheet thus whenever the program is restarted it will show unchanged numbers.(This was done with DBEdits)

我已经能够在DBGrid中显示库存变化，但是没有对实际电子表格进行任何更改，因此每当程序重新启动时，它将显示未更改的数字。（这是通过DBEdits完成的）

**I have created a smaller version of this code to make my problem hopefully more clear and easier to read.Making use of query's to try and update the Excel Spreadsheet, The first row in the spreadsheet has a value of 17 which the program tries to change to 5.Whenever I run this code I get "Syntax error in Update Statement", I am fairly certain this is not the case. I have played around with the code adding things such as

**我创建了一个较小版本的代码，以使我的问题更清晰，更容易阅读。使用查询来尝试更新Excel电子表格，电子表格中的第一行的值为17，程序尝试更改为5.每当我运行此代码时，我得到“更新语句中的语法错误”，我相当确定不是这种情况。我已经玩过添加诸如的代码

ADOQuery.open ADOQuery.Enabled := false ADOQuery.Enabled := true

ADOQuery.open ADOQuery.Enabled：= false ADOQuery.Enabled：= true

etc. each giving me different errors.**

每个给我不同的错误。**

Code as follows:

代码如下：

procedure TForm1.FormCreate(Sender: TObject);
begin

  //Building Connection string as well as recieving filename of excel document
  OpenDialog1.Execute;
  ADOQuery1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=' + OpenDialog1.FileName + ';' + 'Extended Properties="Excel 8.0;IMEX=2"';

  //Working SQL statement to display records in DBGrid
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT * FROM [Sheet1$];');
  ADOQuery1.Active := true;

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
   //Broken code, purpose is to replace the first row value with a new value
   ADOQuery1.SQL.Clear;

   ADOQuery1.SQL.Add('UPDATE [Sheet1$]');
   ADOQuery1.SQL.Add('SET Value = 5');
   ADOQuery1.SQL.Add('WHERE Value = 17;');

   ADOQuery1.ExecSQL;


end;

end.

The sample Spreadsheet: Sample Spreadsheet used in code above

上面代码中使用的示例电子表格：示例电子表格

I have research on this problem for the past two days but somehow an answer seems to elude me constantly, asking is always a last ditch effort. If you do not know a solution any guidance will be appreciated and even suggestions on other programming languages/IDE's which will give me more fruitful results. I will even settle for a Excel scripting tutorial if you are able to link one to me which could be relevant to my current program

我在过去的两天里对这个问题进行了研究，但不知何故，答案似乎总是让我不知所措，总是要求最后的努力。如果你不知道解决方案，任何指导都会受到赞赏，甚至对其他编程语言/ IDE的建议也会给我带来更丰硕的成果。如果你能够将一个链接到我，这可能与我当前的程序相关，我甚至会选择Excel脚本教程

P.S Sorry for such a long post, fairly new to this site. All help is very much appreciated.

P.S对于这么长的帖子很抱歉，这个网站还不太新。非常感谢所有帮助。

1 个解决方案

#1

I can reproduce your problem and get

我可以重现你的问题并获得

Syntax error in UPDATE statement.

UPDATE语句中的语法错误。

Updated answer I have not fully investigated this yet, but I think your problem is arising because of your choice of Value as the column name. I think that this name maybe conflicts with how the UPDATE statement is interpreted by the ADO layer. I think that because if I use this Sql statement

更新的答案我尚未对此进行全面调查，但我认为您的问题是由于您选择Value作为列名而引起的。我认为这个名称可能与ADO层如何解释UPDATE语句有冲突。我认为，因为如果我使用这个Sql语句

'Update [Sheet1$] Set [Value] = 88 where [Value] = 5'

'更新[Sheet1 $]设置[值] = 88，其中[值] = 5'

, the query executes correctly and updates the value of the cell containing 5 correctly.

，查询正确执行并正确更新包含5的单元格的值。

I'm going to leave my original answer in place below in case it helps others.

我将在下面留下我原来的答案，以防它帮助别人。

Original answer:

原始答案：

To check that it wasn't a problem with the Value column's FieldName, I added this to TForm1.Create:

要检查Value列的FieldName是不是有问题，我将其添加到TForm1.Create：

  Caption := AdoQuery1.Fields[0].FieldName;

and that confirmed that FieldName is indeed Value.

并确认FieldName确实是Value。

So then I added a TDBNavigator and TDBEdit to the form and found that I could edit the Value value in the TDBEdit without any problem.

然后我在表单中添加了一个TDBNavigator和TDBEdit，发现我可以毫不费力地编辑TDBEdit中的Value值。

That gave me an idea:

这给了我一个想法：

procedure TForm1.Button2Click(Sender: TObject);
begin
  if AdoQuery1.Locate('Value', '5', []) then begin
    AdoQuery1.Edit;
    AdoQuery1.FieldByName('Value').AsString := '99';
    AdoQuery1.Post;
  end;
end;

and that works fine. Obviously, that's not exactly what you want, because it doesn't fully replicate what an UPDATE statement would do if there were multiple rows matching the WHERE clause, but there are various ways you could achieve that, e.g. using the AdoQuery's Seek method.

这很好。显然，这并不完全是你想要的，因为它没有完全复制UPDATE语句如果有多个行匹配WHERE子句会做什么，但有多种方法可以实现这一点，例如：使用AdoQuery的Seek方法。

~~If I make any progress in getting UPDATE to work, I'll post an update to this answer.~~

如果我在UPDATE工作方面取得任何进展，我会发布此答案的更新。

#1