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

[Steve Gerrard]

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

Siv wrote:

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

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]]

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

Steve,

You can forget that where clause.

Cor

"Steve Gerrard" <mynamehere@com cast.netschreef in bericht
news:o-ednSkf0ZNIhp3Vn Z2dnUVZ_i2dnZ2d @comcast.com...

Siv wrote:

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

>
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]]

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

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


"Steve Gerrard" <mynamehere@com cast.netschreef in bericht
news:o-ednSkf0ZNIhp3Vn Z2dnUVZ_i2dnZ2d @comcast.com...

Siv wrote:

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

>
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]]

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

Siv,

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

Cor

"Siv" <g@removethiste xtsivill.comsch reef in bericht
news:85A32931-EE6F-40D3-B148-AD262710CF47@mi crosoft.com...

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

[Siv]

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

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

Siv

"Steve Gerrard" <mynamehere@com cast.netwrote in message
news:o-ednSkf0ZNIhp3Vn Z2dnUVZ_i2dnZ2d @comcast.com...

Siv wrote:

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

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

[Siv]

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

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

Siv

"Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
news:1BB0F93F-B11D-40EC-96D2-95F029411261@mi crosoft.com...

Siv,
>
Just create a datatable and use an adapter with the fillschema method.
(Any Select is valuable for that)
>
Cor
>
"Siv" <g@removethiste xtsivill.comsch reef in bericht
news:85A32931-EE6F-40D3-B148-AD262710CF47@mi crosoft.com...

>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

>

[Siv]

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

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(By Val 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.I tems.Clear()

strSQL = "Select * from " & TableName.ToStr ing & ";"
Cn = New SqlConnection(C onnString)
Cn.Open()

dt = New Data.DataTable
da = New SqlDataAdapter( strSQL, Cn)
da.FillSchema(d t, SchemaType.Sour ce)

For n = 0 To dt.Columns.Coun t - 1
lstFieldNames.I tems.Add(dt.Col umns(n).Caption )
lstFieldNames.I tems(n).SubItem s.Add(TableName .ToString)
Next


Catch ex As Exception

PEH("PopulateLi st", "frmChooseDataI tems", 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=SERVERN AME;DATABASE=da tabasename;Inte grated
Security=true;A synchronous 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.


"Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
news:1BB0F93F-B11D-40EC-96D2-95F029411261@mi crosoft.com...

Siv,
>
Just create a datatable and use an adapter with the fillschema method.
(Any Select is valuable for that)
>
Cor
>
"Siv" <g@removethiste xtsivill.comsch reef in bericht
news:85A32931-EE6F-40D3-B148-AD262710CF47@mi crosoft.com...

>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

>

[Asegid Debebe]

Re: What is the best way to get the field names from atable in SQL Server using VB.NET - Cor Ligthert[MVP]

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