Locate
This generic search method sets the current record to be the first row matching a specified set of search criteria. By using the Locate method we can look for values of one or more fields, passed in a variant array. The next code puts the Locate method to work finding the first record that contains the string 'Zoom' in the Name field. If the call to Locate returns True - the record is found and is set to be the current one. var ffield, fvalue string opts TLocateOptions ffield 'Name' fvalue...
Setting up MasterDetail 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...
Compacting an Access database with ADO and Delphi DB CourseChapter
Chapter sixteen of the free Delphi Database Course for beginners. While working in a database application you change data in a database, the database becomes fragmented and uses more disk space than is necessary. Periodically, you can compact your database to defragment the database file. This article shows how to use JRO from Delphi in order to compact an Access database from code. While you add and delete records from database tables, your database becomes more and more fragmented and uses...
Connecting to a database BDE ADO DB
Chapter two of the free Delphi database online course. How to connect to an Access database - the UDL file Looking forward the smallest ADO example. As shown in the previous chapter of this course, a database is a collection of one or more tables that store data in a structured format. These tables, contain data that is represented by rows and fields. When a database consists of two or more tables, these tables generally contain separate yet related data. MS Access, Interbase or SQL Server use...
Moving on from BOF to EOF
To iterate through a recordset and to sum some values we'll need to use methods of a dataset component. Take a look at the following code while not ADOTable1.EOF do begin Do_Summing_Calculation ADOTable1.Next end finally ADOTable1.EnableControls end The First method is used to set the current row in the dataset to the first one the Next moves to the next row in a dataset. The EOF and BOF property indicates whether the dataset is at the last first row. In most cases, the dataset is connected to...
TADOCommand
In ADOExpress, the TADOCommand component is the VCL representation of the ADO Command object. The Command object represents a command a query or statement that can be processed by the data source. Commands can then be executed using the ADOCommand's Execute method. TADOCommand is most often used for executing data definition language DDL SQL commands. The CommandText property specifies the command to execute. The CommandType property determines how the CommandText property is interpreted. 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...
New Data Module
To create a data module at design time, choose File New Data Module. At design time, a data module looks like a standard Delphi form with a white background and no alignment grid. At run time data module exists only in memory. DataModule has only two properties, Name and Tag, and two events, OnCreate and OnDestroy. Use the Name property when referring to module's objects from other units. DataModules are not limited to data access components, they can also contain other nonvisual components,...
Data modifications DB
Chapter six of the free Delphi Database Course for beginners. Learn how to add, insert and delete records from a database The main goal of developing database applications is to provide a means of modifying the data. In the first five chapters this DB Course has shown how to connect to an Access database, how to display the data from a database table and how to navigate through the records in a table. In this sixth chapter of the free database course we'll see exactly how to add, edit and...
TeeChart
The DBChart component is a powerful tool for creating database charts and graphs. It is not only powerful, but also complex. We wont be exploring all of its properties and methods, so you'll have to experiment with it to discover all that it is capable of and how it can best suite your needs. By using the DBChart with the TeeChart charting engine you can quickly make graphs directly for the data in datasets without requiring any code. TDBChart connects to any Delphi DataSource. ADO recordsets...
From ADO Query to HTML DB CourseChapter
How to export your data to HTML using Delphi and ADO. This is the first step in publishing your database on the Internet -see how to create a static HTML page from an ADO query. 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...
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...
Filtered FilterOptions FilterGroup OnFilterRecord
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...
Data filtering DB
Chapter eight of the free Delphi Database Course for beginners. Using Filters to narow the scope of data that is presented to the user. As stated in one of the previous chapters, both TADOQuery and TADODatSet as dataset components share the same set of common methods and events. On of the features exposed by those datasets is the ability to narrow the scope of data that is presented to the user. Consider that you might have a database table with thousands of records, but your users are...
Lookup
Lookup does not move the cursor to the matching row, it only returns values from it. Lookup returns a variant array containing the values from the fields specified in a semicolon-delimited list of field names whose values should be returned from the matching row. If there are no matching records, Lookup returns a Null variant. The following code fills in a LookupRes variant array 'Name1, 'Zoom', 'Author Description' if not VarIsNull LookupRes then ShowMessage VarToStr LookupRes 0 author name...
An example
To see some ADOQuery action we'll code a small example. Let's make a query that can be used to fetch the rows from various tables in a database. To show the list of all the tables in a database we can use the GetTableNames method of the ADOConnection component. The GetTableNames in the OnCreate event of the form fills the ComboBox with the table names and the Button is used to close the query and to recreate it to retrieve the records from a picked table. The event handlers should look like...
Pictures inside a database DB
Chapter three of the free Delphi database online course. Displaying images BMP, JPEG, inside an Access database with ADO and Delphi. 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...
Filtering
Filtering is the method by which some data from the dataset is excluded from view by displaying only those records that meet specific criteria. Filtering permits you to present varying views of the data stored in a dataset without actually affecting that data. This criteria is set through the Filter property of the dataset component TADOTable or TADOQuery , it can be set at both design and run time. Filter property represents a string that defines the filter criteria. For example, if you want...
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...
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...
Transactions in Delphi ADO database development DB CourseChapter
How many times have you wanted to insert, delete or update a lot of records collectively wanting that either all of them get executed or if there is an error then none is executed at all This article will show you how to post or undo a series of changes made to the source data in a single call. The general idea behind a transaction is that several steps can be performed in series, with the capability to undo all of the steps at once if needed. In addition, the transaction should happen inside...
This chapter of the free database course for Delphi beginners shows how to use
ADO does not directly expose a method for compacting a database. By using Jet and Replication Objects JRO , you can compact databases, refresh data from the cache, and create and maintain replicated databases. The JRO exposes two objects, the JetEngine object and the Replica object. The Replica object is used to manipulate replicated databases. We will not deal with database replications in this chapter. By using the Jet Engine object we can programmatically control compacting and refreshing...
Lookup inside a PickList 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...
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...
Compact Delphi Project
It's time to see some code. Create a new Delphi application with one form. Add two Edit controls and a Button. From the ActiveX component page pick JetEngine. The first Edit should be renamed to edSource, the second one to edDest. The button should be renamed to btnComapct. The JetEngine should be renamed to JE. It should all look like The TJetEngine class has a CompactDatabase method. The method takes two parameters the ADO connection string for the source as well for the destination database....
The correct way take four
Provided with the Blob type field our function should return the position of the 'FFD8' string inside the ADOBlobStream. The ReadBuffer reads byte by byte from the stream. Each call to ReadBuffer moves the position of the stream by one. When two bytes together as hex values result in SOI marker the function returns the stream position. This is the function bS TADOBlobStream.Create PicField, bmRead Once we have the position of the SOI marker we use it to seek to it in the ADOBlob stream. Once we...
LockType
The LockType property tells the provider what type of locks should be placed on records during editing. Locking can prevent one user from reading data that is being changed by another user, and it can prevent a user from changing data that is about to be changed by another user. Modifying a record in an Access database locks some neighboring records. This is because Access uses, so called, page locking strategy. This means that if a user is editing a record, some other user won't be allowed to...
Lookup with DBLookupComboBox
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...
Lets chart Code
Ok, let's see what we can do about this bug. It seems that all the properties of the chart can be set with the Chart Editor except those related to recordset. We'll simply as suggested set all from code. Add the next code to the form's OnCreate even handler DBChart1.Legend.Visible False with DBChart1.SeriesList.Series 0 do begin DataSource ADOQuery1 XLabelsSource ADOQuery1Company.FieldName XValues.ValueSource ADOQuery1SumItems.FieldName YValues.ValueSource '' Marks.Style smsXValue...