Connecting Delphi with SDF (Sql Compact) Database using ADO

This Delphi Tutorial will show how to connecting Delphi with SQL Compact Database using ADO.

To connecting ADO with SDF file, first we must install Sql Compact on windows. We can download it here for Sql Compact 3.5 SP2.

After we install it, go to registry editor. Windows -> Run -> type "regedit".

find this key : HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC}

(this was in the Windows 7 PC. In the other one it was HKEY_CLASSES_ROOT\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC} )

Then, I add a new key into in: "OLE DB Provider". And finally, set the default value of this key to "Microsoft SQL Server Compact OLE DB Provider".

Now, ADO will shows this provider on the list and we can select it.

Open our delphi project, add These components :
- ADOConnection
- ADODataSet
- ClientDataSet
- DataSetProvider
- DataSource
- Table Grid (i'm using cxGrid)

fill this connection string on ADOConnection :
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=D:\BBlocksStock\test.sdf;Mode=ReadWrite;SSCE:Max Buffer Size=4096;SSCE:Database Password="";SSCE:Encrypt Database=False;SSCE:Default Lock Escalation=100;SSCE:Temp File Directory="";SSCE:Default Lock Timeout=5000;SSCE:AutoShrink Threshold=60;SSCE:Flush Interval=10;SSCE:Test Callback Pointer=0;SSCE:Max Database Size=256;SSCE:Temp File Max Size=128;SSCE:Encryption Mode=0;SSCE:Case Sensitive=False;

change Data Source value with your database path.

set ADODataSet Connection with ADOConnection. Fill the ADODataSet CommandText with SQL Select Command. ex : SELECT * FROM TEST

set DataSetProvider DataSet with ADODataSet.
set ClientDataSet Provider Name with DataSetProvider
set DataSource DataSet with ClientDataSet.

now on Form onCreate event, write this code :

procedure TForm1.FormCreate(Sender: TObject);beginADODataSet1.Active:=true;end;

if it run correctly, data from database will show on table grid component.

Why we use ADODataSet?? I try use ADOTable Component, and when i try to select TableName properties, we will get error "Multiple-step OLE DB operation generated errors.Check each OLE DB status value, if available. No work was done."

We use ClientDataSet for database transaction. i try to insert new data using ADODataSet Component, i always get error.

so we use ClientDataSet Component for database data transactions.
This is example code for insert new data :

procedure TForm1.Button1Click(Sender: TObject);
begin
ClientDataSet1.Append;
ClientDataSet1ID.AsInteger := 13;
ClientDataSet1.ApplyUpdates(-1);
ADODataSet1.Active:=false;
ADODataSet1.Active:=true;
ClientDataSet1.Active:=false;
ClientDataSet1.Active:=true;
end;

read more

Get Column Value from CxGrid Multiselect (DevExpress)

For this Delphi Tutorial, i will write the code how to get value from a column for the data that we select. In this Case, we can select data more than one, and then get the values from a column from data that we select.

first step, make an application with button, cxgrid component, and database Components.
this is for example :

Don't forget to enable Multiselect option on cxGridDBTableView OptionSelection :

Then, write this code on Button OnClick event :

procedure TForm1.Button1Click(Sender: TObject);var i : integer;    s : String;

begin

for i:=0 to cxGrid1DBTableView1.Controller.SelectedRowCount - 1 do   

begin

showmessage(cxGrid1DBTableView1.Controller.SelectedRows[i].Values[0]);

end;

end;

Try to run the application, click the button, and see the result.

If this code run perfectly, the application will show value from column for data that we select.

SelectedRows[i] mean index for data that we select.

Values[0] is index for column. For this code, we will get value from Column ID, since it index is 0. You can change the index base on column you want to get the value.

read more

Learning Delphi for First Time

Like other Programming Language, we will learn how to make 'Hello World!' project with delphi.

for this Delphi Tutorial project, i will use 3 methods :

1. Using Edit Text and Button

I assume we already open Delphi application and we see a form for us to build the project.

First Step , add Edit Text and Button component. You can find Edit Text and Button Component at component tool bar. Here is the picture :

To add the component, simply click the component, and then click again on the Form. Set the position of the component. You can set the component like this or you can set it with your own :

After we set the component position, now we add command on Button component. This command will make Edit Text component write 'Hello World' when we click the button component.

To add that command on Button component, click Button component, and then at Object Inspector (usually it place at bottom left Delphi application). Click on 'Event' tab, and double click OnClick. 

After we click Event OnClick, now we put our command here :

 the command should write like this :

procedure TForm1.FormClick(Sender: TObject);

begin

Edit1.Text := 'Hello World!'

end;

Now, run our Project. 

Click the Button, and see the result..

2. Using Button and ShowMessage command.
Add Button component to Form. And then, on Button OnClick Event, add this command :

procedure TForm1.Button1Click(Sender: TObject);

begin

ShowMessage('Hello World!');

end;

now Run the project, click the Button, and see the result..

3. Using Button and Label Component.

Now try to add Button and Label Component to the Form.

set position for Button and Label on Form.

Now, on Button OnClick event, write the code like this :

procedure TForm1.Button1Click(Sender: TObject);

begin

Label1.Caption := 'Hello World!';

end;

Run the project, and try to click the Button. See the result..

read more