Jet error

[MikeR]

My apologies if this is an inappropriate post for this group. I tried the DAO group,
but no response.

I've just started getting this error message on a seemingly random basis.
'The Microsoft Jet database engine does not recognize 'CID' as a valid field name or
expression.'
CID is a text field, and it is a valid column name.

The line of code that generates the error is
ListRec.FindFirst('CID = "' + retCID + '"');

The database (Access 2K) is opened like this:

dbE := CreateOleObject('DAO.DBEngine.36');
//open Jet
try
dbDAO := dbE.OpenDatabase(WritePath + FOLDER_NAME + DBName, True, False, ';pwd='
+ '');
except
on E:Exception do
begin
MessageDlg('Error opening database.'#13#10 + E.Message, mtInformation, [mbOK],0);
Result := False;
Application.Terminate;
exit;
end;
end;

ListRec is a DAO recordset, opened with

procedure TfrmLog.OpenCountry;
var
sql: String;
begin
sql := ('Select * from C_List order by CID');
try
ListRec := dbDAO.OpenRecordSet(sql);
ListRec.MoveLast;
except
on E:Exception do
begin
MessageDlg('Error opening Country table.'#13#10 + E.Message, mtInformation,
[mbOK],0);
exit;
end;
end;
end;

The only thing Google brought up wasn't what I need to solve this.
TIA,
Mike

 

[Arvin Meyer [MVP]]

Try:

ListRec.FindFirst "CID = """ & retCID & """"

 

[MikeR]

Try:

ListRec.FindFirst "CID = """ & retCID & """"

Thanks, but no can do. ' is the string delimiter in Delphi. And the parens are required.

 

[gllincoln]

HI Mike,

Your line ListRec.FindFirst('CID = "' + retCID + '"');

I don't think this will ever work. Try it this way if CID is a text/string (which you stated that it was)

ListRec.FindFirst "CID= '" & retCID & "'"

Or this way if CID had been a number

ListRec.FindFirst "CID=" & retCID

By the way what is retCID? If it is a local variable, the above should work.

If it refers to a form control - not good, then the reference should be (probably) & Me!retCID
depending on who Me is in the specific context and where retCID resides.

Hope this helps...
Gordon

 

[MikeR]

HI Mike,

Your line ListRec.FindFirst('CID = "' + retCID + '"');

I don't think this will ever work. Try it this way if CID is a
text/string (which you stated that it was)

Hi Gordon -
Thanks for the reply.
It's worked since about 1989. Only in the last couple of days has it failed, and then
sporadically (the *WORST* way). And yep, it's a string.

ListRec.FindFirst "CID= '" & retCID & "'"

+ is the concatenation operator in Delphi, not &.

Or this way if CID had been a number

ListRec.FindFirst "CID=" & retCID
By the way what is retCID? If it is a local variable, the above should
work.

If it refers to a form control - not good, then the reference should be
(probably) & Me!retCID
depending on who Me is in the specific context and where retCID resides.

Yeah, it's a variable. This is in a Delphi program, not using Access forms in any way.

 

[gllincoln]

Hi Mike,

Ouch, I've done my share of needle in haystack duty - not much fun.

Okay - maybe it's time to do all the obvious things - validate each assumption. Might want to make sure that you are getting the value that you think you are in retCID - and that there is truly a record to be found with that CID value.

Maybe test for a successful find:

If(ListRec.NoMatch!=0)

If this fails - maybe pass a literal expression ListRec.FindFirst('CID = 12345');

Where 12345 is a known good value. Just making sure that some obscure recast or overloaded variable issue isn't fooling with us.

Might also verify that are you hooking up to a live and populated recordset - test ListRec.RecordCount for a non-zero value, maybe test ListRec.Fields('CID') to see whether you get a value when you index by the fieldname, and what the first row of data is - if you get something back, stick that in the literal expression and see if you pick up a row of data.

If this has been working for a long time - then some part of the equation has changed - the question is what? Could be anything, the connection string, somebody changed a field name - can't take anything for granted. When I am feeling clueless, and I finally remember to remind myself not to assume I that have any clue - that's usually when I figure things out.

Gordon




">

 

[MikeR]

gllincoln wrote:
Thanks for hanging with me!

Okay - maybe it's time to do all the obvious things - validate each
assumption. Might want to make sure that you are getting the value that
you think you are in retCID - and that there is truly a record to be
found with that CID value.

I'll have to put some error trapping in to display the value when it blows, but when
stepping thru the code the value is correct, and there is in fact a record with it.
But it doesn't seem reasonable to me that either of those conditions would generate
that message.

Maybe test for a successful find:

If(ListRec.NoMatch!=0)

Wouldn't this be a test to see if the find succeded(after the find)? It's the find
it's failing on.

If this fails - maybe pass a literal expression ListRec.FindFirst('CID =
12345');

Where 12345 is a known good value. Just making sure that some obscure
recast or overloaded variable issue isn't fooling with us.

Again, would that throw the message I'm getting?

Might also verify that are you hooking up to a live and
populated recordset - test ListRec.RecordCount for a non-zero value,
maybe test ListRec.Fields('CID') to see whether you get a value when you
index by the fieldname, and what the first row of data is - if you get
something back, stick that in the literal expression and see if you pick
up a row of data.

I don't understand 'index by the fieldname'. Can you expand a bit?
The most likely scenario is that the recordset is past EOF or BOF. But i would expect
a different error, or none, just a nomatch condition. I'll have to test that. It's
not that the find fails because of a bad match condition - that I can handle.

If this has been working for a long time - then some part of the
equation has changed - the question is what? Could be anything, the
connection string, somebody changed a field name - can't take anything
for granted. When I am feeling clueless, and I finally remember to
remind myself not to assume I that have any clue - that's usually when
I figure things out.

True. I have a lot of DOH! moments. If it failed consistently, it would be a lot
clearer. I *HATE* intermittents. <sighs and goes off to do more testing>