ADO insert a record into access DB using delphi

Question

Am learning how to use insert into statements and, with my access database, am trying to insert a single record. The table I'm inserting a new record into has three fields: StockID (AutoN), Description (Text), Cost (Number). I've looked at previous posts but the posted solutions seem to go beyond my basic level of Insert Into...which is what I'm interested in. Anyway, here is my code...

    adoQuery1.Active := true;
adoQuery1.SQL.Clear;
adoQuery1.SQL.Add('INSERT INTO Stock (StockID,Description,Cost) VALUES (4,Cheese,5)');
adoQuery1.open;
adoQuery1.Close;

It compiles fine, but when press a command button to invoke the above, I get the following message:

'ADOQuery1: "Missing SQL property".'

what am I doing wrong?

Thanks, Abelisto. Your last post looks complex indeed...but I did my own little version since your last solution got me up and running. It works so I'm very chuffed. Am now going to focus on DELETE FROM using combobox (for field selection) and user value. Here was my solution I got working... ;)

x:=strtoint(txtStockID.Text);
y:=txtDescription.Text;
z:=strtoCurr(txtCost.Text);

adoQuery1.SQL.Clear;
adoQuery1.SQL.Add('INSERT INTO tblStock (StockID,Description,Cost)');
adoQuery1.SQL.Add('VALUES (:StockID,:Description,:Cost)');   // ':StockID' denotes a parameter
adoQuery1.Parameters.ParamByName('StockID').Value:= x;
adoQuery1.Parameters.ParamByName('Description').Value:= y;
adoQuery1.Parameters.ParamByName('Cost').Value:= z;
adoQuery1.ExecSQL;
adoQuery1.Close;

Call ExecSQL instead of Open. Also do not set Active property - it related to select-like statements, not to DMLs. — Abelisto
– Abelisto, Commented Sep 12, 2015 at 9:46
Thanks Abelisto, removed ExecSQL and Active property... but i'm getting error message "Too Few Parameters. Expected 1" after making the change. Are my values ok? ie. should i be using apostrophes somewhere? — Tiny
– Tiny, Commented Sep 12, 2015 at 9:50
VALUES (4,Cheese,5) -> VALUES (4,''Cheese'',5) (It is not doublequotes but two apostrophes) or VALUES (4,' + QuotedStr('Cheese') + ',5) And do not forget to delete adoQuery1.Active := true; — Abelisto
– Abelisto, Commented Sep 12, 2015 at 9:52
That has sorted it. Many thanks Abelisto. Your instructions were clear and easy for a novice like me. ; ) — Tiny
– Tiny, Commented Sep 12, 2015 at 9:54
PS: adoQuery1.Close; is not necessary in this case. Good luck. — Abelisto
– Abelisto, Commented Sep 12, 2015 at 10:42

Abelisto · Accepted Answer · 2015-09-12 10:26:23Z

Using parameters is more efficient then constant SQL statements. Additional to my comments here is some useful functions which I using frequently to call SQL statements with parameters (Maybe it will be useful for you too):

function TCore.ExecQuery(const ASQL: String; const AParamNames: array of string;
    const AParamValues: array of Variant): Integer;
var
    q: TADOQuery;
    i: Integer;
begin
    if Length(AParamNames) <> Length(AParamValues) then
        raise Exception.Create('There are different number of parameter names and values.');

    q := GetQuery(ASQL) as TADOQuery;
    try
        for i := Low(AParamNames) to High(AParamNames) do
            SetParamValue(q, AParamNames[i], AParamValues[i]);
        q.ExecSQL;
        Result := q.RowsAffected;
    finally
        q.Free;
    end;
end;

function TCore.GetQuery(const ASQL: String): TDataSet;
begin
    Result := TADOQuery.Create(Self);
    (Result as TADOQuery).CommandTimeout := 0;
    (Result as TADOQuery).Connection := Connection;
    (Result as TADOQuery).SQL.Text := ASQL;
end;

procedure TCore.SetParamValue(AQuery: TDataSet; const AName: string; const AValue: Variant);
var
    i: Integer;
    q: TADOQuery;
begin
    q := AQuery as TADOQuery;
    for i := 0 to q.Parameters.Count - 1 do
        if AnsiSameText(AName, q.Parameters[i].Name) then
        begin
            case VarType(AValue) of
                varString, varUString:
                    q.Parameters[i].DataType := ftString;
                varInteger:
                    q.Parameters[i].DataType := ftInteger;
                varInt64:
                    q.Parameters[i].DataType := ftLargeint;
            end;

            q.Parameters[i].Value := AValue;
        end;
end;

And usage example in your case:

Core.ExecQuery(
  'INSERT INTO Stock (StockID, Description, Cost) VALUES (:PStockID, :PDescription, :PCost)', 
  ['PStockID', 'PDescription', 'PCost'],
  [4, 'Cheese', 5]);

Collectives™ on Stack Overflow

ADO insert a record into access DB using delphi

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related