Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

How to insert records to database

Hi, all Experts

I am using ADO to connect to MS Access database. I created the following codes to insert a record:

procedure TForm1.Button1Click(Sender: TObject);
begin
    with dm.ADOQuery1 do
    begin
        Active := false;
        SQL.Clear;
        SQL.Add('insert into category (description) values (''category1'')');
        Active := true;
    end;
end;

But when I run the program and click on button1, it shows some errors:
1. The operation requested by the application is not supported by the provider;
2. CommandText does not return a result set.

Please help me to fix it.

Thanks
0
jl2001
Asked:
jl2001
1 Solution
 
DragonSlayerCommented:
it's interesting to note that you need to enclose strings in a single quote ' instead of a double quote " when using TADOQuery components with Access databases (dunno, never tried ADO with other databases, I usually use native access for other databases)

so let's say you want to insert something that the user has typed in an edit box, try this:

with ADOQuery1 do
begin
  Close;
  SQL.Text := Format('INSERT INTO category (description) VALUES (%s)', [QuotedStr(Edit1.Text)]);
  Open;
end;

using SQL.Text eliminates the need to call Clear and Add.



Hope that helped!
DragonSlayer.
0
 
kretzschmarCommented:
do not open, do execute your query
0
 
CrazyOneCommented:
Here is what kretzschmar is referring to, change the Active := True to ExecSQL

procedure TForm1.Button1Click(Sender: TObject);
begin
   with dm.ADOQuery1 do
   begin
       Active := false;
       SQL.Clear;
       SQL.Add('insert into category (description) values (''category1'')');
       ExecSQL;
   end;
end;
0
 
JaymolCommented:
Actually, DragonSlayers comment will fix the first error message and Kretzschmar's comment will fix the second error.

John.
0
 
jl2001Author Commented:
Thanks all. ExecSQL is the best way to fix my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now