field names

[frank ashley]

I'm populating a treeview using table/field names.

The table names I've done using sysobjects but what I need is for VB to pass the table name to SQL and to return all the field names for that table.

[Ianpbaker]

Hi Frank

If you are using ADO You can use this piece of code

strSQL = "SELECT * FROM " & yourtable 

myrecordset.Open strSQl,Myconnection

Iflag = myrecordset.fields.count - 1

For i = 0 to Iflag 
myvariable = myrecordset.fields(i).name
' Enter code here to put it into your list view
Next

Hope This Helps

Ian

[frank ashley]

Ian,

Thought of that. Problem is that that will bring back all the data as well. I've got some tables in excess of 100K records.

I was hoping for some kind of schema object somewhere/anywhere.

[Clunietp]

what database are you using, frank?

you could also use

Select * from MyTable where MyField = -1

if your MyField field is an autonumber/pri key (or other indexed field), you'll just return the table structure

HTH

Tom

[frank ashley]

Clunietp,

I'm using SQL 7

Where you mention MyField do you mean

Dim myField as Field

"Select * from " & strMyTable & " where " & MyField & "= -1"

[Vit]

OK, if you're using treeview control think of filling each branch on node.expand event. I mean that when you initialize the tree you fill only the first-level nodes. When you expand a specific node you fill it's child nodes and so on. In this way you don't have to bring all the records at once and the things work much faster

[frank ashley]

Just found out that THE way to do it is to call sp_columns.

ie

exec sp_columns 'tablename'

in Query Analyzer grid view

[Clunietp]

I meant that MyField would be your primary key field....but you have figured out another way so forget it, I guess...


Tom

[frank ashley]

Thanks for your reply Clunietp but the problem was that I wouldn't know the names of either the tables or the fields in advance!

[Clunietp]

gotcha

you can also use ADOX to retrieve database info as well if you don't want to tie yourself to SQL Server (but I doubt that the other drivers support this functionality, except maybe the Jet 4.0 provider....)

[frank ashley]

Yeah!

Only found out about a way using ADO after the event.

Cheers!