Topic: Microsoft SQL Database (MSSQL) with script

Hello "My Visual Database" fans and users. I just wanted to say that I love this application because its very easy to use and learn. I love the fact you can use MySQL and SQLlite, but somethings we need to use Microsoft SQL database to retrieve data. Using MVD to this task will be so much easy, so, I challenge my self for several days to find a way to connect to MSSQL and voila.... Finally  I have found a way to this.  I have learn a lot about MVD using this forum that I wanted to share it with you. Thanks and enjoy

procedure LoadTickets_FROM_SQL (Sender: string; Cancel: string);
var
  Conn, rs : Variant;
  db_settings, FirstName,LastName,Phone: String;
  i: int;
var NxTextColumn: TNxTextColumn;
begin
   {***  create database objects ****  }
   Conn :=createoleobject('ADODB.Connection') ;
   rs := createoleobject('ADODB.recordset');

   {** open database connection ***  }
  db_settings:='Provider=SQLOLEDB;Server=myserver_name;Database=DatabaseName;UID=UserName;PWD=MyPassword';    //connection to database
   Conn.Open(db_settings);    //connection to database

   { query to get data}
   rs.Open  ('SELECT *  FROM Application where  TicketNumber  like ''%'+frm_main.fld_ticketnumber.text+'%''  order by DateTime asc;', Conn);


    //create table information
    frm_main.TableGrid1.Columns.Clear;
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    
    //header name
    frm_main.TableGrid1.Columns[0].Header.Caption := 'First Name';    
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Last Name';      
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Phone Number';  
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Date Time';  


   //initialized i
   i:=0;
   //while loop to get data from db
   while not rs.Eof do
    begin

        Application.ProcessMessages;

         //retrieve data and stored them in variables
         FirstName:=trim(rs.Fields['FirstName']);
         LastName:=trim(rs.Fields['LastName']);
         Phone:=trim(rs.Fields['Phone']);
         CDateTime:=trim(rs.Fields['DateTime']);

        //generate the data
        Grid.AddRow(1);
        Grid.Cells[0,i]:=FirstName;
        Grid.Cells[1,i]:=LastName;
        Grid.Cells[2,i]:=Phone;
        Grid.Cells[3,i]:=CDateTime;

        i:=i+1; //increment i
        rs.movenext ;   //next record
    end;


     //close connection
     rs.Close;
     Conn.Close;

end;              

Re: Microsoft SQL Database (MSSQL) with script

Thank you very much for this example!

Dmitry.

Re: Microsoft SQL Database (MSSQL) with script

need help on sql server topic

Re: Microsoft SQL Database (MSSQL) with script

hello ... i tried this script but not working...
i am using sql server 2005 standard.... can you help..

procedure LoadTickets_FROM_SQL (Sender: string; Cancel: string);
var
  Conn, rs : Variant;
  db_settings, Volume,Value,Price: String;
  i: int;
var NxTextColumn: TNxTextColumn;
begin
   {***  create database objects ****  }
   Conn :=createoleobject('ADODB.Connection') ; //
   rs := createoleobject('ADODB.recordset');

   {** open database connection ***  }
  db_settings:='Provider=SQLOLEDB;Server=localhost;database=ENABLERDB;UID="sa";PWD=""';    //connection to database
   Conn.Open(db_settings);    //connection to database

   { query to get data}
   rs.Open  ('SELECT Delivery_Volume,Delivery_Value,Del_Sell_Price FROM Hose_Delivery ORDER BY Delivery_ID DESC', Conn);


    //create table information
    frm_main.TableGrid1.Columns.Clear;
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);


    //header name
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Delivery_Volume';
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Delivery_Value';
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Del_Sell_Price';




   //initialized i
   i:=0;
   //while loop to get data from db
   while not rs.Eof do
    begin

        Application.ProcessMessages;

         //retrieve data and stored them in variables
         Volume:=trim(rs.Fields['Delivery_Volume']);
         Value:=trim(rs.Fields['Delivery_Value']);
         Price:=trim(rs.Fields['Del_Sell_Price']);

         //CDateTime:=trim(rs.Fields['DateTime']);

        //generate the data
        frm_main.TableGrid1.AddRow(1);
        frm_main.TableGrid1.Cells[0,i]:=Volume;
        frm_main.TableGrid1.Cells[1,i]:=Value;
        frm_main.TableGrid1.Cells[2,i]:=Price;

        //Grid.Cells[3,i]:=CDateTime;

        i:=i+1; //increment i
        rs.movenext ;   //next record
    end;


     //close connection
     rs.Close;
     Conn.Close;

end;

Re: Microsoft SQL Database (MSSQL) with script

try replace this code

    //create table information
    frm_main.TableGrid1.Columns.Clear;
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);

to

    //create table information
    try frm_main.TableGrid1.Columns.Clear; except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
Dmitry.

Re: Microsoft SQL Database (MSSQL) with script

oh sorry.. now its working.. thanks ...
but datagrid background color is black.. i used script to make it white but first row is still black..

Re: Microsoft SQL Database (MSSQL) with script

procedure LoadTickets_FROM_SQL (Sender: string; Cancel: string);
var
  Conn, rs : Variant;
  db_settings, Volume,Value,Price: String;
  i: int;
var NxTextColumn: TNxTextColumn;
begin
   {***  create database objects ****  }
   Conn :=createoleobject('ADODB.Connection') ; //
   rs := createoleobject('ADODB.recordset');
   {** open database connection ***  }
  db_settings:='Provider=SQLOLEDB;Server=localhost;database=ENABLERDB;UID="sa";PWD=""';    //connection to database
   Conn.Open(db_settings);    //connection to database
   { query to get data}
   rs.Open  ('SELECT Delivery_Volume,Delivery_Value,Del_Sell_Price FROM Hose_Delivery ORDER BY Delivery_ID DESC', Conn);

    //create table information
    frm_main.TableGrid1.Columns.Clear;
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);
    frm_main.TableGrid1.Columns.Add(TNxTextColumn);

    //header name
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Delivery_Volume';
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Delivery_Value';
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Del_Sell_Price';



   //initialized i
   i:=0;
   //while loop to get data from db
   while not rs.Eof do
    begin
        Application.ProcessMessages;
         //retrieve data and stored them in variables
         Volume:=trim(rs.Fields['Delivery_Volume']);
         Value:=trim(rs.Fields['Delivery_Value']);
         Price:=trim(rs.Fields['Del_Sell_Price']);
         //CDateTime:=trim(rs.Fields['DateTime']);
        //generate the data
        frm_main.TableGrid1.AddRow(1);
        frm_main.TableGrid1.Cells[0,i]:=Volume;
        frm_main.TableGrid1.Cells[1,i]:=Value;
        frm_main.TableGrid1.Cells[2,i]:=Price;
        //Grid.Cells[3,i]:=CDateTime;
        i:=i+1; //increment i
        rs.movenext ;   //next record
    end;

     //close connection
     rs.Close;
     Conn.Close;
end;

hello dmitry... how can we print automaticay when recent data update in grid...