Search code examples
delphidatasnap

Datasnap - insert record with parameter

On DatasnapServer I have : TSQLConnection connected to my database. SQLDataset1 (CommandType=ctQuery) that fetches data from my table (lets call it "RESORTS". DataSetProvider1 is connected to my SQLDataset1. DataSetProvider1 is set to AllowCommandText.

This works OK. Server starts without a problem. Data is obtained.

On the Client side I have : SQLConnection1 which is connected OK.Driver is Datasnap. DSProviderConnection1 is linked to my SQLConnection1. Both connected without problem. Then I have DataSource1 which is connected to a ClientDataSet1. ClientDataSet1 is connected to my DataSetProvider1. Setting it active retrieves the data from the server. All displayed right in the grid.

On the Client form I have a Edit1 and a Button1. I try and run a query using the ClientDataset1

procedure TForm2.Button1Click(Sender: TObject);
begin
ClientDataSet1.Close;
ClientDataSet1.CommandText := ' INSERT INTO RESORTS (RES_NAME) VALUES (:RN)';
ClientDataSet1.FieldByName('RN').AsString := Edit1.Text;
ClientDataSet1.Execute;
ClientDataSet1.Open;
end;

I get : ClientDataSet1: Field 'RN' not found.

So, I am wondering what is going on? Why cant I insert data with parameter? If I substitute the parameter with : ClientDataSet1.CommandText := ' INSERT INTO RESORTS (RES_NAME) VALUES ("TRY")'; I get :Remote error: SQLDataSet1: Cursor not returned from Query. However, the data does get inserted.

What am I doing wrong here ?

Solution

  • (Rewritten based on new information provided by the poster in comments.)

    Your entire approach is wrong. :-) You don't use parameters, SQL or CommandText. The TClientDataSet.CommandText documentation clearly says:

    CommandText specifies what data the client dataset wants to receive from its (internal or external) provider. It is either:

    • An SQL statement (query) for the database server to execute.
    • The name of a table or stored procedure

    An SQL statement (query) means only a SELECT is acceptable SQL. An INSERT is not a query, so it cannot be used in a CommandText to insert data.

    To insert data in a TClientDataSet, you simply Insert or Append, and then use FieldByName to set the value, and then call the Post method:

    ClientDataSet1.Insert;
ClientDataSet1.FieldByName('RES_NAME').AsString := Edit1.Text;
ClientDataSet1.Post;

    To edit, you simply use Edit instead of Insert or Append; the rest stays exactly the same.

    ClientDataSet1.Edit;
ClientDataSet1.FieldByName('RES_NAME').AsString := Edit1.Text;
ClientDataSet1.Post;

    When you're ready to actually update the server data from the changes made in the TClientDataSet, call it's ApplyUpdates:

    ClientDataSet1.ApplyUpdates(0);