Defining the User Interface

We'll build our data browsing form in three steps. First step is to define the user interface for the form. Next, the data access components are added and configured. In the third and final step, the data-aware controls are added. Before you start, close any open projects. Then use the following steps Select File New Application. This creates a new project containing an empty form, a unit, and a project file. Add one one PageControl on the form. The PageControl can be found on the Win32 tab on...

ADO Connection

The ADOConnection component is used to establish a connection with an ADO data store. Although each ADO dataset component can directly connect to a database, we will typically want to use the ADOConnection component since the component provides methods and properties for activating the connection, accessing the ADO data store directly and for working with transactions. In order to connect to a specific database, we use the ConnectionString property. Now, when we know the theory it's time to see...

TField DefData Type

Obviously, data type that represents a string in Access is TEXT. In Paradox it's STRING. In order to port Paradox tables to Access we'll have to know what data types are available and what are their names. When working with the BDE and Paradox tables, the TFieldDef.DataType determines the type of a physical field in a (dataset) table. To successfully migrate Paradox tables to Access you need to have a function that transforms a Paradox field type to an Access type. The next function checks the...

Lets chart Prepare

Our task will be to create a simple Delphi form with a chart filled with values from a database query. To follow along, create a Delphi form as follows 1. Start a a new Delphi Application - one blank form is created by default. 2. Place the next set of components on the form ADOConnection, ADOQuery, DataSource, DBGrid and a DBChart. 3. Use the Object Inspector to connect ADOQuery with ADOConnection, DBGrid with DataSource with ADOQuery. 4. Set up a link with our demo database (aboutdelphi.mdb)...

Pulling the Jpeg take two

Since we can do nothing with the DBImage - remove it from the form and place an ordinary TImage component Additional palette on it. Name it ADOImage. Unfortunately the Image component does not have any data-aware properties, so all the code needed to show a picture from a table inside it will require a separate procedure. The easiest thing to do is to put a Button on a form and place all the code inside it's OnClick event. Name the button 'btnShowImage'. To use the ADOBLOBStream the Help...

Storing pictures in Access

Before going on to discussion about displaying the image inside a table within a Delphi form, we need to add some graphical data to our database. Start Access and open the aboutdelphi.mdb database. Open the Applications table it should have one row of data and select the Picture field. 1. Select Insert Object this will display the Insert Object dialog box. 2. Click on the Browse button, this pops up the Browse open dialog. Note you probably have some .jpg files on your computer, so you could...

Setting up Master Detail with ADOExpress

Creating a master-detail data form is not to much complicated. Have an empty Delphi form, and just follow the steps 1. Select the ADO page on the Component palette. Add two TADOTable components and one TADOConnection to a form. 2. Select the Data Access page on the Component palette. Add two TDataSource components to a form. 3. Select Data Controls page on the Component palette. Place two TDbGrid components on a form. Add two DBNavigator components, too. 4. Use the ADOConnection, the...

The Delphi Project

Our task is to have Delphi do all the work. We want to create a new database from code, add all three tables from code, add indexes from code and even set up a referential integrity between those tables - again from code. As usual, have an empty Delphi form. Add two button component. Add a TADOConnection, TADOCommand. We'll use TADOCommand with a DDL language to create and link tables. Add a TADOXCatalog component ActiveX page . The TADOXCatalog will do the trick of creating a new database. Let...

Master Source and Master Fields

The MasterSource and MasterFields properties of the TADOTable component define master-detail relationships in Delphi ADO database applications. To create a master-detail relationships with Delphi, you simply need to set the detail table's MasterSource property to the DataSource of the master table and its MasterFields property to the chosen key field in the master table. In our case, first, set ADOTable2.MasterSource to be DataSource1. Second, activate the Field Link Designer window to set the...

Creating a data entry form

Creating a data editing form by hand is not to much complicated, as we already know. When developing database applications with Delphi and ADO , most of the work is done inside the IDE by simply connecting various components together, thus having to write no code. A typical data browsing editing form presents a database table inside a DBGrid. Another way is to add several data aware controls to a form and link them to the data source. We'll place both a DBGrid and several data aware controls....

Lookup with DBLookup ComboBox

As stated above, when your data entry form is made of more data controls DBEdit, DBComboBox, etc. it makes sense to just use DBLookupComboBox without creating a new field. For the start, use dragging from the Fields editor to add data controls to a form. Drag Name, Author, Type and Description. This will add 4 DBEdit components and 4 Label components. At this point, remove the DBEdit connected with the Author field of the Applications table and replace it with a DBLookupComboBox. Name it...

Seek

The ADO datasets Seek method uses an index when performing a search. If you don't specify an index and you are working with an Access database, the database engine will use the primary key index. Seek is used to find a record with a specified value or values in the field or fields on which the current index is based. If Seek does not find the desired row, no error occurs, and the row is positioned at the end of the dataset. Seek returns a boolean value reflecting the success of the search True...

Quick ADO Delphi report

Unfortunately the Wizard crates a reporting template for BDE based database application, by placing the TTable component on a form along with DB and DBTables units in the uses clause. To prepare the form for our aboutdelphi.mdb MS Access database and ADO you need to delete the TTable from the form as well as the DB and DBTables units from the forms uses clause. We'll now create a simple list report containing data from the Applications table. First make sure the newly created form is the...

Lookup inside a Pick List of a DBGrid Column

The last approach to having a lookup values displayed inside a DBGrid is to use the PickList property of a DBGrid Column object. You'll usually add Columns to a DBGird when you want to define how a column appears and how the data in the column is displayed. A customized grid enables you to configure multiple columns to present different views of the same dataset different column orders, different field choices, and different column colors and fonts, for example . I will not discuss this topic...

Jro

Uvv T jS t - ne uni Hi Miri NiA- . vt-f L ' I f-'iH KTi I I Mha M M- MIMH.il T I lt Jic LTi I I. l'- j I - . p uvv T jS t - ne uni Hi Miri NiA- . vt-f L ' I f-'iH KTi I I Mha M M- MIMH.il T I lt Jic LTi I I. l'- j I - . p UHjniH IT Ffci rtni-j wcli 'J TTH H, J Sn-i. n UlEL l-ir c1l3ELPH.-irHD LFnriiI J UHjniH IT Ffci rtni-j wcli 'J TTH H, J Sn-i. n UlEL l-ir c1l3ELPH.-irHD LFnriiI J ADO does not directly expose a method for compacting a database. By using Jet and Replication Objects JRO , you...

Filtered Filter Options Filter Group OnFilter Record

The Filtered property is a Boolean value True or False that determines if the string in the Filter property is used to filter the dataset. When Filtered is False, the filtering is ignored and the complete dataset is available to the application. The FilterOptions is a set of two values - both used when filtering string fields. If the foCaseInsensitive is included in the FilterOptions, comparison between the literal in the Filter property string and the field values are case-insensitive. The...

Transaction processing

To start a transaction call the BeginTrans method of the TADOConnection object. BeginTrans returns the nesting level of the new transaction. A return value of 1 indicates you have opened a top-level transaction that is, the transaction is not nested within another transaction , 2 indicates that you have opened a second-level transaction a transaction nested within a top-level transaction , and so forth. Once the BeginTrans is executed, the OnBeginTransComplete event is triggered and the...

The DBImage take one

The first thing I do when trying to do something new with Delphi is to ask Delphi Help for help. This is what the Help system replies TDBImage Data Controls page on the component palette represents a graphic image from a BLOB binary large object field of the current record of a dataset. Use TDBImage to represent the value of graphic fields. TDBImage allows a form to display graphical data from a dataset. The DBImage is nothing more than a TImage component with some data aware properties. The...

Adding a new record

The simplest way to add a new record to a table is to click on the DBNavigators Insert button the one with the plus sign on it . The Insert method called adds opens a new - empty record in a table. The DBGrid display one empty row with the asterisk sign in the first column. All three DBEdit components are empty and ready for the user to enter values for the new record. The call to Insert results in calling series related events, too. Programmatically inserting and posting could look like...

ADO Errors collection

Any operation involving ADOExpress components can generate one or more errors. As each error occurs, one or more Error objects are placed in the Errors collection of the ADOConnection component. You must note that error objects represent individual errors from the provider and are not ADO-specific, this means that the same error will be reported differently by MS Access and differently by MS SQL Server. When an error occurs, the provider is responsible for passing an error text to ADO. In...

OLE object type format take three

All this leaves us with nothing but to store the picture to a disk as an ordinary binary file and see what's inside it. One nice thing with picture files formats is that all have some header that uniquely identifies the image. The JPG picture file starts with the, so called, SOI marker that has the value of FFD8 hex. This next line of code stores the value of the Picture field to a file BlobImage.dat in the working directory. Assign this code in the OnCreate event for the form, start the...

Add table create index set referential integrity

The next step is to create all tables three of them , add indexes, and create referential integrity. Even though we could use ADOX, that is, TADOXTable, TADOXKey, etc. I'm somehow more familiar with the standard DDL language and the TADOCommand component. Back in the chapter 11 of this course we discussed database tables porting issues. This time we'll create tables from nothing. The following peaces of code are to be placed inside the button's btnAddTables OnClick even handler, I'll slice the...

Microsofts Variant type

The first is how to handle Microsoft's VARIANT type. Delphi's equivalent is OLEVARIANT, though most programmers will be used to the native STRING, INTEGER and the like. Again this is something Delphi handles with ease with an array of functions such as VarCast. Below is an example of a function that allows us to cast variants into something easier to swallow function oleGetStr value oleVariant string var highVal integer oleArray PSafeArray oleObj oleVariant begin varError result IntToStr value...

From ADO Query to HTML

In this chapter, of the free database Delphi ADO course, you are going to see how to easily create HTML pages based on database information. In particular, you'll see how to open a query from an MS Access database with Delphi and loop through the contents generating an HTML page for each row in a recordset. Start a new application - this creates an empty Delphi form. TAdoConnection name AdoConnection1 TAdoQuery name AdoQuery1 TDataSource name DataSource1 To display the generated HTML you'll...

Begin Trans Commit Trans Roll BackTrans

Database transactions are a means to allow a user to do many operations on a recordset or not to do any of them. There is no such thing in a transaction that one task is done and other is not. Transactions are always executed as a whole . By using transactions, you ensure that the database is not left in an inconsistent state when a problem occurs completing one of the actions that make up the transaction. In Delphi ADO's transaction processing, 3 methods are used with the TADOConnection object...

Pictures inside a database

These days developing database applications requires more than just operating with textual or numeric data. If you are, for example, developing an Internet intranet or multimedia based application, frequently there is a need to display pictures along with text from a database. In this third chapter of the Delphi database course, we'll see how to pull out and display the graphical data images inside an Access database with ADO. Don't be worried with the fact that working with images inside an...

Dynamic queries

One of the great properties of the TADOQuery components is the Params property. A parameterized query is one that permits flexible row column selection using a parameter in the WHERE clause of a SQL statement. The Params property allows replacable parameters in the predefined SQL statement. A parameter is a placeholder for a value in the WHERE clause, defined just before the query is opened. To specify a parameter in a query, use a colon preceding a parameter name. At design-time use the Object...