Greetings,

I am using VB.NET 2003 and am simply trying to loop through all of the
tables in a SQL Server 2005 Database.  I am using the code below to
accomplish this.  However, all that is being returned in the "For Each" loop
is a "-" and a "1".   Any ideas as to what I am missing?

        sqlComm.CommandText = "Select name from sys.tables"
        strResult = sqlComm.ExecuteNonQuery()

        For Each x In strResult
            MessageBox.Show(x.ToString)
        Next

Thanks in advance!

=?Utf-8?B?U2hlcndvb2Q=?= <Sherw***@discussions.microsoft.com> wrote in
This won't work - I believe you'll need to look through a datareader or
dataset of the results.

Thanks.  Using a DataReader seemed to solve the problem.  I used the
following code just in case you're interested.

sqlComm.CommandText = "Select Table_name from information_schema.tables"
Dim dr As SqlClient.SqlDataReader
dr = sqlComm.ExecuteReader

Do While dr.Read
    MsgBox(dr("Table_name"))
Loop

dr.Close()
Show quoteHide quote

=?Utf-8?B?U2hlcndvb2Q=?= <Sherw***@discussions.microsoft.com> wrote in
Show quoteHide quoteThis would be a bit better:


Dim dr As SqlClient.SqlDataReader = sqlComm.ExecuteReader

try
        Do While dr.Read
            MsgBox(dr("Table_name"))
        Loop
catch ex as exception
Finally
        dr.Close()
end try

Notice that the dr.close is between the Finally statement? This is good
practice to force a close of the datareader in ALL situations to prevent
orphaned connections. Orphaned datareader connections gobble resources
like no tomorrow ;)

Spam Catcher,

But the not handled catch is in my opinin very bad code, I assume you know
it, but this can people reading this give them wrong idea's.

Cor

Show quoteHide quote

Yup of course ;-)

On Wed, 13 Dec 2006 14:15:02 -0800, Sherwood <Sherw***@discussions.microsoft.com> wrote:

¤ Greetings,
¤
¤ I am using VB.NET 2003 and am simply trying to loop through all of the
¤ tables in a SQL Server 2005 Database.  I am using the code below to
¤ accomplish this.  However, all that is being returned in the "For Each" loop
¤ is a "-" and a "1".   Any ideas as to what I am missing?
¤
¤         sqlComm.CommandText = "Select name from sys.tables"
¤         strResult = sqlComm.ExecuteNonQuery()
¤
¤         For Each x In strResult
¤             MessageBox.Show(x.ToString)
¤         Next
¤
¤ Thanks in advance!

An alternative would be to use GetOleDbSchemaTable. I don't typically recommend running queries
directly against system tables.

        Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
        Dim SchemaTable As DataTable

        DatabaseConnection.ConnectionString = "Provider=sqloledb;" & _
                                                "Data Source=(local);" & _
                                                "Initial Catalog=Northwind;" & _
                                                "Integrated Security=SSPI"

        DatabaseConnection.Open()

        SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                                                            New Object() {Nothing, Nothing, Nothing,
Nothing})

        Dim RowCount As Int32

        For RowCount = 0 To SchemaTable.Rows.Count - 1
            Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
        Next RowCount

        DatabaseConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

System.Threading.Timer does not tick
There is already an open DataReader associated with this Connection which must be closed first
Q: DataColumn Expressions
What is WebBrowser control called now?
The below snippet does not work...anyone know how to reference a procedure?
Form1.closing in VB2005???
Datagrid problem
how can I reference this bitmap?????????
Getting an Object Properties value...
Tab Control