Unable to access a field 2

[Stretchwickster]

Hi,

I'm new to using ADO components. I have successfully managed to populate a TComboBox with some fields from a database using the following code.

  Cb_VenueList.Items.Clear;
  ADOTable_Venue.First;
  while not ADOTable_Venue.EOF do
  begin
    Cb_VenueList.Items.Add(ADOTable_Venue.FieldByName('Name').AsString + ', ' +
                           ADOTable_Venue.FieldByName('Town').AsString);
    ADOTable_Venue.Next;
  end;

What I am trying to do now is to add the associated "VenueID" to the combobox item as a TObject by using the AddItem method, as you can see from the following code.

  Cb_VenueList.Items.Clear;
  ADOTable_Venue.First;
  while not ADOTable_Venue.EOF do
  begin
    Cb_VenueList.AddItem(ADOTable_Venue.FieldByName('Name').AsString + ', ' +
                         ADOTable_Venue.FieldByName('Town').AsString,
                         TObject(ADOTable_Venue.FieldByName('VenueID').AsInteger));
    ADOTable_Venue.Next;
  end;

However, this code does not run. An EDatabaseError appears saying 'ADOTable_Venue: Field 'VenueID' not found'. I have checked and double-checked and my venue table definitely contains a field called "VenueID" of type AutoNumber. In fact it is the primary key for that table.

I've probably made a blindingly obvious error but I can't spot it. So, does anyone know how to resolve this?

Your help would be much appreciated!

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[lespaul]

don't you need some + (concatenating operator) in there somewhere? Maybe between the town field and the VenueID field? I'm not sure what you're doing with the TObject, so I may be completely off base!!!

Good luck figuring this out!

les

(and could you explain what the TObject is all about? ~thanks)

 

[Stretchwickster]

Here's an excerpt from the Delphi help file which may help explain my use of TObject:

TCustomCombo.AddItem
Adds an item to the drop-down list of items.

procedure AddItem(Item: String; AObject: TObject);

Description

Call AddItem to add a string, with an associated object, to the drop-down list.

Item is the string to add to the drop-down list.

AObject is an object associated with that string. It can be accessed using the Objects property of the TStrings object that implements the Items property.

-----------------

Basically, I don't want to display VenueID but I want to associate it with the TComboBox item that displays the Name and Town for that row of the venue table. So, I have cast the VenueID as a TObject because that is the type it must be.

When the user clicks a save button on my data entry form, I will extract the VenueID from the selected item of the TComboBox (casting it back to an integer) so as to add a new row in a different table of my database.

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[lespaul]

I see now! Thanks for the explanation and no I don't have a clue why it's not working.

Have a great day!

les

 

[Stretchwickster]

Oh and I don't need a concatenation because I am passing the VenueID as the second parameter of the AddItem procedure. Incidentally, if I do a simple call to ShowMessage to display the field within the while loop, I get the same error.

   ShowMessage(ADOTable_Venue.FieldByName('VenueID').AsString);

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[LucieLastic]

Have you tried?


Cb_VenueList.AddItem(ADOTable_Venue.FieldByName('Name').AsString + ', ' +
ADOTable_Venue.FieldByName('Town').AsString,
Pointer(ADOTable_Venue.FieldByName('VenueID').AsInteger));

 

[Stretchwickster]

Hi Lou,

Thanks for the suggestion - but the error still stands when I change TObject to Pointer. As I said in my previous post, it occurs even when I simply make a call to ShowMessage which involves no casting.

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[LucieLastic]

Sorry Clive, just seen your previous message - doh!

I don't think you should normally need to do this, but have you added the fields to the fields editor of the ADOTable_Venue component, just to see if that'll work?

lou

 

[LucieLastic]

hi Clive

I think that is the problem, actually. If you use FieldByName it needs to be in the Fields editor, if you use the Fields[n] it doesn't. Btw, using the indexes rather than names is always faster.

lou

 

[Stretchwickster]

Lou, you are an absolute genius! I have been struggling to work this out for ages. The problem was me forgetting one small step of the process: adding the VenueID field to the Fields Editor! I had previously added Name and Town but had forgotten that step, so when I came to use VenueID it was not being recognised.

I had previously tried using Fields[0] to access the VenueID (as it is the first column of the table). However, this gave me the Name field instead? So it looks like you need to add the field in the Field Editor anyway! Thanks for the tip - I'll use the indexes instead.

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[Zathras]

I can't see what's wrong with your code either. I often use the TObject property to contain an integer that way, so that should not be an issue. (It's not expected, so be sure to document clearly what you are doing.)

Generally I use TADOQuery instead of TADOTable, but that shouldn't make any difference. However, if you've tried everything else, it couldn't hurt to try using TADOQuery.

 

[LucieLastic]

hi Clive
"I had previously tried using Fields[0] to access the VenueID (as it is the first column of the table). "

Using the indexes, you have to index them in order of your select. If you use "select *", the fields come in the order the table was created.

Apologies if I'm teaching you to suck eggs but for others: if you have "select Name, Town, VenueID from ..." then you'd access them as (indexes always start at 0):

Fields[0] = Name, Fields[1] = Town and Fields[2] would give you the VenueID.

lou

 

[Stretchwickster]

So when you select the fields in the Field Editor does that index them? Is that the same as a query of the form:

SELECT FirstFieldAdded, SecondFieldAdded, ThirdFieldAdded FROM TableName

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[LucieLastic]

Yes, if you change the order of the fields in the fields editor the indexing will change. When using the indexing, I believe it looks for the field first in the Fields Editor and if none are there, it looks in the SELECT. If not all the fields are in the Fields Editor, eg you have 3 fields in Fields Editor and there are 20 fields in the actual table, then trying to access an index greater than 2, would cause an Access Violation.

Sorry, should of said in my last post that if you assume no fields have been added to the fields editor then the indexing is taking from the order in the SELECT statement or from the order of the fields in the table if you're using SELECT *.

Hope that's clearer, I'm not the best at explaining things

lou

 

[Stretchwickster]

Right, I understand now. Thanks a lot for your help Lou.

Zathras, in what situations is it more beneficial to use a TADOTable as opposed to a TADOQuery and vice versa.

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[LucieLastic]

I think the only benefits of a Query over a Table component are: you can select specific fields, use joins and subqueries, and add a conditions to the query. You can execute commands in TADOQueries too which you can't do in a TADOTable.

I rarely need all the fields for all the rows in a table at once, mind you, we have some pretty large tables here, both wide and 'long'.

lou

 

[Zathras]

Clive, Lou has summed it up quite nicely.

I might add that when you use TADOTable with index references to the fields, you could get into a heap of trouble if the DBA ever re-loads the table after defining the columns in a different order. With TADOQuery, you are always in control of the sequence. So as long as the column names themselves don't get changed, you are a little safer there.

One more thing. I advise against using "magic numbers" when writing code. When using indexes for referencing the fields (which I almost always do), I define constants so as better to identify what data are being retrieved.

To go back to your original example, I might write it this way:

const
  COL_NAME    = 0;
  COL_TOWN    = 1;
  COL_VENUEID = 2;

procedure LoadVenueList( MyQuery:TADOQuery );
begin
  Cb_VenueList.Items.Clear;
  with MyQuery do
    begin
      Close;
      SQL.Text := 'SELECT NAME,TOWN,VENUEID FROM VENUE';
      Open;
      while not Eof do
        begin
          Cb_VenueList.AddItem(Fields[COL_NAME].AsString
                      + ', ' + Fields[COL_TOWN].AsString,
               TObject(Fields[COL_VENUEID].AsInteger));
          Next;
        end;
      Close;
    end;
end;

Of course, the production version would have try...except around the code, but this is just an example.
Notice the initial Close method call. This is SOP to protect this part of the code from some other code that used MyQuery and neglected to close the result set when it was finished.

 

[Stretchwickster]

That's excellent Zathras - thanks for that explanation. I agree, meaningfully named constants would be a great advantage here. I will definitely use TADOQuery instead.

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[Stretchwickster]

One final question: how do I check the record count after executing an TADOQuery?

Clive
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer."
Paul Ehrlich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[LucieLastic]

hi

If it's a SELECT, use query.recordCount, it it's owt else, use query.rowsaffected.

lou