Problem connecting to a MS Access Database with ODBC
lazarus / 16-Databases

----------------------------------------------------------------------
timmermanj · 2009-12-08 09:45

I've searched in this forum how to connect to a MS Access DB with
ODBC.

I've tried this with following code:

procedure TForm1.FormCreate(Sender: TObject);
begin
  ODBCConnection1.Connected:= False;
  ODBCConnection1.Driver:= 'Microsoft Access Driver (*.mdb)';
  ODBCConnection1.Params.Add('DBQ=C:\BackUp.mdb');
  ODBCConnection1.Connected:= True;
end;

When i try this i get following error. (See picture in attachment)

Could someone please tell me what i did wrong?

Thanks a lot!

----------------------------------------------------------------------
LacaK · 2009-12-10 06:20

Try set DatabaseName property to name of your ODBC DataSource (As
defined in ControlPanel/ODBC) and omit Driver property (I had never
used it) ...

----------------------------------------------------------------------
timmermanj · 2009-12-10 14:24

Could someone please show me a simple example, just how to connect to
a MS Access DB?
I really don't have a clue.

I would really appreciate the help!

Thanks a lot!!!

----------------------------------------------------------------------
theo · 2009-12-10 15:10

I've tried this once and it worked.
I've simply set up a System DSN (using ODBC Administrator) and then
entered the DSN in the DatabaseName property of the ODBCConnection and
set Login Prompt to false.

I don't know it this helps.

----------------------------------------------------------------------
stanks · 2010-01-10 10:20

this example works great under winblowz but not under linux

procedure TMKB.Button1Click(Sender: TObject);
var
  S: String;
  conn: TODBCConnection;
  query: TSQLQuery;
  transaction: TSQLTransaction;

begin
  conn := TODBCCOnnection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);
  try
    try
//      conn.HostName := '127.0.0.1';
//      conn.DatabaseName := 'diary'; {replace this with the name of
your database}
      conn.Transaction := transaction;
//      conn.UserName:= '';
//      conn.Password:= '';
// for winblowz
      conn.Params.Add ('DBQ=C:\Lazarus-Pascal\odbc-example\dg.mdb');
      conn.Driver := 'Microsoft Access Driver (*.mdb)';
      query.DataBase := conn;
      query.UsePrimaryKeyAsKey:=false;
      query.SQL.Text := 'SELECT * FROM dg';
      query.Open;
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('sifra').AsString + #13#10;
        query.Next;
      end;
    finally
      query.Free;
      conn.Free;
    end;
  except
    on E: Exception do
      ShowMessage(E.message);
  end;
  Memo1.Text:= S;
end;

I only change some things in this example. Orig example is on wiki (i
don't have url now...search for it)

btw can somebody tell me why this example fails on linux (i changed
the path to db file)?

----------------------------------------------------------------------
theo · 2010-01-10 12:24

http://wiki.lazarus.freepascal.org/ODBCConn#TOBCConnection

----------------------------------------------------------------------
dobedani · 2010-03-22 09:10

Hi folks,
I'm new to Lazarus but not new to Delphi. I tried the above example on
Windows XP SP2, specifying the driver and the DBQ. As far as I
understand, I don't need to do anything in the ODBC Data Source
Administrator and in fact I even want to avoid that. Unfortunately, I
am getting this error when I try to open the connection:

Could not connect with connection string: DSN=access;
DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=D:\MyPathWithoutSpaces\MyDB.mdb; ;". ODBC error details:
LastReturnCode: SQL_ERROR; Record 1: SqlState: IM002; NativeError:0;
Message: [Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified;

Am I overlooking something?

BTW, the executable I got for my rather simple app has size 20000 kb
whereas before with Delphi it had size 750 kb. Why this great
difference? TIA

Kind regards,
Dobedani

----------------------------------------------------------------------
theo · 2010-03-22 09:42

Quote from: dobedani on March 22, 2010, 10:10:42 amBTW, the executable
I got for my rather simple app has size 20000 kb whereas before with
Delphi it had size 750 kb. Why this great difference?

http://wiki.lazarus.freepascal.org/Lazarus_Faq#Why_are_the_generated_b
inaries_so_big.3F

----------------------------------------------------------------------
dobedani · 2010-03-22 11:50

Hi folks,

Theo: thanks for the reply. Can somebody also answer my other
question? TIA

Regards, Dobedani

----------------------------------------------------------------------
Wodzu · 2010-03-22 12:00

Have you read article given to you by theo?

It says:

Connecting to MS Access

On Windows, you can use

Driver = 'Microsoft Access Driver (*.mdb)'
Params.Strings = (
  'DBQ=C:\path\to\my\database.mdb'
)

Note: The MS Access ODBC driver seems not to support the
SQLPrimaryKeys API function, hence UpdateIndexDefs will fail.
Currently, you must set SQLQuery.UsePrimaryAsKey:=false; to avoid an
exception until code has been added that tests for the availability of
the function in the driver.


And I see that in your connection string you are setting up a DSN.
Don't do this unless you configured it in ODBC manager.

----------------------------------------------------------------------
dobedani · 2010-03-22 13:12

Hi Wodzu,

Thanks a lot for your reply! I've got my application working. Now I
trust Lazarus a lot more.

Kind regards,
Dobedani

----------------------------------------------------------------------
thierrybo · 2010-03-22 19:58

Quote from: dobedani on March 22, 2010, 10:10:42 am
BTW, the executable I got for my rather simple app has size 20000 kb
whereas before with Delphi it had size 750 kb. Why this great
difference? TIA


Besides the Wiki link , I post a chart of what base sizes you can
achieve :

http://www.lazarus.freepascal.org/index.php/topic,7916.msg41310.html#m
sg41310

----------------------------------------------------------------------
AdrianW · 2010-03-25 10:02

Thanks to stanks (for the instant poetry opportunity, and..)
for your example of ODBC Access DB connectivity.
At first I got "transaction not set", but then I added the line
transaction.DataBase := conn;
after setting the conn.Driver, and it worked *nicely*
Lazarus lives!

----------------------------------------------------------------------
snesska · 2010-04-01 15:31

Hi, everybody!
I have placed components on the form (as in the picture in the
attachment).
I have configured the components so that DBGrid would link up with the
base in Microsoft Access (the base has only one table). In control
panel, I have configured SystemDSN, FileDSN and Drivers as it says in
http://wiki.lazarus.freepascal.org/ODBCConn.
Lazarus doesn't report errors, but I can't see the data from the table
(Table is in MS Access) in DBGrid, Navigator also doesn't work.
My operating system is Windows XP SP3, I have tried both Access 2003
and 2007.
Does anybody have any idea what seems to be the problem?
Also, can you give me some suggestions about some other way to view
and update data in MS Access base?
Are there any tutorials that explain that in detail.
Kind regards,
Snesska

----------------------------------------------------------------------
Zoran · 2010-04-01 16:27

You should use either SystemDNS or FileDNS, not both.

From the picture you provided not much can be seen -- we can't see how
you set properties of your components.

Can your ODBCConnection component connect in design time (when you set
connected to True in ObjectInspector)?
Then, what happens if you set Query1.Active to True in
ObjectInspector?

I just tried with SystemDNS and at first the Query coud not open, but
when I set the Query's UseKeyAsKey to False it worked (Win XP sp3,
Access 2003).

----------------------------------------------------------------------
thierrybo · 2010-04-01 21:09

I just tried with C:\Program Files\Fichiers communs\Borland Shared
D2006\Data\bddemos.mdb with a file dsn and {Microsoft Access Driver}
version 4.00.6305..00 and SQL := 'SELECT * FROM Customer'.

The DBgrid get populated at design time without any other quirk that
adding fields manually to the sqlquery (edit: and setting components
needed options as  Datasource/Dataset/active of course). Without I get
an error.

----------------------------------------------------------------------
snesska · 2010-04-08 19:00

Thank you for your quick answer. Unfortunately, I can't try that until
now (I was on the trip), and now I must solve the problem...

Here is my situation:
I started from the begining:

I created SystemDSN named "zabazu2" with driver
"Microsoft Access Driver (*.mdb)"


Components properties are:

ODBCConnection1
DatabaseName:= zabazu2

SQLQuery1
Database:=ODBCConnection1
Datasource:=Datasource1
SQL:=select * from Table1


SQLTransaction1
Database:=ODBCConnection1

Datasource1
DataSet:=SQLQuery1

DBGrid1
Datasource:=Datasource1


When I set connected to True nothing happens.
When I set Query1.Active to True in ObjectInspector there is an error:
"Transaction not set.

When I start the application i get a message like one in the
attachment

I try a little variation of these settings  but nothing helps...

----------------------------------------------------------------------
thierrybo · 2010-04-08 21:45

Hi,

I think this is your tsqlquery datasource property, it should be blank
(see picture of what works (used a file odbc source)

----------------------------------------------------------------------
snesska · 2010-04-08 22:23

YESSSSSSSSS!!! I works! At last! After two weeks of agony...
Thank you Zoran and  thierrybo!

You should use either SystemDNS or FileDNS, not both.
&
tsqlquery datasource property, it should be blank

is a winning combination!

----------------------------------------------------------------------
snesska · 2010-04-09 10:18

Hi, it's me, again    
In run-time I edited some records shown in DBGrid.
I "post-it" with a button in navigation bar, but there are no changes
in the database!
What must be done to "tell lazarus" to make changes in the *mdb file?

----------------------------------------------------------------------
thierrybo · 2010-04-09 11:02

Read http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Cached_
Updates

all changes are "buffered" and ou need to  manually send them to the
database.

You can do this in  the "afterscroll" event of TSqlquery, data will be
sent when when you change row. Or in "afterpost" event if you
absolutely need to send changes as soon as you enter data in a cell.

----------------------------------------------------------------------
snesska · 2010-04-09 22:52

Thank you!
That was very helpful!
If someone have the same problem - these two links helps me a lot to
finish this:

http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial
http://bugs.freepascal.org/view.php?id=13241

At last, syntax for that flags...

SQLQuery1.Fields.Fields[0].ProviderFlags:=[pfInkey]

----------------------------------------------------------------------
thierrybo · 2010-04-10 10:22

interesting you also need to add the mars connection ODBC property to
'yes', I use it for MSQqlserver, but I believed it was only for this
database.

----------------------------------------------------------------------
snesska · 2010-04-14 06:35

bad news...
I try to do the same thing on 10 computers at school (i am a teacher
), but on every computer when I try to set SQLQuery1.Active to "true"
i get a message "Access violation"!
Any idea?

----------------------------------------------------------------------
Wodzu · 2010-04-14 07:05

It would be good if you could describe your problem with more detail.
Perhaps attach a test project to your post?

----------------------------------------------------------------------
snesska · 2010-04-14 07:11

I set everything what is said on tihis forum, and I describe that on
http://www.e-pismen.com/mod/lesson/view.php?id=168&pageid=79
(Log in as a guest)
It works on my computer at home, but not work at school!

----------------------------------------------------------------------
Zoran · 2010-04-14 17:47

I followed the images on your site and it works here... I just can't
tell why it doesn't work in school...
Might be something with the permissions on the school's computers?!

----------------------------------------------------------------------
snesska · 2010-04-14 18:36

Thank you for your time and  your answer. It gives me a hope that my
exlpanation is not so bad...
But, I am admin at school, this not works neither on my nor on
students computer.
One student wrote to me, that it is not working on his computer at
home  too...

Maybe it is something with ODBC driver manager?

Maybe I have done something wrong while I was trying to setup ODBS
driver – which is setup at your system but not on school computers –
at the moment I have nothing on my mind....

----------------------------------------------------------------------
snesska · 2010-04-18 19:59

Well, that problem iz solved -  on school's computers was older
version of Lazarus, (thank's to Zoran), but now...

For updating database, I put a Button vith procedure:

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Fields.Fields[0].ProviderFlags:=[pfInkey];
  SQLQuery1.ApplyUpdates;
end;

Update iz working good, but insert and delete causes message:
Project raIsed exception class 'External: SIGSEGV'.

What is this?

----------------------------------------------------------------------
asdf · 2012-11-03 11:19

Dear All,

How can I add 'file.dsn' into codes?

In http://wiki.lazarus.freepascal.org/MS_Access and  sample project
from http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip

There is a separate file namely 'file.dsn' but I would like to write
it in codes and do not need to drag many controls(TODBCConnection,
TSQLTransaction and TSQLQuery) onto form.

And another question, now I use MS Access 2010. The extention is
'.accdb', not '.mdb' . How should I write codes for those ?

My codes (it does not work)...

Code: [Select]implementation

{$R *.lfm}

{ TForm1 }

var
  cn : TODBCConnection;
  sq : TSQLQuery;
  tr : TSQLTransaction;

procedure TForm1.FormCreate(Sender: TObject);

begin

  // Set up
  cn := TODBCConnection.Create(nil);
  tr := TSQLTransaction.Create(nil);
  tr.DataBase := cn;
  tr.Action   := caCommit;

  cn.Transaction := tr;

  sq := TSQLQuery.Create(nil);
  sq.DataBase:= cn;
  sq.Transaction := tr;

  sq.UsePrimaryKeyAsKey:=false;

  cn.Driver := 'Microsoft Access Driver (*.accdb)';
  cn.Params.Add('DBQ=D:\KTH.accdb');

  cn.CloseDataSets;

  sq.SQL.Clear;
  sq.sql.text:='select FSpersonnelID from FromFingerScan';
  sq.open;

  while not sq.EOF do begin
  RadioGroup1.Items.Add(trim(uppercase(sq.FieldByName('FSpersonnelID')
.Text)));
  sq.Next;
  end;

  sq.close

----------------------------------------------------------------------
anis2505 · 2012-11-03 20:40

Quote from: asdf on November 03, 2012, 12:19:04 pmDear All,

How can I add 'file.dsn' into codes?

In http://wiki.lazarus.freepascal.org/MS_Access and  sample project
from http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip

There is a separate file namely 'file.dsn' but I would like to write
it in codes and do not need to drag many controls(TODBCConnection,
TSQLTransaction and TSQLQuery) onto form.

And another question, now I use MS Access 2010. The extention is
'.accdb', not '.mdb' . How should I write codes for those ?

My codes (it does not work)...

Code: [Select]implementation

{$R *.lfm}

{ TForm1 }

var
  cn : TODBCConnection;
  sq : TSQLQuery;
  tr : TSQLTransaction;

procedure TForm1.FormCreate(Sender: TObject);

begin

  // Set up
  cn := TODBCConnection.Create(nil);
  tr := TSQLTransaction.Create(nil);
  tr.DataBase := cn;
  tr.Action   := caCommit;

  cn.Transaction := tr;

  sq := TSQLQuery.Create(nil);
  sq.DataBase:= cn;
  sq.Transaction := tr;

  sq.UsePrimaryKeyAsKey:=false;

  cn.Driver := 'Microsoft Access Driver (*.accdb)';
  cn.Params.Add('DBQ=D:\KTH.accdb');

  cn.CloseDataSets;

  sq.SQL.Clear;
  sq.sql.text:='select FSpersonnelID from FromFingerScan';
  sq.open;

  while not sq.EOF do begin
  RadioGroup1.Items.Add(trim(uppercase(sq.FieldByName('FSpersonnelID')
.Text)));
  sq.Next;
  end;

  sq.close
                        



Hi,

I'm using MS Access 2010 with accdb extention

I'm using controls it works for me all I do is on the property fileDSN
of the ODBCConnection control i provide the FULL PATH
(X:\...\file.dsn)

nothing more I don't provide the driver or anything else just the full
file path of the dsn file

found this too

http://wiki.lazarus.freepascal.org/MS_Access

------------------------------
Lazarus #1.0.2 on Windows 7 64 bits

----------------------------------------------------------------------
asdf · 2012-11-03 23:33

Thank you,
but what I need is moving all codes inside 'file.dsn' and write in
code of project.
Not like this...
Code: [Select]  //connection
 ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) +
'file.dsn';

----------------------------------------------------------------------
asdf · 2012-11-04 11:04

Only AnyDAC -> http://www.da-soft.com/anydac/ can do. 


Finally after reading in Lazarus wiki, I have to create '.dsn'.

Are there any other non-commercial components equivalent to AnyDAC ?

----------------------------------------------------------------------
BigChimp · 2012-11-04 13:25

I'm a bit unclear about what you did. Did you try entering the ODBC
details to create a DSN-less connection?
http://wiki.lazarus.freepascal.org/ODBCConn#Without_a_DSN

Did you get error messages then?

----------------------------------------------------------------------
asdf · 2012-11-04 15:45

ํYes, what I only need is dsn-less.

I tried

Code: [Select]  sq.UsePrimaryKeyAsKey:=false;
  cn.Driver := 'Microsoft Access Driver (*.accdb)';
  cn.Params.Add('DBQ=D:\KTH.accdb')

But error.

----------------------------------------------------------------------
BigChimp · 2012-11-04 16:30

Ok. 2 ideas (haven't used accdb myself though)
1. Make sure the .accdb driver supports the DBQ=bla notation for
specifying the database
2. If you haven't already: make sure you have a 64 bit accdb ODBC
driver if compiling a 64 bit program and a 32 bit ODBC driver if
compiling a 32 bit program.

----------------------------------------------------------------------
BigChimp · 2012-11-04 16:37

Also, make sure the driver name you use in cn.Driver is correct. I
could only find this example:
http://www.connectionstrings.com/access-2007#microsoft-access-accdb-
odbc-driver

----------------------------------------------------------------------
anis2505 · 2012-11-04 19:37

Hi,

I used the code below and it works fine

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;

----------------------------------------------------------------------
asdf · 2012-11-05 03:04

Today I tried DSN-less again, but it worked. I am so   now.

Thank you for all kind suggestions from all of you.

Maybe I placed codes in wrong lines or wrong sequence, or misspelled
the data field name or table name.

----------------------------------------------------------------------
Goodman H__ · 2012-11-12 08:47

Quote from: anis2505 on November 04, 2012, 08:37:36 pmHi,

I used the code below and it works fine

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;    

Sorry for my stepping in but I've encountered the same issue.
Are you all talking about the dsn-less connection via ODBC with Access
database (*.mdb)?With the above connection code,it still failed to
connect to an access database if dsn is not defined in ODBC source
panel a 'database =' property is not provided in IDE.

All I want is just to connect an access database (*.mdb) with sqlDB
component(TODBCconnection,QSqlTransaction,TSqlQuery...),the most
important point is there is NO need to set up a dsn in the ODBC Source
panel!Something like in C# below code is sufficient to connect to an
access *.mdb database:
Code: [Select]OleDbConnection nwindConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\mydatabase.mdb;")

Solution would be appreciated.

Regards,
Sam

----------------------------------------------------------------------
anis2505 · 2012-11-12 10:55

Quote from: Goodman H__ on November 12, 2012, 09:47:28 amQuote from:
anis2505 on November 04, 2012, 08:37:36 pmHi,

I used the code below and it works fine

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;    

Sorry for my stepping in but I've encountered the same issue.
Are you all talking about the dsn-less connection via ODBC with Access
database (*.mdb)?With the above connection code,it still failed to
connect to an access database if dsn is not defined in ODBC source
panel a 'database =' property is not provided in IDE.

All I want is just to connect an access database (*.mdb) with sqlDB
component(TODBCconnection,QSqlTransaction,TSqlQuery...),the most
important point is there is NO need to set up a dsn in the ODBC Source
panel!Something like in C# below code is sufficient to connect to an
access *.mdb database:
Code: [Select]OleDbConnection nwindConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\mydatabase.mdb;")

Solution would be appreciated.

Regards,
Sam

Hi,

The C# code means this
For the database driver
Code: [Select]Provider=Microsoft.Jet.OLEDB.4.0;
Your database
Code: [Select]Data Source=C:\mydatabase.mdb;


It's the same here

you set the driver like this:
Code: [Select]cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';

The database like this
Code: [Select]cn.Params.Add('DBQ=C:\mydatabase.mdb');

I hope that will help

----------------------------------------------------------------------
anis2505 · 2012-11-12 11:01

Quote from: asdf on November 05, 2012, 04:04:18 amToday I tried DSN-
less again, but it worked. I am so   now.

Thank you for all kind suggestions from all of you.

Maybe I placed codes in wrong lines or wrong sequence, or misspelled
the data field name or table name.



Hi,
Could you provide the code you used.

Thanks in advance.

----------------------------------------------------------------------
BigChimp · 2012-11-12 11:42

Note: I'm sure people would appreciate an update of
http://wiki.lazarus.freepascal.org/ODBCConn#Connecting_to_MS_Access
... and/or a rewrite of
http://wiki.lazarus.freepascal.org/MS_Access
... which seems a bit well... one sided at least (focus on File-based
DSN connection)

----------------------------------------------------------------------
anis2505 · 2012-11-12 13:48

Quote from: BigChimp on November 12, 2012, 12:42:30 pmNote: I'm sure
people would appreciate an update of
http://wiki.lazarus.freepascal.org/ODBCConn#Connecting_to_MS_Access
... and/or a rewrite of
http://wiki.lazarus.freepascal.org/MS_Access
... which seems a bit well... one sided at least (focus on File-based
DSN connection)

GREAT THANKS

----------------------------------------------------------------------
asdf · 2012-11-13 04:24

in MS Access database, there is a field and its data type is
'Date/time'.

Code: [Select]  sq.SQL.Text := 'select FSDT from TimeTable order by
FSDT'; // FSDT is a field of Date/Time data type
  sq.Open;
  stringgrid2.RowCount:=sq.RecordCount+1;

    while not sq.EOF do
      begin
        stringgrid2.Cells[1,sq.RecNo]:=sq.FieldByName('FSDT').Text;
        sq.Next;
      end;
  sq.Free;
  cn.Free;

It crashed, and could you please teach me how to design the database
upon Date/Time type ?
And what is the correct sql statement for Date/Time type field ?

----------------------------------------------------------------------
BigChimp · 2012-11-13 08:31

The SQL looks fine.

What do you mean by "crash" exactly? SIGSEGV? Blue screen? Non-
responding application?
What is your Lazarus version+FPC version?

If I were troubleshooting, I'd first test:
- using  sq.FieldByName('FSDT').AsString instead of .Text (no idea if
.Text should work)
- then try to get the field contents into a date/time field, using
something like sq.FieldByName('FSDT').AsDateTime (or similar function)

----------------------------------------------------------------------
anis2505 · 2012-11-13 09:34

Quote from: asdf on November 13, 2012, 05:24:32 amin MS Access
database, there is a field and its data type is 'Date/time'.

Code: [Select]  sq.SQL.Text := 'select FSDT from TimeTable order by
FSDT'; // FSDT is a field of Date/Time data type
  sq.Open;
  stringgrid2.RowCount:=sq.RecordCount+1;

    while not sq.EOF do
      begin
        stringgrid2.Cells[1,sq.RecNo]:=sq.FieldByName('FSDT').Text;
        sq.Next;
      end;
  sq.Free;
  cn.Free;

It crashed, and could you please teach me how to design the database
upon Date/Time type ?
And what is the correct sql statement for Date/Time type field ?

Hi,

parse it to datetime and you are ready to go
Code: [Select]StringGrid2.Cells[1,sq.RecNo]:=DateTimeToStr(sq.FieldByN
ame('FSDT').AsDateTime);

----------------------------------------------------------------------
asdf · 2012-11-13 11:34

Sorry for my misunderstanding   ,
the problem is overhere...
Code: [Select]
stringgrid2.Cells[1,sq.RecNo]:=sq.FieldByName('FSDT').Text;
sq.RecordCount gave 10.
but number of records in table is 32.
So, the row number of stringgrid2 is not enough.
What is the correct way/code to count the record of 'sq' ?

----------------------------------------------------------------------
BigChimp · 2012-11-13 12:13

Mmm... count number of records.... Something like recordcount maybe?

See http://wiki.lazarus.freepascal.org/SqlDBHowto#Why_does_TSQLQuery.R
ecordCount_always_return_10.3F
Edit: which explains why recordcount is sometimes not precise. It also
gives a solution to get a precise count. anis2505's suggestion below
could also work of course...

Perhaps it's a good idea to have a look through the wiki - there's a
lot of database-related information out there.

Because sqldb is database-independent, a lot of the info on using
sqldb with other databases (postgresql, mysql etc) also applies to
using sqldb with ODBC (Access)

----------------------------------------------------------------------
anis2505 · 2012-11-13 12:56

Quote from: asdf on November 13, 2012, 12:34:47 pmSorry for my
misunderstanding   ,
the problem is overhere...
Code: [Select]
stringgrid2.Cells[1,sq.RecNo]:=sq.FieldByName('FSDT').Text;
sq.RecordCount gave 10.
but number of records in table is 32.
So, the row number of stringgrid2 is not enough.
What is the correct way/code to count the record of 'sq' ?

to me SQ1.RecordCount give me the right number of record

I know this is ugly but why don't you store the row count on an sql
value
Code: [Select]sq.SQL.Text := 'select count(idTable) as RowCount, FSDT
from TimeTable order by FSDT';

----------------------------------------------------------------------
asdf · 2012-11-14 01:04

Quote from: BigChimp on November 13, 2012, 01:13:49 pmSee http://wiki.
lazarus.freepascal.org/SqlDBHowto#Why_does_TSQLQuery.RecordCount_alway
s_return_10.3F

Thank you so much   .

----------------------------------------------------------------------
BigChimp · 2012-11-14 08:33

My pleasure.

Sorry I was a bit rude... too many people asking for the same simple
things, though your question was actually not simple/didn't have an
easy to find answer.

----------------------------------------------------------------------
Goodman H__ · 2012-11-14 11:17

Quote from: anis2505 on November 12, 2012, 11:55:08 amQuote from:
Goodman H__ on November 12, 2012, 09:47:28 amQuote from: anis2505 on
November 04, 2012, 08:37:36 pmHi,

I used the code below and it works fine

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;    

Sorry for my stepping in but I've encountered the same issue.
...
Code: [Select]OleDbConnection nwindConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\mydatabase.mdb;")
...
Solution would be appreciated.

Regards,
Sam

Hi,

The C# code means this
For the database driver
Code: [Select]Provider=Microsoft.Jet.OLEDB.4.0;
Your database
Code: [Select]Data Source=C:\mydatabase.mdb;


It's the same here

you set the driver like this:
Code: [Select]cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';

The database like this
Code: [Select]cn.Params.Add('DBQ=C:\mydatabase.mdb');

I hope that will help

I am sorry but...
What I am trying to say is that with only below listed codes ,and
without defining a DSN in the ODBC Administration Panel,I FAILED to
connect to an MS Access Database!
Code: [Select]cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
cn.Params.Add('DBQ=C:\mydatabase.mdb');
Provided if there is a defined DSN named artistsDB via ODBC to MS
Access Database file c:\my\database\article.mdb,adding below code
Code: [Select]databaseName:=artistDB;
and then the connection works great,the dbgrid will show the data and
the dbnavigator will navigate the record...but if I commented the last
line ,program will still run but exception's thrown immediately due to
the fail to connect to Access database.

Hope this make it clearer and anybody who've already successfully
connected MS Access Database via ODBC WITHOUT DSN help me.Thank you so
much!!!

----------------------------------------------------------------------
anis2505 · 2012-11-14 12:44

Quote from: Goodman H__ on November 14, 2012, 12:17:18 pmQuote from:
anis2505 on November 12, 2012, 11:55:08 amQuote from: Goodman H__ on
November 12, 2012, 09:47:28 amQuote from: anis2505 on November 04,
2012, 08:37:36 pmHi,

I used the code below and it works fine

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  con.Password:='aaaa';
  con.UserName:='admin';
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;    

Sorry for my stepping in but I've encountered the same issue.
...
Code: [Select]OleDbConnection nwindConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\mydatabase.mdb;")
...
Solution would be appreciated.

Regards,
Sam

Hi,

The C# code means this
For the database driver
Code: [Select]Provider=Microsoft.Jet.OLEDB.4.0;
Your database
Code: [Select]Data Source=C:\mydatabase.mdb;


It's the same here

you set the driver like this:
Code: [Select]cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';

The database like this
Code: [Select]cn.Params.Add('DBQ=C:\mydatabase.mdb');

I hope that will help

I am sorry but...
What I am trying to say is that with only below listed codes ,and
without defining a DSN in the ODBC Administration Panel,I FAILED to
connect to an MS Access Database!
Code: [Select]cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
cn.Params.Add('DBQ=C:\mydatabase.mdb');
Provided if there is a defined DSN named artistsDB via ODBC to MS
Access Database file c:\my\database\article.mdb,adding below code
Code: [Select]databaseName:=artistDB;
and then the connection works great,the dbgrid will show the data and
the dbnavigator will navigate the record...but if I commented the last
line ,program will still run but exception's thrown immediately due to
the fail to connect to Access database.

Hope this make it clearer and anybody who've already successfully
connected MS Access Database via ODBC WITHOUT DSN help me.Thank you so
much!!!


Hi,

I think your database is protected by passoword. if so the code will
be like this

Code: [Select] cn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  cn.Params.Add('DBQ='+ExtractFilePath(Application.ExeName) +
'yourdb.accdb');
  cn.UserName:='admin'; 
  cn.Password:='your-database-password';
  cn.Connected:=True;
  cn.KeepConnection:=True;
  SQLQuery1.SQL.Text:='SELECT * FROM table1';
  SQLQuery1.Open;    

regards

----------------------------------------------------------------------
Goodman H__ · 2012-11-14 14:06

No,my database have no password actually.
Error message attached.

Thanks for the help.

----------------------------------------------------------------------
anis2505 · 2012-11-14 15:00

Quote from: Goodman H__ on November 14, 2012, 03:06:17 pmNo,my
database have no password actually.
Error message attached.

Thanks for the help.

hi,

Please check your database path on the cn.Params.add its  c:\persone\*
NOT c:person\*

regards

----------------------------------------------------------------------
Goodman H__ · 2012-11-15 00:28

Quote from: anis2505 on November 14, 2012, 04:00:22 pmQuote from:
Goodman H__ on November 14, 2012, 03:06:17 pmNo,my database have no
password actually.
Error message attached.

Thanks for the help.

hi,

Please check your database path on the cn.Params.add its  c:\persone\*
NOT c:person\*

regards
Thanks for the tips.However it seems this is not the root caused the
problem after testing.
Don't quite know what `can't find a '=' in '' ` means.

Would anybody please upload a short but complete GUI example that
communicating with Access Database via ODBC, DSNless?Appreciated.

----------------------------------------------------------------------
anis2505 · 2012-11-15 10:25

Quote from: Goodman H__ on November 15, 2012, 01:28:27 amQuote from:
anis2505 on November 14, 2012, 04:00:22 pmQuote from: Goodman H__ on
November 14, 2012, 03:06:17 pmNo,my database have no password
actually.
Error message attached.

Thanks for the help.

hi,

Please check your database path on the cn.Params.add its  c:\persone\*
NOT c:person\*

regards
Thanks for the tips.However it seems this is not the root caused the
problem after testing.
Don't quite know what `can't find a '=' in '' ` means.

Would anybody please upload a short but complete GUI example that
communicating with Access Database via ODBC, DSNless?Appreciated.

Hi,

here is an example of dealing with access dbs

http://www.4shared.com/zip/o438PqFF/testaccess.html

regards

----------------------------------------------------------------------
Goodman H__ · 2012-11-16 02:31

I appreciated the file you've uploaded and tried to download,but ..I
can't.Sorry,even registered as a member and installed the client
desktop software,still ,no.

I noticed the file you've uploaded (*.zip) is just several hundred kb
in size.So would you please upload here as attachment?The menu to add
an attachment is just beneath the reply textbox.

Thank you very much !!

----------------------------------------------------------------------
asdf · 2012-11-16 04:39

Code: [Select]var
  FSDateTimeS: TDateTime;
   FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS: string;.
...
sq.sql.text:='insert into '
  + 'fromFingerScanner'
  + '(FSDateTime, FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4,
FSStatus, FSSource)'
  + ' values('
  + datetimetostr(FSDateTimeS)  + ', '
  + FSFingerIDS  + ', '
  + FSOther1S  + ', '
  + FSOther2S  + ', '
  + FSOther3S  + ', '
  + FSOther4S  + ', '
  + FSStatusS  + ', '
  + FSSourceS  + ')';

  sq.ExecSQL;
  tr.CommitRetaining;
 
The error is as attached, what is the problem in 'sq.sql.text' ?

----------------------------------------------------------------------
BigChimp · 2012-11-16 07:56

Sorry to rain on your parade, hope these comments help...

My crystal ball says the problem is you're trying to assing a string
to a date/time column??!?
IIRC, Access uses #YYYY-MM-DD# notation for dates (replace Y, M and D
with year/month/date numbers)
Edit: however, your query is going through ODBC as well. No idea if
you have to format dates differently in that layer. Solution:
parameterized queries.

Also, you need to quote strings in SQL with single quotes. Don't
forget to escape single quotes in the actual value with another single
quote, or use QuoteStr.

And yes, you can much better use parameterized queries to avoid the
entire problem with how to format dates and strings.

Why don't you print out your sql string before running it and try
running it in an Access query window to see if the syntax is ok?

As for your field names: I presume you're using a fixed Access
database delivered to you by some supplier? Otherwise I would
definitely at least change field names like FSOther1, FSOther2... This
smells of a denormalized design to me, so perhaps extra tables may
need to be made.

As to whether this error should give a SIGSEGV? That seems a bit
extreme!?!? However, there's enough errors in your SQL to warrant
fixing that first.

----------------------------------------------------------------------
asdf · 2012-11-16 09:33

Code: [Select]var
  FSDateTimeS: string;
  FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS: string;.
...
sq.sql.text:='insert into '
  + 'fromFingerScanner'
  + '(FSDateTime, FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4,
FSStatus, FSSource)'
  + ' values('
  + '#'
  + FSDateTimeS + '#'  + ', '
  + quotedstr(FSFingerIDS)  + ', '
  + quotedstr(FSOther1S)  + ', '
  + quotedstr(FSOther2S)  + ', '
  + quotedstr(FSOther3S)  + ', '
  + quotedstr(FSOther4S)  + ', '
  + quotedstr(FSStatusS)  + ', '
  + quotedstr(FSSourceS)  + ')';

  sq.ExecSQL;
  tr.CommitRetaining;
  sq.SQL.Clear;

The problem is still the same   .

----------------------------------------------------------------------
BigChimp · 2012-11-16 09:43

Quote from: asdf on November 16, 2012, 10:33:03 amThe problem is still
the same   .

QuoteWhy don't you print out your sql string before running it and try
running it in an Access query window to see if the syntax is ok?
And? Did the generated SQL run in the Access query window when you
copied the SQL text and pasted it there? That will at least eliminate
errors with spelling of column names, quoting etc.

----------------------------------------------------------------------
Nebula · 2012-11-16 09:46

Add this after sq.sql.text is set .....
showmessage(sq.sql.text);
and see if you can spot what's wrong, else show us

----------------------------------------------------------------------
BigChimp · 2012-11-16 09:47

Quote from: Nebula on November 16, 2012, 10:46:44 amAdd this after
sq.sql.text is set .....
showmessage(sq.sql.text);
and see if you can spot what's wrong, else show us 
Better yet, output sq.sql.text to an edit box or memo, copy the text
and paste it in an Access query window to see if Access throws an
error or not as I suggested 2 times already.
Sigh.

----------------------------------------------------------------------
anis2505 · 2012-11-16 10:23

Quote from: asdf on November 16, 2012, 10:33:03 amCode: [Select]var
  FSDateTimeS: string;
  FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS: string;.
...
sq.sql.text:='insert into '
  + 'fromFingerScanner'
  + '(FSDateTime, FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4,
FSStatus, FSSource)'
  + ' values('
  + '#'
  + FSDateTimeS + '#'  + ', '
  + quotedstr(FSFingerIDS)  + ', '
  + quotedstr(FSOther1S)  + ', '
  + quotedstr(FSOther2S)  + ', '
  + quotedstr(FSOther3S)  + ', '
  + quotedstr(FSOther4S)  + ', '
  + quotedstr(FSStatusS)  + ', '
  + quotedstr(FSSourceS)  + ')';

  sq.ExecSQL;
  tr.CommitRetaining;
  sq.SQL.Clear;

The problem is still the same   .

Why don't you use parametred sql query. I think you should use
parametred queries.

Quote
I appreciated the file you've uploaded and tried to download,but ..I
can't.Sorry,even registered as a member and installed the client
desktop software,still ,no.

I noticed the file you've uploaded (*.zip) is just several hundred kb
in size.So would you please upload here as attachment?The menu to add
an attachment is just beneath the reply textbox.

Thank you very much !!


I have uploaded the file on UploadHero

http://uploadhero.com/dl/jq8Thret

Hope that works now. anyway it's just contains how to connect to MS
Access DB and to use dbnavs to manage it

regards

----------------------------------------------------------------------
BigChimp · 2012-11-16 14:22

Quote from: asdf on November 16, 2012, 10:33:03 amCode: [Select]var
  FSDateTimeS: string;
  FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS: string;.
...
sq.sql.text:='insert into '
  + 'fromFingerScanner'
  + '(FSDateTime, FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4,
FSStatus, FSSource)'
  + ' values('
  + '#'
  + FSDateTimeS + '#'  + ', '
  + quotedstr(FSFingerIDS)  + ', '
  + quotedstr(FSOther1S)  + ', '
  + quotedstr(FSOther2S)  + ', '
  + quotedstr(FSOther3S)  + ', '
  + quotedstr(FSOther4S)  + ', '
  + quotedstr(FSStatusS)  + ', '
  + quotedstr(FSSourceS)  + ')';
Quote from: BigChimpMy crystal ball says the problem is you're trying
to assing assign a string to a date/time column??!?
Oh by the way, when I talked about crystal ball earlier, I did that
for a reason. I have no idea what your column data types are, we can
only guess!?!
Is FSDateTime really a date/time field? Or is it a varchar/text field?
Or something else?
For that matter is FSDateTimeS formatted correctly in the format the
Access ODBC driver expects it? (hint: parameterized queries, example
in http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_qu
eries....)

----------------------------------------------------------------------
taazz · 2012-11-16 16:04

Quote from: asdf on November 16, 2012, 05:39:55 amCode: [Select]var
  FSDateTimeS: TDateTime;
   FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS: string;.
...
sq.sql.text:='insert into '
  + 'fromFingerScanner'
  + '(FSDateTime, FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4,
FSStatus, FSSource)'
  + ' values('
  + datetimetostr(FSDateTimeS)  + ', '
  + FSFingerIDS  + ', '
  + FSOther1S  + ', '
  + FSOther2S  + ', '
  + FSOther3S  + ', '
  + FSOther4S  + ', '
  + FSStatusS  + ', '
  + FSSourceS  + ')';

  sq.ExecSQL;
  tr.CommitRetaining;
 
The error is as attached, what is the problem in 'sq.sql.text' ?

there is no problem in the command you are showing us. from the error
I would guess that FSSourceS  variable has been corrupted for some
reason and that leads to a sigsegv, you probably have a memory
overflow on the lines you have cut out from the routine before you set
the sql.sql.text variable.

Try to assign constant values to the string variables instead of the
logic that now exists, if that solves the problem then your assigning
processes have overflow problems.

----------------------------------------------------------------------
asdf · 2012-11-17 01:38

Code: [Select]var
 
  FSDateTimeS: TDateTime;
...

ln:='[2011/09/05-05:49:21]1002/1/4/0/0';

FSDateTimeS:=EncodeDateTime(strtoint(copy(ln,2,4)),
                                   strtoint(copy(ln,7,2)),
                                   strtoint(copy(ln,10,2)),
                                   strtoint(copy(ln,13,2)),
                                   strtoint(copy(ln,16,2)),
                                   strtoint(copy(ln,19,2)),
                                   0);

 sq.sql.text:='insert into fromFingerScanner(FSDateTime, FSFingerID,
FSOther1, FSOther2, FSOther3, FSOther4, FSStatus, FSSource)'
              + ' values(FSDateTimeS, FSFingerIDS, FSOther1S,
FSOther2S, FSOther3S, FSOther4S, FSStatusS, FSSourceS)';

  sq.Params.ParamByName('FSDateTime').AsDateTime:=FSDateTimeS;
  sq.Params.ParamByName('FSFingerID').AsString:=FSFingerIDS;
  sq.Params.ParamByName('FSOther1').AsString:=FSOther1S;
  sq.Params.ParamByName('FSOther2').AsString:=FSOther2S;
  sq.Params.ParamByName('FSOther3').AsString:=FSOther3S;
  sq.Params.ParamByName('FSOther4').AsString:=FSOther4S;
  sq.Params.ParamByName('FSStatus').AsString:=FSStatusS;
  sq.Params.ParamByName('FSSource').AsString:=FSSourceS;

The problem is as attached hereunder (--")

----------------------------------------------------------------------
taazz · 2012-11-17 03:15

Code: [Select]var
  FSDateTimeS: TDateTime;
  FSFingerIDS, FSOther1S, FSOther2S, FSOther3S, FSOther4S, FSStatusS,
FSSourceS :string;
begin
  ln:='[2011/09/05-05:49:21]1002/1/4/0/0';

  FSDateTimeS:=EncodeDateTime(strtoint(copy(ln,2,4)),
                                   strtoint(copy(ln,7,2)),
                                   strtoint(copy(ln,10,2)),
                                   strtoint(copy(ln,13,2)),
                                   strtoint(copy(ln,16,2)),
                                   strtoint(copy(ln,19,2)),
                                   0);

  FSFingerIDS := 'null';
  FSOther1S := 'null';
  FSOther2S := 'null';
  FSOther3S := 'null';
  FSOther4S := 'null';
  FSStatusS := 'null';
  FSSourceS  := 'null';

  sq.sql.text:='insert into fromFingerScanner(FSDateTime, FSFingerID,
FSOther1, FSOther2, 
                                                                     
 FSOther3, FSOther4, FSStatus, FSSource)'
                      + ' values('+FSDateTimeS+', '
                                       +FSFingerIDS+', '
                                       +FSOther1S+', '
                                       +FSOther2S+', '
                                       +FSOther3S+', '
                                       +FSOther4S+', '
                                       +FSStatusS+', '
                                       +FSSourceS+')';


end;


Try this one do not change anything just make sure that sq.sql.text
gets the correct string if it does and no error is raised then try to
find out which peace of code creates the problem by comment/un-comment
various parts of your routine and see what goes on.

----------------------------------------------------------------------
anis2505 · 2012-11-17 08:39

Quote from: asdf on November 17, 2012, 02:38:32 amCode: [Select]var
 
  FSDateTimeS: TDateTime;
...

ln:='[2011/09/05-05:49:21]1002/1/4/0/0';

FSDateTimeS:=EncodeDateTime(strtoint(copy(ln,2,4)),
                                   strtoint(copy(ln,7,2)),
                                   strtoint(copy(ln,10,2)),
                                   strtoint(copy(ln,13,2)),
                                   strtoint(copy(ln,16,2)),
                                   strtoint(copy(ln,19,2)),
                                   0);

 sq.sql.text:='insert into fromFingerScanner(FSDateTime, FSFingerID,
FSOther1, FSOther2, FSOther3, FSOther4, FSStatus, FSSource)'
              + ' values(FSDateTimeS, FSFingerIDS, FSOther1S,
FSOther2S, FSOther3S, FSOther4S, FSStatusS, FSSourceS)';

  sq.Params.ParamByName('FSDateTime').AsDateTime:=FSDateTimeS;
  sq.Params.ParamByName('FSFingerID').AsString:=FSFingerIDS;
  sq.Params.ParamByName('FSOther1').AsString:=FSOther1S;
  sq.Params.ParamByName('FSOther2').AsString:=FSOther2S;
  sq.Params.ParamByName('FSOther3').AsString:=FSOther3S;
  sq.Params.ParamByName('FSOther4').AsString:=FSOther4S;
  sq.Params.ParamByName('FSStatus').AsString:=FSStatusS;
  sq.Params.ParamByName('FSSource').AsString:=FSSourceS;

The problem is as attached hereunder (--")

Hi,
You didn't montioned the parameter on the sql query a parametre looks
like this :para_name

Code: [Select]sq.sql.text:='insert into fromFingerScanner(FSDateTime,
FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4, FSStatus,
FSSource)'
              + ' values(:FSDateTimeS, :FSFingerIDS, :FSOther1S,
:FSOther2S, :FSOther3S, :FSOther4S, :FSStatusS, :FSSourceS)';

Besides a line of code has a maximum length try to wrap lines even on
variable declaration

regards

----------------------------------------------------------------------
BigChimp · 2012-11-17 14:46

Not going to bother posting my suggestion another time because you
won't follow that advice anyway[1]

Anyway, thanks for showing the field types, that does help.

[1] If you do want to know what I think you should do to troubleshoot
this, try to read my posts, then figure out what you did and you will
see you left something (that somebody else told you about as well).
Now I think about it, there may be 2 things both I and somebody else
said. Anyway, I still think these 2 things are good ideas

----------------------------------------------------------------------
asdf · 2012-11-17 17:44

Code: [Select] sq.sql.text:='insert into fromFingerScanner(FSDateTime,
FSFingerID, FSOther1, FSOther2, FSOther3, FSOther4, FSStatus,
FSSource)'
              + ' values(:FSDateTimeS, :FSFingerIDS, :FSOther1S,
:FSOther2S, :FSOther3S, :FSOther4S, :FSStatusS, :FSSourceS)';

Still error (--")

----------------------------------------------------------------------
BigChimp · 2012-11-17 17:50

Quote from: asdf on November 17, 2012, 06:44:32 pmCode: [Select]
sq.sql.text:='insert into fromFingerScanner(FSDateTime, FSFingerID,
FSOther1, FSOther2, FSOther3, FSOther4, FSStatus, FSSource)'
              + ' values(:FSDateTimeS, :FSFingerIDS, :FSOther1S,
:FSOther2S, :FSOther3S, :FSOther4S, :FSStatusS, :FSSourceS)';

Still error (--")                                                    
                          
Ok. Good, parameterized queries.
Great.

No idea what the rest of your code looks like though (e.g. parameter
assignment) etc
At this point, I think it is easiest if you please upload a small
sample Lazarus project + mdb file that shows the problem.

If the problem is in the code, we can all try to fix it and upload the
fixed version.

If the problem is somewhere else, the project can serve as a test in
the bug report.

----------------------------------------------------------------------
Nebula · 2012-11-17 18:37

Have your variables got valid values? That's why we need to see what
the actual SQL is.

----------------------------------------------------------------------
asdf · 2012-11-18 08:47

Thank you so much for all my great teachers,
help me please   and please see the project as attached herewith.

----------------------------------------------------------------------
BigChimp · 2012-11-18 09:04

Having the .lfm, .lpr, and lpi files would have been nice but ok I'll
try and recreate the project.

Edit: one small hint: you have
Code: [Select]procedure TForm1.MenuItem_ExitClick(Sender: TObject);
begin
  sq.Free;
  tr.Free;
  cn.Free;
end;
You could put that code in the Form's OnDestroy event instead. IIUC,
if you press Alt-F4 or the close button at the top of the form, the
menu code will not run and you'll have a memory leak.

Please also get rid of the free stuff here:
Code: [Select]procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  cn.Free;;
  tr.Free;
  sq.Free;
  application.Terminate;
end;Of course nothing to do with your Access problems...

----------------------------------------------------------------------
Nebula · 2012-11-18 09:13

After a VERY quick look, I suspect it's to do with the freeing of the
sql and connection objects, and them not existing when the sql text is
being set.

----------------------------------------------------------------------
BigChimp · 2012-11-18 09:15

Quote from: Nebula on November 18, 2012, 10:13:56 amAfter a VERY quick
look, I suspect it's to do with the freeing of the sql and connection
objects, and them not existing when the sql text is being set.
Exactly: e.g. TForm1.BitBtn2Click is freeing sq while it's used in
button3.

----------------------------------------------------------------------
BigChimp · 2012-11-18 09:41

Corrected version attached with remarks in the code. You'll have to
change the db location back to your place though, forgot that one.

Items include:
- parameter names spelled wrong (using field names instead of
parameter names)
- the freeing mentioned above

Hope the remarks are clear enough, if not, please let me knoe.

PS: <rant>IF YOU HAD SUPPLIED A SMALL SAMPLE PROJECT THAT DEMONSTRATES
YOUR PROBLEMS IN THE FIRST PLACE, WE WOULD HAVE FOUND THESE PROBLEMS
DAYS AGO!</rant> Don't talk about your code, POST the code (and not
snippets, but everything that is relevant)!

PS2: Did like your posting of screenshots of errors etc, that was very
helpful.

----------------------------------------------------------------------
asdf · 2012-11-18 17:06

Thank you so much for your kind support, I will never forget this.
From many replies in this topic, I saw that the SQL is not so easy   .
Could you please introduce the sources for me to learn for more ?

----------------------------------------------------------------------
BigChimp · 2012-11-18 17:11

Hope it works

If you're asking about how to use SQL databases in Lazarus/FPC: have
you read the wiki articles on databases I linked to? Especially the
sqldb tutorial articles can be helpful...

For a general introduction to SQL, see the wiki article on
documentation - there's a link to a pdf by Marco Cantu, Essential SQL,
which can help you. Also there's some nice info on
http://www.w3schools.com/SQl/

----------------------------------------------------------------------
anis2505 · 2012-11-18 18:34

Quote from: asdf on November 18, 2012, 06:06:38 pmThank you so much
for your kind support, I will never forget this. From many replies in
this topic, I saw that the SQL is not so easy   . Could you please
introduce the sources for me to learn for more ?

On my opinion Lazarus/Freepascal database access is very simple in
comparaison to .NET and Java DB access.

regards

----------------------------------------------------------------------
Goodman H__ · 2012-11-19 04:16

Quote from: anis2505 on November 16, 2012, 11:23:31 amI have uploaded
the file on UploadHero

http://uploadhero.com/dl/jq8Thret

Hope that works now. anyway it's just contains how to connect to MS
Access DB and to use dbnavs to manage it

regards

Appreciated.
Finally I make it work!!!I am very 
Attached my homework if it helps.I used a database name test.mdb and
try to view each tables when select the table name in a combobox.It
works (at least partly,that is when only view on table and then close
the form,no exception caught) but exception thrown if I viewed one
table and then select another table to view.Attached also the error
mesage.Appreciated if anybody would like to take a look at the code
and figure me out why the exception occurred.

What's more,when I tried to select  table(artists,person)  from
database(test.mdb),I hard coded all table strings one by one ,but I am
wondering if there is any more general way (say SQL statement) to
retrieve the table names from test.mdb database and then added them to
the combobox automatically.

Appreciated again for all the help!

----------------------------------------------------------------------
anis2505 · 2012-11-19 09:43

Quote from: asdf on November 18, 2012, 06:06:38 pmThank you so much
for your kind support, I will never forget this. From many replies in
this topic, I saw that the SQL is not so easy   . Could you please
introduce the sources for me to learn for more ?

Hi,

I think Lynda.com SQL essential trainning would be e good help for you
to learn SQL

http://www.lynda.com/SQL-tutorials/essential-training/769-2.html

Hope that help.

regards

----------------------------------------------------------------------
BigChimp · 2012-11-21 11:52

Updated the ms access wiki page:
http://wiki.freepascal.org/MS_Access
is UsePrimaryKeyAsKey: False needed? I seem to remember there was
another, better workaround for that.

Additions welcome.

----------------------------------------------------------------------
anis2505 · 2012-11-21 19:40

Quote from: BigChimp on November 21, 2012, 12:52:30 pmUpdated the ms
access wiki page:
http://wiki.freepascal.org/MS_Access
is UsePrimaryKeyAsKey: False needed? I seem to remember there was
another, better workaround for that.

Additions welcome.

I think there should be a section for databases management comment
error and how to fixe them. I mean the problem that the programmer can
do in using databases component.
That would a good and necessary topic.

regards

----------------------------------------------------------------------
BigChimp · 2012-11-22 07:55

@anis: feel free to add/edit the wiki pages. There are also articles
in French and Arabic if you prefer to edit those (assuming you speak
those languages).

----------------------------------------------------------------------
asdf · 2012-11-24 09:34

From the same database,
Code: [Select]sq.SQL.Text:='select FSDateTime from FromFingerScanner';

How can I rewrite the above code to get only date or only time of
FSDateTime ?

----------------------------------------------------------------------
BigChimp · 2012-11-24 09:47

Perhaps it's a good idea to start a new thread if you're opening a new
subject?

That might interest more people to answer & make it clearer it's not
part of your original problem?

As for the answer: look into the SQL needed for Access to return only
date or time (something with CAST perhaps? Or a date() function? Can't
remember right now). I'd say it's much easier to use Pascal to convert
the Tdatetime to only date or time later though.

----------------------------------------------------------------------
asdf · 2012-11-24 09:52

Quote from: BigChimp on November 24, 2012, 10:47:32 amI'd say it's
much easier to use Pascal to convert the Tdatetime to only date or
time later though.

Thank you so much   .