Delphi SQL how to add data into a specific record in a MS Access Database table

Question

I am busy making an important IT Project for my class and I am struggling to succeed trough this one problem.

The program needs to Store Images to the table of the database, but I am trying to use a different technique. When the user uses the 'loadfromfile' control, the directory of the File Name is used instead of saving the Picture itself into the database. So I have a field in the table called : "Directory" that only reads a string and I am storing the File name path into that field, like 'C:/Pictures/Picture.JPG' etc.

I use sName by an edit.text to let the user find the specific record to save the path to the field. And sDirectory for the path name itself.

I use a MS Access database table. In the table fields : (ID, Real Name, Surname,Pass,Age,Directory,Medium,Location,Artwork Name)

Once the user chooses the Picture from the Loadfromfile dialogue, the user needs to save the path into the specific record that the user wants to save. I tried using this code but it keeps giving me a Syntax error :

Syntax error(missing operator) in query expression 'Username = 'the Name' Insert (Directory) Values(?)'

procedure TfrmPost.btnBrowseClick(Sender: TObject);
begin

opdImage.Execute;
sDirectory := opdImage.FileName;
MyPic.Picture.LoadFromFile(sDirectory);
bitBtnUpload.Enabled := true;

end;

procedure TfrmPost.bitBtnUploadClick(Sender: TObject);

begin
 sName := lblName.Caption;
 sDirectory := QoutedStr(opdImage.Filename);

  with dmArt do
    begin
      qryArt.Close;
      qryArt.SQL.Clear;
      qryArt.SQL.Text := 'SELECT * FROM tbArt WHERE Username = '''+sName + ''' '; 
       qryArt.Open;
       qryArt.SQL.Add('Insert');
       qryArt.SQL.Add('(Directory)');
       qryArt.SQL.Add('Values (:Directory)');
       qryArt.Parameters.ParamByName('Directory').Value := sDirectory;
       qryArt.ExecSQL;
       qryArt.Close;

        end;

        end;

Freddie Bell · Accepted Answer · 2018-10-05 15:13:39Z

2

Insert INTO tbArt is what you're looking for to correct the error.

That's if you want to insert the sDirectory value directly into the database.

It looks like you think that the insert will happen into qryArt. It won't do that. You want to

qryArt.Edit; 
qryArt.FieldByName('directory').AsString := sDirectory; 
qryArt.Post;

if you want to edit the value of the directory field in the current record. That also might (not) edit the value in the database. It all depends on what kind of component qryArt is.

answered Oct 5, 2018 at 15:13

Freddie Bell

2,3261 gold badge28 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

B.Anderson Over a year ago

Thanks! This worked very well ! I am new to using SQL code so I needed this help.

Ken White · Accepted Answer · 2018-10-06 12:27:18Z

Stop concatenating SQL. It leaves you open to SQL injection attacks, and makes things very difficult when trying to quote strings correctly or get date formats right. Use SQL parameters instead, and use an UPDATE statement with a WHERE clause. You can also easily test for success or failure.

procedure TfrmPost.bitBtnUploadClick(Sender: TObject);
begin
  with dmArt do
  begin
    qryArt.Close;
    qryArt.SQL.Text := 'UPDATE tbArt SET Directory = :Directory'#13 +
                       'WHERE UserName := :UserName';
    qryArt.Parameters.ParamByName('UserName').AsString := lblName.Caption;
    qryArt.Parameters.ParamByName('Directory').AsString := opdImage.FileName;
    if qryArt.ExecSQL <> 1 then
      raise Exception.Create('UPDATE failed. Is username correct?');
  end;
end;

As an aside, your code to get the filename isn't very well designed. It does not properly handle the user cancelling the file selection dialog. Try something like this instead:

procedure TfrmPost.btnBrowseClick(Sender: TObject);
begin
  if opdImage.Execute then
  begin
    MyPic.Picture.LoadFromFile(opdImage.FileName);
    bitBtnUpload.Enabled := True;
  end;
end;

Collectives™ on Stack Overflow

Delphi SQL how to add data into a specific record in a MS Access Database table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related