SQL Table Informations

[danasegarane]

'This code will display the SQL Table Information

'Global declartion
Imports System.Data
Imports System.Data.SqlClient

'In a Form
'Add one command button and name it as Button2
'Add one Dropdownbox and name it as cmbTablenames
'Add one DataGridview control
'And in the command button click event add this code

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim con As New SqlConnection("User ID=sa;pwd=secreate;Initial Catalog=YourDatabaseName;Data Source=SQLSERVERNAME")
            Dim SchemaAdapter As New SqlDataAdapter("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME", con)
            con.Open()
            Dim Schematable As New DataTable
            SchemaAdapter.Fill(Schematable)
            Dim i As Long
            For i = 1 To Schematable.Rows.Count - 1
                cmbTablenames.Items.Add(Schematable.Rows(i).Item("TABLE_NAME").ToString) 'add the tablenames
            Next
            Schematable.Dispose()
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

'FUNCTION TO DISPLAY THE TABLE INFORMATION WITH RESPECT TO TABLENAME
 Private Sub DisplayTableInformation(ByVal tablename As String)
        Try

            'Function to display the table information
            'Add a datagridview control
            Dim con As New SqlConnection"User ID=sa;pwd=secreate;Initial Catalog=YourDatabaseName;Data Source=SQLSERVERNAME")
            Dim SQL As String
            SQL = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='" & tablename & "'"
            Dim SchemaAdapter As New SqlDataAdapter(SQL, con)
            con.Open()
            Dim Schematable As New DataTable
            SchemaAdapter.Fill(Schematable)
            DataGridView1.DataSource = Schematable
             Schematable.Dispose()
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
'In the DropDownBox Change event add this code
Private Sub cmbTablenames_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbTablenames.SelectedIndexChanged
        DisplayTableInformation(cmbTablenames.Text)
    End Sub

[bczm8703]

i follow the above codes and got this error:
Compiler Error Message: BC30519: Overload resolution failed because no accessible 'New' can be called without a narrowing conversion

[danasegarane]

It is working For me.Can you highlight the line Where you got the error?

[bczm8703]

the code can work..
but is it possible to list the data of the table???
What i got now is all the table properities eg:
TABLE_CATALOG:mydb
TABLE_SCHEMA:dbo
TABLE_NAME:customer
the above is what got after using the codes.....which is not i want..
what i want is the data like in customer table there are 3 column:
name, contact, status
so the page should display name, contact and status with all the data in the respective column

[danasegarane]

Here is the Link

[anooshiran]

hi danasegarane
Thank you for your nice and helpfull code.
That really work good and help me a lot
again thanks
i am so lucky that find this tread