Can't get Delphi ADOQuery to update or insert into Excel Worksheet

Question

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.

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)

**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

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

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

Answer 1

I can reproduce your problem and get

Syntax error in UPDATE statement.

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

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

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

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:

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

and that confirmed that FieldName is indeed 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.

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.

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