Problem connecting to a MS Access Database with ODBC

[BigChimp]

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]

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]

The problem is still the same  .

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?

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]

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]

Add 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]

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  .

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

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]

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)  + ')';


My 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_queries....)

[taazz]

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' ?

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]

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]

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]

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 (--")

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]

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]

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]

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 (--")                                                                                 

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]

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