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

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

Please upload An example...

JUST LEARNING, O GOD HELP ME.

Re: Microsoft SQL Database (MSSQL) with script

Hello... how can i genrate report of retrieved data from sqlserver database...

Re: Microsoft SQL Database (MSSQL) with script

blackpearl8534 wrote:

Hello friends....
How to get sql retrieved data in fast report and how to generate report...
Urgent response required

I can suggest an idea: first you need to get the data into the recordset as described in the first post. Then copy the received data to the SQLite database. Well, now you can create a report )))

Библиотека https://mvdlibrary.blogspot.com
Визуальное программирование https://createmyvisualdatabaseapp.blogspot.com

Re: Microsoft SQL Database (MSSQL) with script

Hello livexox... are you available on this platform