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...
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...
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....
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...
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 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...
Compacting an Access database with ADO and Delphi DB Course Chapter
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...
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...
From ADO Query to Html Db Course Chapter
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 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...
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...
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...
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...
This chapter of the free database course for Delphi beginners shows how to use JRO from Delphi in or
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...
Compact Delphi Project
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...
Lock Type
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 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...
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...
