getting ms-sql column names

[Technocratic]

using MS SQL 7.0 and Delphi 5:
I need to retrieve column names from a table to populate a combobox... I've tried using the sp_column system proc in a query, but Delphi doesn't see Column_Name as a field in the result set... maybe that's an utterly wrong way to approach it though.. I'm not a big SQL guru... has anyone attempted this?

 

[TealWren]

I've used SP_COLUMNS successfully lots of times, but I'm using ADO instead of the BDE. Are you using the bde?

Is your syntax right on the query? (Not that there's much to it!) Just
SP_COLUMNS tablename

Did you open the query? Are you getting any results back at all?

Good luck!
TealWren

 

[Technocratic]

hi, thanks for answering... I'm wondering if maybe it's my retrieval, and not the query that's the problem. it runs fine in query analyzer, and delphi only gives an exception when i try to access "column_name"
here's my code:

procedure TForm1.cbTargetFieldEnter(Sender: TObject);
begin
  with TheQuery do
  begin
    sql.clear;
    sql.add('select column_name from information_schema.columns where table_name = '#34 + cbTableID1.text + #34);
    
    Open;
    First;
    cbTargetField.Clear;
    while not eof do
    begin
      cbTargetField.Items.add(fieldbyname('column_name').asString);
      next;
    end;
    close;
  end;
end;

i simplified it using sp_columns but got the same error

sql.add('sp_columns ' + cbTableID1.text);

 

[Technocratic]

oh yeah, i forgot..
yes, I'm using the BDE.
the exception says:


TheQuery: Field'column_name' not found


Thanks for your assistance!

 

[TealWren]

oh, try this:

procedure TForm1.cbTargetFieldEnter(Sender: TObject);
begin
  with TheQuery do
  begin
    sql.clear;
    sql.add('SP_COLUMNS ' +bTableID1.text);  
    Open;
    First;
    cbTargetField.Clear;
    while not eof do
    begin
      cbTargetField.Items.add(fieldbyname('column_name').asString);
      next;
    end;
    close;
  end;
end; TealWren

 

[TealWren]

Sorry, I didn't read the last line which says you already did that!

I have a little query runner which will run queries through BDE or ADO, and SP_COLUMNS works just fine through BDE too, so that's not it. Have you put a break on the open to make sure the SQL is what you expected? Maybe your table combo isn't giving you a valid table...

Good luck! TealWren

 

[DjangMan]

[tt]
cbTargetField.Items.add(fieldbyname('column_name').asString);
[/tt]

Will this work since you have a single field result:
[tt]
cbTargetField.Items.add(field(0).asString);
[/tt]

DjangMan