Columbo

I changed the code in the connection procedure as follows: Procedure TfrmDatabaseTest.btnConnectClick(Sender:TObject); begin try if not FileExists(‘dat2.sqlite’) then begin memData.Text := ‘Cannot connect to database!’; end Else Begin //Establish connection FDConnection1.Connected := true btnExecute.Enabled := true; memData.Text := ‘Connection Established!’; end; end;

I changed the code in the connection procedure as follows: Procedure TfrmDatabaseTest.btnConnectClick(Sender:TObject); begin try if not FileExists(‘dat2.sqlite’) then // I added this code to see if it was finding the database. begin memData.Text := ‘Cannot connect to database!’; end Else Begin //Establish connection FDConnection1.Connected := true btnExecute.Enabled := true; memData.Text := ‘Connection Established!’; end; end; When I run the program now I get the message ‘Cannot connect to database!’ so obviously there is a problem finding and connecting to the database which is why I was getting the 'no such table' error. The path to the database is correct. I even tried placing the dat2.sqlite file into the same folder as the program and changing the path but still can't connect.

Hi JonRobertson, In the tutorial I changed the database from employees to the database that I was going to use in the program that I want to write, that is if I can figure this Delphi tutorial out. The database is dat2.sqlite and the table is DAT2. I originally wrote the program in Clipper back in 1997 and the database was in .DBF format. I later converted the .DBF to SQLite and then I wrote the same program again in Purebasic using the sqlite format of the Database and it worked perfectly. The file that I am trying to access with FireDAC is the same database that I used in the Purebasc version of the program. The table is definitely there and it is DAT2. I followed you suggestion and changed the database from dat2.sqlite in the path to dummy.sqlite and it still says 'Connection established!' so that may be where the problem lies. It is probably not opening the database and thus cannot find the table. So I guess I have to determine if it is not opening the database, then why? I'll look at your tutorial although I suspect that the only real difference will be the database and table names.

First of all, I want to thank everyone for their help and suggestions. I'll try to respond to all of the suggestions in this one post. Stano: No leading spaces in table title. haentschman: I have it set in the Inspector 'Connection FDConnection1' Serge_G: I Changed the folder names removing all of the white spaces and changed the path in my code to reflect that. As for adding parameters, I am aware of that but if it can't find the table 'DAT2' I don't think the parameters mean much. I removed 'Database = ' and I set ActiveStorageUsage to [auDesignTime]. I am still getting ''no such table DAT2'. When I click the 'Connect' button it says 'Connection established!' but how can I tell if there really is a connection to the database?

Well, I rewrote the entire tutorial but this time I used FireDAC components for the connection and query. When I run the program it says 'Connection established!'. Then when I click the 'Execute' button I get the same error as with the Delphi components saying 'no such table DAT2'. If you look at the image in my post at the top of this page you can see that the table DAT2 clearly exists. I'm totally confused with this. Suggestions? t FDTest_u; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, Vcl.StdCtrls, FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs, FireDAC.Phys.SQLiteWrapper.Stat; type TfrmDatabaseTest = class(TForm) FDConnection1: TFDConnection; btnConnect: TButton; btnExecute: TButton; memData: TMemo; FDQuery1: TFDQuery; procedure btnConnectClick(Sender: TObject); procedure btnExecuteClick(Sender: TObject); private { Private declarations } public { Public declarations } procedure ShowSelectResults; end; var frmDatabaseTest: TfrmDatabaseTest; implementation {$R *.dfm} procedure TfrmDatabaseTest.btnConnectClick(Sender: TObject); begin FDConnection1.Params.Add('D:\Database=Delphi 11 Community\My Projects\FDTest\Data\dat2.sqlite'); try //Establish connection FDConnection1.Connected := true; btnExecute.Enabled := true; memData.Text := 'Connection established!'; except on E: EDatabaseError do ShowMessage('Exception raised with message:' + ' ' + E.Message); end; end; procedure TfrmDatabaseTest.btnExecuteClick(Sender: TObject); var query: String; begin memData.ClearSelection; //A random query query := 'SELECT * FROM DAT2;'; try //Assign the query to the object FDQuery1 FDQuery1.SQL.Text := query; FDQuery1.Active := true; except on E: Exception do memData.Text := 'Exception raised with message: ' + ' ' + E.Message; end; //Show the results of the query in a TMemo control ShowSelectResults(); end; procedure TfrmDatabaseTest.ShowSelectResults(); var headline: TStringList; i: integer; currentField: Tfield; currentLine: String; begin if not FDQuery1.IsEmpty then begin FDQuery1.First; headline := TStringList.Create; try FDQuery1.GetFieldNames(headline); while not FDQuery1.Eof do begin currentline := ''; for i := 0 to headline.Count-1 do begin currentField := FDQuery1.FieldByName(headline[i]); currentLine := currentLine + ' ' + currentField.AsString; end; end; memData.Lines.Add(currentLine); FDQuery1.Next; finally headline.Free; end; end; end; end.

Thanks, I corrected that. I still don't understand why it says that there is 'no such table DAT2' when clearly it is there.

When I run the program it says 'Connection established'. The error comes when it tries to retrieve data from the table DAT2. The error says 'no such table.'

Thanks Surge_G. I'll look at your code and try to digest it. I'll also read the documentation that you suggest in the link that you included. But, in my code I am only getting one error and that is that it says that there is 'no such table DAT2. If you look at the image that I attached in my message at the top of this page you can see that the table does exist so I am guessing that maybe I have some kind of error in my code.

https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Tutorial:_Connecting_to_a_SQLite_Database_from_a_VCL_Application

I am getting this code from an Embarcadero tutorial and that is what they use in the tutorial.

Here is the contents of the ConnectDB_u.dfm: object Form1: TForm1 Left = 192 Top = 107 Width = 870 Height = 450 Caption = 'Form1' Color = clBtnFace Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -11 Font.Name = 'MS Sans Serif' Font.Style = [] OldCreateOrder = False PixelsPerInch = 96 TextHeight = 13 end Here is the code from SQLQuery1: object SQLConnection1: TSQLConnection DriverName = 'Sqlite' Params.Strings = ( 'DriverUnit=Data.DbxSqlite' 'DriverPackageLoader=TDBXSqliteDriverLoader,DBXSqliteDriver280.bp' + 'l' 'MetaDataPackageLoader=TDBXSqliteMetaDataCommandFactory,DbxSqlite' + 'Driver280.bpl' 'FailIfMissing=True' 'Database=') Left = 304 Top = 384 end

Ok friends, My son just loaned me one of his computers with Windows 10 on it so I downloaded and installed Delphi 11 Community on it. I rewrote the code for the tutorial that I was trying in Delphi 7 with Delphi 11 and had 8 errors. I corrected 7 of these errors and now I have just one left and I can’t see where the problem is. When I run the program it connects to the database ok but when I click the ‘Execute’ button I get the error saying that ‘no such table: DAT2’. I have attached an image of the database in SQLite Stuio showing the database and the tables. I have obviously made an error in my code somewhere but I don’t see it. Here is the code: unit ConnectDB_u; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DbxSqlite, Data.FMTBcd, Data.DB, Data.SqlExpr, Vcl.StdCtrls, DBXpress, FMTBcd, DB, SqlExpr, StdCtrls, Classes, Controls; type TfrmConnectDB = class(TForm) btnConnect: TButton; btnExecute: TButton; SQLConnection1: TSQLConnection; memOutput: TMemo; SQLQuery1: TSQLQuery; procedure btnConnectClick(Sender: TObject); procedure btnExecuteClick(Sender: TObject); private { Private declarations } public { Public declarations } procedure ShowSelectResults(); end; var frmConnectDB: TfrmConnectDB; implementation {$R *.dfm} procedure TfrmConnectDB.btnConnectClick(Sender: TObject); begin SQLConnection1.Params.Add('D:Delphi 11 Community\My Projects\Connecting to SQLite\Data\dat2.sqlite'); try //Establish the connection SQLConnection1.Connected := true; btnExecute.Enabled := true; memOutput.Text := 'Connection established'; except on E: EDatabaseError do ShowMessage('Exception raised with message ' + E.Message); end; end; procedure TfrmConnectDB.btnExecuteClick(Sender: TObject); var query: String; begin memOutput.ClearSelection; //A random query query := 'SELECT * FROM DAT2;'; try //Assign the query to the object SQLQuery1 SQLQuery1.SQL.Text := query; SQLQuery1.Active := true; except on E: Exception do memOutput.Text := 'Exception raised with message:' + E.Message; end; //Show the results of the query in in a TMemo control ShowSelectResults(); end; procedure TfrmConnectDB.ShowSelectResults(); var headline: TStringList; i:integer; currentField: TField; currentLine: String; begin if not SQLQuery1.IsEmpty then begin SQLQuery1.First; headline := TStringList.Create; try SQLQuery1.GetFieldNames(headline); while not SQLQuery1.Eof do begin currentLine := ''; for i := 0 to headline.Count -1 do begin currentField := SQLQuery1.FieldByName(headline[i]); currentLine := currentLine + ' ' + currentField.AsString; end; memOutput.Lines.Add(currentLine); SQLQuery1.Next; end; finally headline.Free; end; end; end; end.

Thanks Serge, I'll check it out.

Hi stano, I don't know anything about Firebird but I will look into it. I seen it mentioned a number of times in posts related to FireDAC.

Interesting that you started with dBase as well. I don't know much about Lotus Approach although I knew of it back in the day. I am assuming that TDBGrid gives you a MS Access style layout? The program that I wrote in Clipper was called "This Day In History" and it did not display anything. It uses 7 databases and once the person's name and birth date is entered it goes directly to the printer and prints out the data for that date in history. I did another program in Purebasic called "Prehistoric Life" and it uses a lot of text fields, a memo field, 2 image boxes and a few buttons. It displays data and pictures on 112 animals and sea creatures that lived in prehistoric times. That might also be a good program to learn Delphi with. I did the same program when I was learning Python.

Thanks, Die Hollander. I have bookmark that web site.

Thanks Gary. Actually I did watch Gerhard's videos. What I like about his videos is that he takes his time and explains everything. I found that a lot of videos that I had looked at previously, the teacher went too fast for a beginner to keep up. They know what they are doing so they have the cursor moving all over the place clicking this and clicking that and I find it difficult to follow. Gerhard is not like that and takes his time. I still have a few of his videos to watch yet but I didn't see any that dealt with databases. I used to program in dBaseIII and Clipper back in 1997 and I wrote a program where you enter someone's name, the day, month and year of their birth and the program would do a printout showing their birthstone, birth flower, astological sign, planet and element, 3 newspaper headlines that appeared on their birthday, the Stanley cup winner for that year, The Greay Cup winner, the Academy Award best Actor and actress, best movie, the U.S President at that time, the Canadian Prime Minister, and the price of a loaf of bread, quart of milk, a dozen eggs, a pound of butter, average price of a home, average price of an automobile and the average price of gas. I wanted to try writing the same program in Delphi so I have to get to know how to manipulate databases in Delphi. I took my .DBF database files and converted them to SQLite so I still have the data for all birth dates from the year 1900 to 1997. I will have to add the data from 1998 to present. A lot of work and research but fun in the end. Besides, I'll be 82 in January so, hopefully, it will help to keep my mind sharp. Thanks again Gary. You have been very helpful.

That has got me past the error. Thank you very much guys. I've only been playing with Delphi 7 for 3 days and it is somewhat confusing at times. And now,... on to the next error. 🙂 Trying to assign an empty string to a variable. Thanks to all for your help and patience. Very much appreciated.

Thanks Gary. In the declaration section I put the following code: private { Private declarations } public { Public declarations } procedure TfrmDatabaseTutorial.ShowSelectResults; end; and in the implementation I put the following code: procedure TfrmDatabaseTutorial.ShowSelectResults; var headline : TStringList; ... but I am still getting the error flagged on the line in the Public declarations saying 'Undeclared Identifier: 'TfrmDatabaseTutorial'.

I tried that but it now flags an error at private { Private declarations } public { Public declarations } procedure TfrmDatabaseTutorial.ShowSelectedResults(); end; The actual procedure is: procedure TDatabaseTutorial.ShowSelectResults(); If I change the actual procedure to match the change in the Public delarations using 'ShowSelectedResults' I still get the error in the Public declarations.

Thanks Tom, I replaced the line procedure TDatabaseTutorial.ShowSelectResults(); with procedure TfrmDatabaseTutorial.ShowSelectResults(); but error still exists.

Thanks Serge_G. Delphi 7 actually belongs to my son so I am using his computer to see if I can learn Delphi before making such a purchase. I looked at Delphi 11 Community and downloaded it but it won't install. I am still using Windows 7 Professional on my computer, (I'm not a fan of Windows 10 or 11). I am pretty sure that the Delphi web site said that Delphi 11 was compatible with Windows 7 but it won't install. When I enter the Serial number that they emailed to me it says that the Serial number is invalid. I tried 3 times uninstalling and reinstalling but continue to have the same problem. One thing that I don't understand is that at one point a dialog came up saying that I was using Windows 6.1 which I have never heard of. I gave up on trying to install it. I will Google ZEOSDBO and try that as you kindly suggested. Since I posted this I have downloaded ZeosLib but any instructions that I have found about installing and using it seems to always refer to Firebird. I want to use it for SQLite.

I made the changes that you suggested but it still flags the same error, Undeclared identifier: ‘ShowSelectResults’. Here is the change that I made: unit DatabaseTutorial_u; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DBXpress, FMTBcd, DB, SqlExpr, StdCtrls; type TfrmDatabaseTutorial = class(TForm) btnConnect: TButton; btnExecute: TButton; SQLConnection1: TSQLConnection; memOutput: TMemo; SQLQuery1: TSQLQuery; procedure btnConnectClick(Sender: TObject); procedure btnExecuteClick(Sender: TObject); private { Private declarations } public { Public declarations } procedure ShowSelectedResults; end; I also changed hline1 to headline as you suggested.