locked
Get column names from a datatable?

    Question

  • I am trying to get the column names from a specific table from my database.

    I can get the column names for all the tables but i am having trouble restricting it to just 1

     

     

    Me.ColumnsListBox.Items.Clear()

     

            sql_connection = New SqlClient.SqlConnection _

        ("Data Source=.\SQLExpress;AttachDbFilename=" & dblocation & ";Integrated Security=True;User Instance=True")

     

            'maker_sql_select_string = "SELECT * FROM " & Me.TablesListBox.SelectedItem.ToString

            maker_sql_select_string = "SELECT * FROM sysobjects WHERE type = 'U'"

     

            'maker_sql_select_string = "SELECT SO.NAME, SC.NAME FROM SYS.OBJECTS SO INNER JOIN SYS.COLUMNS SC " _

            '& "ON SO.OBJECT_ID = SC.OBJECT_ID WHERE SO.TYPE = 'U' ORDER BY SO.NAME, SC.NAME"

     

            'maker_sql_select_string = "SELECT SC.NAME FROM SYS.OBJECTS SO INNER JOIN SYS.COLUMNS SC " _

            '& "ON SO.OBJECT_ID = SC.OBJECT_ID WHERE SO.TYPE = 'U'" ' ORDER BY SC.NAME"

     

            maker_command = New SqlClient.SqlCommand(maker_sql_select_string, sql_connection)

     

            If maker_command.Connection.State = ConnectionState.Closed Then

                maker_command.Connection.Open()

            End If

     

     

            maker_command.ExecuteNonQuery()

            'maker_data_adapter.Fill(maker_data_table)

     

     

     

    these 2 work but not as i have them

    i just can't seem to figure this one out

            

            dim restrictions1 as String= new string[4] {null , null, tableName, null }

            maker_data_table = sql_connection.GetSchema("Columns", restrictions1)

     

            Dim curtable As String = Me.TablesListBox.SelectedItem.ToString

            maker_data_table = sql_connection.GetSchema("Columns", CType(New Object() {Nothing, Nothing, curtable}, String()))

     

     

     

     

            For Each rows As DataRow In maker_data_table.Rows

                Me.ColumnsListBox.Items.Add(rows.Item(3))

            Next

     

     

            'gets column schema info

            'Dim row As DataRow

            'Dim column As DataColumn

            'For Each row In maker_data_table.Rows

            '    For Each column In maker_data_table.Columns

            '        Me.ColumnsListBox.Items.Add(row(column))

            '    Next

            'Next

     

     

     

                    maker_command.Connection.Close()

            sql_connection.Close()

     

     

    Friday, November 16, 2007 2:18 PM

Answers

  • I found the answer

    i knew there had to be something built in for this, it was just a little tricky to figure out because when you combine certain strings and methods, the results are not what they are by themselves.

     

    anyway here is the code

     

    sql_connection = New SqlClient.SqlConnection _

        ("Data Source=.\SQLExpress;AttachDbFilename=" & dblocation & ";Integrated Security=True;User Instance=True")

     

            maker_sql_select_string = "SELECT * FROM YourTable"

     

            maker_command = New SqlClient.SqlCommand(maker_sql_select_string, sql_connection)

     

            If maker_command.Connection.State = ConnectionState.Closed Then

                maker_command.Connection.Open()

            End If

     

            sql_data_reader = maker_command.ExecuteReader()

     

     

            For i As Integer = 0 To sql_data_reader.FieldCount() - 1

                Me.ColumnsListBox.Items.Add(sql_data_reader.GetName(i))

            Next

     

     

            sql_data_reader.Close()

            maker_command.Connection.Close()

            sql_connection.Close()

     

    Saturday, November 17, 2007 5:56 AM