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

  • Thread starter Thread starter Siv
  • Start date Start date
S

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,

You can forget that where clause.

Cor

Steve Gerrard said:
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" .
Click to expand...
 
Sorry steve, now I see what you want to do.


Steve Gerrard said:
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" .
Click to expand...
 
Siv,

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

Cor
 
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.
 
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
 
Back
Top