field names

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.

Hi Frank

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

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

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.

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

Clunietp,

I'm using SQL 7

Where you mention MyField do you mean

Dim myField as Field

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

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

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

ie

exec sp_columns 'tablename'

in Query Analyzer grid view

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


Tom

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!

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

Yeah!

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

Cheers!