Delphi Insert data from StringGrid to a Database Table

Question

Iam trying to insert data from a StringGrid to the Oracle DB table for that i tried like below.

function TfrmMapping.LoadtoTable: Boolean;
var
  I, J: Integer;
  lQuery, s: string;
  lData: TArray<string>;
begin

  for I := 0 to vTableColumns.count - 1 do
  begin
    if I <> vTableColumns.count - 1 then
    begin
      s := s + vTableColumns[I] + ',';
    end
    else
    begin
      s := s + vTableColumns[I];
    end;
  end;

  for I := 1 to StrGrdLoadCSVData.RowCount - 1 do
  begin
    vSortedGrid.Add(StrGrdLoadCSVData.Rows[I].CommaText);
  end;

  for I := 0 to vSortedGrid.count - 1 do
  begin
    lQuery := 'Insert into ' + cmbBXDBTables.Text + '(' + s + ') values(' +
      vSortedGrid[I] + ')';
    DataModSample.FDQuery1.SQL.Clear;
    DataModSample.FDQuery1.SQL.Add(lQuery);
    DataModSample.FDQuery1.ExecSQL;
  end;
  Result := True;
end;

In the code , Iam adding all the data of StringGrid(StrGrdLoadCSVData) to a StringList(vSortedGrid), and now iam trying to loop through the StringList to add each row to the DB, But Iam not able to insert because my is taking the values like this

Insert into abc(sno,Name)values(1,welcome);

It is because there are no quotes to welcome it is giving an error.

it is error like this : [FireDAC][Phys][Ora]ORA-00984:column not allowed here

How i can modify my code to insert the data successfully to Db.

EDIT

My table Structure is :

Name            Type
 ---------  ------------
 SNO          NUMBER(38)
 NAME         VARCHAR2(15)

my desired result in the table should be like this :

       SNO NAME
---------- ----------
         1 Hello
         2 Welcome

The values in the table comming from the string List

`'Insert into abc(SNO,NAME) values(''1,welcome'')'` if i use Quotedstr, it is getting values like this and giving an error like `[FireDAC][Phys][Ora]ORA-00947:not enough values` if there is no Quotedstring it is error like this : `[FireDAC][Phys][Ora]ORA-00984:column not allowed here` — userhi, Jan 19 '17 at 10:01
It is not Column, The table has only two columns. `SNO` and `NAME` where `SNO` is of `number / integer type` and `NAME` is of `VARCHAR` — userhi, Jan 19 '17 at 10:34
When inserting to a StringList, if the value is a string type, then insert it to a StringList with quotes associated with the value. — Alec, Jan 19 '17 at 11:46
@Fero68, Is there any possibility to avoid the StringList , and insert from the StringGrid to DB Table. — userhi, Jan 19 '17 at 12:30
Yes you can, by getting the value straight from the StringGrid then you would have to use TStringGrid.Row and applying your cell properties, then using that value and inserting straight to the DB — Alec, Jan 19 '17 at 12:40

Ilyes · Answer 1 · 2017-01-19T14:12:02.230

It is because there are no quotes to welcome it is giving an error.

So from what you say :

  for I := 0 to vSortedGrid.count - 1 do
  begin
    lQuery := 'Insert into ' + cmbBXDBTables.Text + '(' + s + ') values('+IntToStr(i+1)+',' +
      QuotedStr(vSortedGrid[I]) + ')';
    DataModSample.FDQuery1.SQL.Clear;
    DataModSample.FDQuery1.SQL.Add(lQuery);
    DataModSample.FDQuery1.ExecSQL;
  end;
  Result := True;
end;

Note: Better to use parameters.

Update:

Another option to insert from TStringGrid using TFDTable:

procedure TForm1.Button2Click(Sender: TObject);
Var I : Integer;
begin

for i := 1 to StringGrid1.RowCount-1 do

    begin
     try
      FDTable1.Append;
      FDTable1SNO.Value := StrToInt( StringGrid1.Cells[0,i] );
      FDTable1SName.Value := StringGrid1.Cells[1,i];
      FDTable1.Post;
      except on E: Exception do
        begin
         MessageDlg(E.Message,mtError,[mbOK],0);
         MessageBeep(MB_ICONERROR);
       end;
    end;
end;

Another option to insert from the TStringGrid using TFDQuery (avoid SQL Injection):

procedure TForm1.Button1Click(Sender: TObject);
Var I : Integer;   TableName : String;
begin

TableName := 'Table1';

for i := 1 to StringGrid1.RowCount-1 do

    begin
     try
      FDQuery1.SQL.Text := 'Insert Into '+TableName+' Values(:Val1 , :Val2)' ;
      FDQuery1.Params.ParamByName('Val1').Value := StrToInt( StringGrid1.Cells[0,i] );
      FDQuery1.Params.ParamByName('Val2').Value := StringGrid1.Cells[1,i];
      FDQuery1.ExecSQL;
     except on E: Exception do
      begin
       MessageDlg(E.Message,mtError,[mbOK],0);
       MessageBeep(MB_ICONERROR);
      end;
    end;

You can also Create parameters as you need at Runtime for exemple:

FDQuery1.Params.CreateParam(ftString,'ParamName',ptInput) ;

Also you can use GetTableNames() to get all tables in the Database.

`'Insert into abc(SNO,NAME) values(1,''1,welcome'')'` ` SNO NAME ---------- ----------- 1 1,Welcome ` It is inserting like above, but i want like this SNO NAME ---------- ----------- 1 welcome — userhi, Jan 19 '17 at 10:14
I don't understand what you mean by this , So Please edit your question and add your table structure and the resault expected. — Ilyes, Jan 19 '17 at 10:17
`Note: Better to use parameters.` How can i use parameters , here The Table which I am inserting is not same all time , the table will change depending on user selection and the no of columns also will change. — userhi, Jan 19 '17 at 10:39
@userhi if there are many tables, use one statement and one parameter set per table, it is more robust, supports more field types, and removes SQL injection risks — mjn42, Jan 19 '17 at 12:23
@mjn42 , How it can be done, Can you guide me in achieveing this? — userhi, Jan 19 '17 at 12:28

score 2 · Accepted Answer · answered Jan 20 '17 at 06:41

I Modified the Code like below

function TfrmMapping.LoadtoTable: Boolean;
var
  I, J: Integer;
  lQuery, s, lcolvalues: string;
begin

  for I := 0 to vTableColumns.count - 1 do
  begin
    if I <> vTableColumns.count - 1 then
    begin
      s := s + vTableColumns[I] + ',';
    end
    else
    begin
      s := s + vTableColumns[I];
    end;
  end;

  for I := 1 to StrGrdLoadCSVData.RowCount - 1 do
  begin
    for J := 0 to vTableColumns.count - 1 do
    begin
      if J <> vTableColumns.count - 1 then
      begin
        lcolvalues := lcolvalues +
          QuotedStr(StrGrdLoadCSVData.Cells[J, I]) + ',';
      end
      else
      begin
        lcolvalues := lcolvalues + QuotedStr(StrGrdLoadCSVData.Cells[J, I]);
      end;
    end;
    lQuery := 'Insert into ' + cmbBXDBTables.Text + '(' + s + ') values (' +
      lcolvalues + ')';
    DataModSample.FDQuery1.SQL.Clear;
    DataModSample.FDQuery1.SQL.Add(lQuery);
    DataModSample.FDQuery1.ExecSQL;

    lcolvalues := '';
  end;
  Result := True;
end;

This is inserting values to Table from the string grid, I didn't use Parameter passing as of now. I have to try that also for ensuring more security.

Thank You @Sami , Buy using your concept of FDQuery I have got this idea...

Good luck man , all what I can say is , I come to SO for help other as I can and learn from them , that's the life .Together Everyone Achieves more **(TEAM)**. — Ilyes, Jan 20 '17 at 08:04

Delphi Insert data from StringGrid to a Database Table

2 Answers2