Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Manipulating Database Record with Codes

pascal

  • Please log in to reply
15 replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 20 March 2011 - 04:13 PM

From the previous tutorials for beginners in database with Delphi (see: Very Basic Database Application in Delphi and Most Basic Editing Database in Delphi) I had shown you how easy to build simple database application with Delphi using only its GUI designer. In the demo project of those tutorials we had written zero lines of codes! But like I also said, we won't be able to do anything more sophisticated if we don't write codes to manipulate the database. And this tutorial will introduce you to the basic of manipulating database records using codes.

In manipulating database records we interested in loading the records, moving cursor (i.e. the changing active record), finding record, editing the record, adding new record, and deleting record.

1. Loading Records

Of course before we could do anything with the records we need to load them into a dataset. To load records into a dataset we need to activate it by
calling its Open method, or by setting its Active property to True. If the dataset is "table" based, such as TADOTable, TTable, or TIBTable, then prior to activating you have to supply the table name. Most "table" based dataset will try to load the whole table when activating. So it might not interesting for those who have to work with tables with large content.

Beside "table" based datasets we also have query based dataset. These datasets execute SQL command to load the records. With these, we can specify which records to load by customizing the "WHERE" clause of the SQL, if the SQL is SELECT query, or adjusting parameters if the SQL refers to stored procedures. SQL based dataset examples are: TQuery, TADODataset, TADOQuery, and TIBQuery.

In our demo project we have a button with caption "Use Table". This button will make the datagrid to show the whole content of "Table11" using table-based dataset TADOTable. And the code for when it got clicked is:
procedure TFormMain.btnUseTableClick(Sender: TObject);
begin
  // close whatever dataset currently shown in the datagrid
  DBGrid1.DataSource.DataSet.Close;
  // Set the table name to our TADOTable
  Table1.TableName := 'Table11';
  // Activate/open the table
  Table1.Open;
  // Assign the table to datasource used by our datagrid
  DBGrid1.DataSource.DataSet := Table1;
end;
Button with caption "Use Query" will make the datagrid to show the content of "Table11" using SQL SELECT command. And to make it a bit different with when we use table-based dataset, we will add filter to the SQL SELECT to make it only show records with FirstName's field started with 'R'. And the code is:
procedure TFormMain.btnUseQueryClick(Sender: TObject);
begin
  // close whatever dataset currently shown in the datagrid
  DBGrid1.DataSource.DataSet.Close;
  // Set the SQL SELECT command
  ADOQuery1.SQL.Text := 'SELECT * FROM Table11 WHERE FirstName LIKE ''R%''';
  // Activate/open the query
  ADOQuery1.Open;
  // Assign the query-based dataset to datasource used by our datagrid
  DBGrid1.DataSource.DataSet := ADOQuery1;
end;
2. Moving Cursor

When working with a dataset, we can only work with the active record, i.e. the cursor. For example, if we have a dataset with name of "Table1" then this code:
  WriteLn(Table1.FieldByName('FirstName').AsString);
means to write the content of field with the name of 'FirstName' from the currently active record of Table1. Table1 may contain a lot of records, therefore making sure that we are working with the correct record is important.

Dataset classes in Delphi's VCL are all descendants from TDataset. And TDataset already provided methods and properties we need related with moving the cursor. The methods are:
  • First, to move cursor to the first record
  • Next, to move cursor to the record next of the current one
  • Prior, to move cursor to the record prior to the current one
  • Last, to move cursor to the last record
  • MoveBy, to attempt to move the cursor x number of records from the current one. When x is positive the cursor will move forward, and cursor moves backward when x is negative. This function will return the number of records it's actually skipped which could be the same or less than x.
  • IsEmpty, to detect whether the dataset has any record or not. When it's empty we can't move anything, right?
  • GetBookmark, to get a bookmark of the current record so we can easily go back to it later.
  • GoToBookmark, to make the record associated with the supplied bookmark active.
  • FreeBookmark, to release the memory occupied by a bookmark.
And TDataset's properties that are helpful when moving cursors are:
  • BoF, short for Beginning of File. Indicates whether the cursor is at the first record or not.
  • EoF, short for End of File. Indicates whether the cursor is at the last record or not.
  • IsUnidirectional, indicates whether the dataset is unidirectional. Unidirectional datasets allows forward-only cursor movement. So it only allows First and Next methods when moving cursors.
  • RecordCount, indicates how many records are there in the dataset. Note that the reliability of this property depends on the implementation in the descendant class. Some dataset might have chosen not to implement this property reliably.
In the demo project we have a button to show the number of records shown in the datagrid. Instead of using RecordCount, here we will use First, Next, and EoF to iterate each record.
procedure TFormMain.btnCountRecordsClick(Sender: TObject);
var
  vCount: Integer;
  vBookmark: TBookmark;
begin
  vCount := 0;

  // we use whatever dataset currently used by the 
  // datagrid
  with DBGrid1.DataSource.DataSet do
  begin
    // get bookmark of the current record so we can 
    // go back to it later
    vBookmark := GetBookmark;
    First; // move to the first record

    // do the following block of codes while we 
    // haven't passed last record
    while not EoF do
    begin
      vCount := vCount + 1; // increment our record counter
      Next; // move to next record
    end;

    // we are done with counting the records, 
    // time to activate the priorly active
    // record that we've bookmarked earlier
    GotoBookmark(vBookmark);
    FreeBookmark(vBookmark);
  end;

  // Display our finding
  ShowMessage('We found ' + IntToStr(vCount) + ' records');
end;
Note that without the bookmarking codes, each time we record-counting we will end activating the last record. Using bookmarking feature, we can make our cursor movements seems transparent, since to our user it looks like the cursor was never moved.


3. Editing A Record

Editing a record means we change the content of some (or all) of its fields. Before we edit a record, we have to make it enters "editable" mode. After we have done editing, we have to "save" the modifications by posting the modifications and thus make the record enters "browsing" mode. To enter "editable" mode, just call the dataset's Edit method. And to post the modification, you can either call Post or CheckBrowseMode method.

After the record enters editable mode, we can change its field(s) with new values. To access a field value we can use FieldByName method or FieldValues property of the Dataset. Since FieldValues is default property with array type, instead of using it like this:
  MyDataset.FieldValues['FieldName'] := 'New Value';
we can use shorter way like this:
  MyDataset['FieldName'] := 'New Value';
There are other ways to access the fields beside these two. Like using persistent field, or using TField objects contained in Fields property. But I will save them for later.

In our demo project there is a button with 'Change Name' caption. The button will change the FirstName and LastName fields of the current record. The code is:
procedure TFormMain.btnChangeNameClick(Sender: TObject);
var
  vNewFirstName: string;
  vNewLastName: string;
begin
  if DBGrid1.DataSource.DataSet.IsEmpty then
    raise Exception.Create('There is no record available '
                           + 'for editing');

  vNewFirstName := DBGrid1.DataSource.DataSet['FirstName'];
  vNewLastName := DBGrid1.DataSource.DataSet['LastName'];

  if not InputQuery('Change FirstName'
                    , 'New FirstName'
                    , vNewFirstName) then
    Exit;

  if not InputQuery('Change LastName'
                    , 'New LastName'
                    , vNewLastName) then
    Exit;

  with DBGrid1.DataSource.DataSet do
  begin
    Edit; // enter edit mode
    // change the content of field "FirstName"
    FieldByName('FirstName').Value := vNewFirstName;
    // change the content of field "LastName"
    FieldByName('LastName').Value := vNewLastName;
    Post;
  end;
end;
Cancelling changes
If for any reason we have to cancel any changes we have made to the fields, we should call Cancel instead of Post or CheckBrowseMode. Cancel will discard any changes we have made and make the dataset back to "browsing" mode.

For example, in our demo project there is a button 'Change Name (Dont Post)'. When we click that button we will change the FirstName and LastName fields. But the code in that button's OnClick will not automatically post/save the changes, although we can still inspect the changes from the datagrid. If we click the Cancel button, the changes will be discarded and datagrid will show back the old values.


4. Adding/Inserting New Record

The process of adding or inserting new records actually is very similar with editing a record. The only difference is that we need to call Append or Insert instead of Edit when we make the dataset entering editable mode. Beside that the rest are the same.

For example, in our demo project we have 'Add Name' name button which will add a new record with code of:
procedure TFormMain.btnAddNameClick(Sender: TObject);
var
  vFirstName: string;
  vLastName: string;
begin
  if not InputQuery('FirstName'
                    , 'FirstName'
                    , vFirstName) then
    Exit;

  if not InputQuery('LastName'
                    , 'LastName'
                    , vLastName) then
    Exit;

  with DBGrid1.DataSource.DataSet do
  begin
    Insert; // add new record (****)
    // change the content of field "FirstName"
    FieldByName('FirstName').Value := vFirstName;
    // change the content of field "LastName"
    FieldByName('LastName').Value := vLastName;
    Post;
  end;
end;
Cancelling Record Addition
Similar with when editing existing record, we can call Cancel method instead of Post or CheckBrowseMode. This time, however, the new record will be removed (since it's actually non-existent in the first place).


5. Deleting Record

To delete a record we only need to go to the record and call Delete method of the dataset. Note that we can not cancel this operation. So I really suggest to add some verification prior calling Delete.

In the demo project, this is a sample of deleting current record.
procedure TFormMain.btnDeleteClick(Sender: TObject);
begin
  with DBGrid1.DataSource.DataSet do
  begin
    // no need to delete if dataset is empty
    if IsEmpty then Exit;

    // verify the user action is not accidental
    if MessageDlg('Are you sure you want to delete '
                  + 'this record?'
                  , mtConfirmation
                  , [mbYes, mbNo], 0)=mrNo then
      Exit;

    Delete; // delete the current record
  end;
end;
Full source code of the demo project is attached. Note that the database used in the demo project and also how to setup the database connection can be found in the first tutorial in this series.

Attached Files


Edited by LuthfiHakim, 26 February 2013 - 06:49 PM.

  • 0

#2 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 24 October 2013 - 06:58 AM

Hi, I'm currently working on a task for school where I need to use a database repeatedly to compare user inputs. Is there a way to clear the whole database in order to use it again with new data? I am using MS Access by the way. 

 

Thanks in advanced. :)


  • 0

#3 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 25 October 2013 - 06:58 AM

I eventually found a solution by using the following code :    

 

while Table1.recordcount <> 0 do

Table1.Delete;


  • 1

#4 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 25 October 2013 - 07:49 AM

Yeah. That's one way to clear a table. Another way is by executing sql command like this:

DELETE FROM MyTableName

Of course with this you need to use query-based dataset. I.e. datasets that accept sql command.


  • 0

#5 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 25 October 2013 - 08:22 AM

Unfortunately I don't know much about databases and I need to hand my project in on Tuesday, therefore I do not want to play around with object that I don't know all too much about. So far the only database object we have used in class are DBGrid, DBSource, ADOTable and ADOConnection. Thanks though for the help  :thumbup: .


  • 0

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 25 October 2013 - 11:17 AM

ADOQuery is easy enough to use and can utilize your ADOConnection.


  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 25 October 2013 - 11:52 AM

Sorry to bother again. A bit off of the subject now but you are the only person replying to me. I have my program do error catching when btnEnter clicked, I have a few fields to check such as ID Number, Cell Number etc. For example:

 

if Length(IDNumber) <> 13 then

  ShowMessage('Please check your ID number again as it is not valid');

{Now I want to add a command to stop the rest of the code from running and to return to the form where the user can fix their mistake}

 

At the moment it does the ShowMessage command then carries on with the rest of the code. Sorry if I am being a bother and I understand if you cannot help me as I am off topic

 

P.S I also tried a while loop, but this caused me to be unable to close the showMessage as it would repeatedly popup as soon as I pressed OK


Edited by Quintin96, 25 October 2013 - 12:06 PM.

  • 0

#8 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 25 October 2013 - 12:43 PM

I would follow it with an else and a begin/end block.


  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#9 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 25 October 2013 - 11:17 PM

Sorry to bother again. A bit off of the subject now but you are the only person replying to me. I have my program do error catching when btnEnter clicked, I have a few fields to check such as ID Number, Cell Number etc. For example:

 

if Length(IDNumber) <> 13 then

  ShowMessage('Please check your ID number again as it is not valid');

{Now I want to add a command to stop the rest of the code from running and to return to the form where the user can fix their mistake}

 

At the moment it does the ShowMessage command then carries on with the rest of the code. Sorry if I am being a bother and I understand if you cannot help me as I am off topic

 

P.S I also tried a while loop, but this caused me to be unable to close the showMessage as it would repeatedly popup as soon as I pressed OK

 

The best practice for this is to raise exception. Of course you can also use if then else (for newbie i really recommend this).

 

If you choose the path of exception, you could do something like this:

if Length(IDNumber) <> 13 then
  raise Exception.Create('Please check your ID number again as it is not valid');

// next commands
...
...

With that code, if the length of IDNumber was not 13, the next commands will not be executed. And your application will stop (but not terminated) after nicely show error message of "Please check your ID number again as it is not valid".


Edited by Luthfi, 25 October 2013 - 11:17 PM.

  • 0

#10 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 26 October 2013 - 12:34 AM

Thank you. I tried the exception code and it looks as follows :

 

sCellNr := edtNr.Text;
  if (Length(sCellNr) <> 10) then
    raise Exception.Create('Please check your contact number again as it is not valid');
  sID := edtID.Text; {Rest of code}
 
It then gave me the following Debugger Exception Notification:
 
Project PredictionGame_p.exe raised exception class Exception with message 'Please check your contact number again as it is not valid'.Process stopped. Use Step or Run to continue.
 
I then click the run button and the program continued as it should with the exception message showing correctly, however I need to get rid of the Exception Notification as my teacher will not realise to carry on and will likely think my program crashes there. Thanks again for the help!

  • 0

#11 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 26 October 2013 - 12:42 AM

That will only happening if you run the project inside Delphi IDE. When it's running by its own, there will be no such thing. In fact you can tell Delphi IDE to ignore that exception.


Edited by Luthfi, 26 October 2013 - 01:00 AM.

  • 0

#12 Quintin96

Quintin96

    CC Lurker

  • New Member
  • Pip
  • 8 posts
  • Location:Nelspruit
  • Learning:Java, C#, JavaScript, Delphi/Object Pascal

Posted 26 October 2013 - 12:43 AM

How do I tell the IDE to ignore the exception? Sorry just don't know all that much yet.


  • 0





Also tagged with one or more of these keywords: pascal

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download