GetOleDbSchemaTable for SQl?

[BukHix]

I am looking for the SQL equivalant of the GetOleDbSchemaTable. I have two different sample from the web. One on this forum and the other on VB2TheMax. However both are for Access.

Any ideas of how to pull the names of all the tables when the DB is SQL?

Thanks

[Edneeis]

You can use the OLEDB provider with SQL also, since the SQL one doesn't have an feature like that. Or you can use query the Master Db for the Tables and DB info.

[BukHix]

Do you know which Import I need to add? Right now I am getting the blue squigly line under the cnn.GetOleDbSchemaTable.

' Top of code
Imports System.Data.OleDb
'Imports System.Data.SchemaType
'***************************


      Dim cnn As SqlClient.SqlConnection = _
                         New SqlClient.SqlConnection( _
                         "Data Source=ServerName;" & _
                         "Initial Catalog=DataBaseName;" & _
                         "User ID=SQLUser;password=password")
        cnn.Open()

        Dim tables As DataTable = _
            cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _
            {Nothing, Nothing, Nothing, "TABLE"})

        cnn.Close()

Thanks!

[Edneeis]

You don't need an Imports. Imports are used to save us typing thats all. So these two things are the same:

Dim obj as New System.Object

'and
Imports System

Dim obj As New Object

It just means the the namespace outlined in the Imports is implied where applicable, but I'm rambling now.

The problem is that you are still using a SQLConnection object which doesn't have a GetOleDbSchemaTable method. You have to use an OLEDBConnection object which has a GetOleDbSchemaTable method. This also means you'll need to change your connection string to include the SQL Server provider, which is not needed in the SQLConnection.ConnectionString.

There should be an example posted here: http://www.vbforums.com/showthread.p...hreadid=232248

[BukHix]

Thank you. I seen that sample but didn't realize it would work (or was for) SQL Server. For those of you following this thread this is how you do it without the integrated security.

        Dim cnn As New OleDbConnection( _
            "Provider=SQLOLEDB.1;" & _
            "User ID=UserName;password=PassWord;" & _
            "Initial Catalog=DataBaseName;" & _
            "Data Source=ServerName")

        cnn.Open()

        Dim Tables As DataTable = _
        cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _
            {Nothing, Nothing, Nothing, "TABLE"})

        cnn.Close()
        ListBox1.DataSource = Tables
        ListBox1.DisplayMember = "TABLE_NAME"