insert data from dbgrid into SQL SERVER database table

[azapovjednik]

Hello,
Is it possible to insert the data from dbgrid component into an SQL SERVER database table.
The data that is in the grid is imported from Excel worksheet.
It may look silly but I need to insert that data into a table.

Any help is appreciated, thank you

[cao]

Since you did not state which components you use to connect to Excel I have used ADO

procedure TForm1.Button1Click(Sender: TObject);
begin

  {Depending on which components you use to connect to SQL 
    Server the syntax might vary but the underlying concept should 
    be the same  

   This example does not not how to connect to the SQL Server 
   but  how to get data from the Excel spreadsheet using TADODataset 
    }

  { ADOQuery1 sql should be INSERT INTO "table_name" ("column1", "column2", }
  { VALUES ("value1", "value2", ...)                                        }

  ADOConnection1.connected := True;

  with ADODAtaSet1 do
  begin
    Active := True;
    DisableControls;
    try
      First;
      { Initialize each parameter with  excel data, execute the query and repeat }
      while not Eof do
      begin
        { Process each record here }

        ADOQuery1.Parameters.ParamByName('Value1').Value :=
          Fields.Fields[1].AsString;
        ADOQuery1.Parameters.ParamByName('Value2').Value :=
          Fields.Fields[2].AsString;
        ADOQuery1.Parameters.ParamByName('Value3').Value :=
          Fields.Fields[3].AsString;
        ...
          ADOQuery1.ExecSQL;

        Next;
      end;
    finally
      EnableControls;
    end;
  end;
end;

[azapovjednik]

Thank you very much for your help!

this is what I have so far:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, ExtCtrls, DBCtrls, Grids, DBGrids, ComCtrls, StdCtrls,
  Buttons,typinfo;

type
  TForm1 = class(TForm)
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    Edit2: TEdit;
    ComboBox1: TComboBox;
    Label3: TLabel;
    ListBox1: TListBox;
    StatusBar1: TStatusBar;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    BitBtn1: TBitBtn;
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    Button2: TButton;
    ADODataSet1: TADODataSet;
    Button3: TButton;
    procedure FormCreate(Sender: TObject);
    procedure DisplayException(Sender:TObject; E: Exception);
    procedure ConnectToExcel;
    procedure FetchData;
    procedure BitBtn1Click(Sender: TObject);
    procedure OpenDialog1CanClose(Sender: TObject; var CanClose: Boolean);
    procedure Button1Click(Sender: TObject);
    procedure GetFieldInfo;
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FetchData;
begin
StatusBar1.SimpleText:='';

ConnectToExcel;
AdoQuery1.Close;
AdoQuery1.SQL.Text := Edit2.Text;
try
  AdoQuery1.Open;
except
  ShowMessage('Unable to read data from Excel, make sure the query '+Edit1.Text+' is meaningful!');
  raise;

end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
AdoConnection1.loginprompt := False;
AdoQuery1.Connection := AdoConnection1;
DataSource1.DataSet := AdoQuery1;
DBGrid1.DataSource := DataSource1;
DBNavigator1.DataSource := DataSource1;

Application.OnException := DisplayException;
end;


procedure TForm1.GetFieldInfo;

var
i : integer;
ft : TFieldType;
sft : string;
fname : string;
begin
Listbox1.Clear;
for i := 0 to AdoQuery1.Fields.Count - 1 do
  begin
    ft :=  AdoQuery1.Fields[i].DataType;
    sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
    fname := AdoQuery1.Fields[i].FieldName;

    ListBox1.Items.Add(Format('%d) NAME: %s TYPE: %s',[1+i, fname, sft]));
  end;
end;

procedure TForm1.OpenDialog1CanClose(Sender: TObject; var CanClose: Boolean);
begin
Edit1.Text:=OpenDialog1.Filename;
end;

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
  begin
    if (adoquery1.RecNo mod 2) = 0 then
  begin
    if dbgrid1.Canvas.Brush.Color = DBGrid1.Color then
      dbgrid1.Canvas.Brush.Color := clInfoBk;
    end;
    dbgrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;

procedure TForm1.DisplayException(Sender: TObject; E: Exception);
begin
StatusBar1.SimpleText := E.Message;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
FetchData;
GetFieldInfo;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
Edit1.Clear;
OpenDialog1.Execute;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
Listbox1.Visible := not ListBox1.visible;
end;

procedure TForm1.ConnectToExcel;
var strConn : widestring;
begin
  strConn := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
                                              'Data Source=' + Edit1.Text + ';' +
                                              'Extended Properties=Excel 8.0;';

  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  try
    AdoConnection1.Open;
    AdoConnection1.GetTableNames(ComboBox1.Items,True);
  except
    Showmessage('Unable to connect to Excel, make sure the workbook ' + Edit1.Text + ' exist!');
    raise;
  end;
end;

end.

when I create this new button and put the code you gave me it says: Missing connection or ConnectionString...

I know, I am dumb with this. I am just starting with Delphi.

Thank you very much!!

[azapovjednik]

procedure TForm1.Button3Click(Sender: TObject);
begin


  ADOConnection1.connected := True;

  with ADOQuery1 do
  begin
    Active := True;
    DisableControls;
    try
      First;
      { Initialize each parameter with  excel data, execute the query and repeat }
      while not Eof do
      begin
        { Process each record here }

       AdoQuery2.Close;
AdoQuery2.SQL.Clear;
AdoQuery2.SQL.Add('INSERT INTO AMIR');
AdoQuery2.SQL.Add('(IDENT,IME,PREZIME,GRAD)');
AdoQuery2.SQL.Add('VALUES');
AdoQuery2.SQL.Add('(:parIDENT,:parIME,:parPREZIME,:parGRAD)');

AdoQuery2.Parameters.ParamByName('parIDENT').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parIME').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parPREZIME').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parGRAD').DataType := ftString;

AdoQuery2.Parameters.ParamByName('parIDENT').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parIME').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parPREZIME').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parGRAD').Direction := pdInput;

AdoQuery2.Parameters.ParamByName('parIDENT').Value:= Fields.Fields[0].AsString;
AdoQuery2.Parameters.ParamByName('parIME').Value:= Fields.Fields[1].AsString;
AdoQuery2.Parameters.ParamByName('parPREZIME').Value:= Fields.Fields[2].AsString;
AdoQuery2.Parameters.ParamByName('parGRAD').Value:= Fields.Fields[3].AsString;
AdoQuery2.ExecSQL;

NEXT;



     //   Next;
      end;
       showmessage('Uspješno');
    finally
      EnableControls;
    end;
  end;

end;

[cao]

You do not need to recreate the query or parameter list with each iteration. See my change below

procedure TForm1.Button3Click(Sender: TObject);
begin


  ADOConnection1.connected := True;

  with ADOQuery1 do
  begin
    Active := True;
    DisableControls;
    try
      First;
      { Initialize each parameter with  excel data, execute the query and repeat }

       AdoQuery2.Close;
AdoQuery2.SQL.Clear;
AdoQuery2.SQL.Add('INSERT INTO AMIR');
AdoQuery2.SQL.Add('(IDENT,IME,PREZIME,GRAD)');
AdoQuery2.SQL.Add('VALUES');
AdoQuery2.SQL.Add('(:parIDENT,:parIME,:parPREZIME,:parGRAD)');

AdoQuery2.Parameters.ParamByName('parIDENT').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parIME').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parPREZIME').DataType := ftString;
AdoQuery2.Parameters.ParamByName('parGRAD').DataType := ftString;

AdoQuery2.Parameters.ParamByName('parIDENT').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parIME').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parPREZIME').Direction := pdInput;
AdoQuery2.Parameters.ParamByName('parGRAD').Direction := pdInput;

{ You do not need to recreate the query or parameter list with each iteration. Thats the benefit of paramaters the query needs to be prepared once only on the server }

      while not Eof do
      begin
        { Process each record here }

AdoQuery2.Parameters.ParamByName('parIDENT').Value:= Fields.Fields[0].AsString;
AdoQuery2.Parameters.ParamByName('parIME').Value:= Fields.Fields[1].AsString;
AdoQuery2.Parameters.ParamByName('parPREZIME').Value:= Fields.Fields[2].AsString;
AdoQuery2.Parameters.ParamByName('parGRAD').Value:= Fields.Fields[3].AsString;
AdoQuery2.ExecSQL;

NEXT;



     //   Next;
      end;
       showmessage('Uspješno');
    finally
      EnableControls;
    end;
  end;

end;