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; 
