What is the best way to get the field names from a table in SQL Server using VB.NET

[Siv]

Hi,
What is the best way to get the field names of a table in SQL server where
you only have Windows Authentication access to a database. Previously I have
used the SQLDMO object to get this, but now I do not have access to the
server using an SQL Server user account and am using the Windows
Authentication.

Any help appreciated.

Siv

 

[Cor Ligthert[MVP]]

Steve,

You can forget that where clause.

Cor

Just fill a datatable in the usual way, using a SQL select statement that
returns no records, i.e. "Select * From TheTable Where 1 = 0" .

 

[Cor Ligthert[MVP]]

Sorry steve, now I see what you want to do.

Just fill a datatable in the usual way, using a SQL select statement that
returns no records, i.e. "Select * From TheTable Where 1 = 0" .

 

[Cor Ligthert[MVP]]

Siv,

Just create a datatable and use an adapter with the fillschema method.
(Any Select is valuable for that)

Cor

 

[Siv]

Steve,
Many thanks for the tip, will give that a go.

Siv

 

[Siv]

Cor,
Thanks for the advice.
Will try your method as well as Steve's

Siv

 

[Siv]

Cor,
I created this procedure and it works a charm, so I posted it here for
others to crib if they have the same problem.
Thanks:

Private Sub PopulateList(ByVal TableName As String)
Dim Cn As SqlConnection = Nothing, dt As Data.DataTable = Nothing
Dim da As SqlDataAdapter = Nothing
Dim strSQL As String = ""
Dim n As Integer = 0

Try

lstFieldNames.Items.Clear()

strSQL = "Select * from " & TableName.ToString & ";"
Cn = New SqlConnection(ConnString)
Cn.Open()

dt = New Data.DataTable
da = New SqlDataAdapter(strSQL, Cn)
da.FillSchema(dt, SchemaType.Source)

For n = 0 To dt.Columns.Count - 1
lstFieldNames.Items.Add(dt.Columns(n).Caption)
lstFieldNames.Items(n).SubItems.Add(TableName.ToString)
Next


Catch ex As Exception

PEH("PopulateList", "frmChooseDataItems", ex.Message)

Finally

If Not IsNothing(dt) Then
dt.Dispose()
da.Dispose()
Cn.Close()
End If


End Try

End Sub

Connstring is a global var that holds the connection string to my database:
ConnString = "Server=SERVERNAME;DATABASE=databasename;Integrated
Security=true;Asynchronous Processing=true;"
It is populated via a settings dialog that populates the registry so that
thereater the user's database location is retrieved at start up.

PEH is my "Program Error Handler" routine that is used everywhere to display
error information as a messagebox.

Siv
Martley, Near Worcester, UK.

 

[Asegid Debebe]

Thanks Cor, it worked well for me. I previously used the fill method of the adapter object but was not able to get the length of each column from the SQL table. using fillschema has enabled me to get not only the length of the column but it is also very very fast!

Thanks!

Asegid