Get list of all tables in database then select 1 table of list and populate its column in data grid for sql management

Question

1.00/5 (2 votes)

See more:

so far i have connection and list made...i need some ideas or how i can select any table from the list to displayy in datagrid etc for sql management etc

below is what i have now:

VB

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sqlConn As New SqlClient.SqlConnection(DBconnection)
        con.Open()
        If con.State = ConnectionState.Open Then

            'Dim cmd As New SqlClient.SqlCommand '("SELECT * FROM INFORMATION_SCHEMA.tables order by table_name asc", con)
            Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='Base Table'order by 'TABLE_NAME' asc", con)

            Dim ds As New DataSet()

            da.Fill(ds)

            Dim MyTableCount As Integer = ds.Tables.Count

            For Each row As DataRow In ds.Tables(0).Rows
                ComboBox1.Items.Add(row.Item("TABLE_NAME"))
            Next row
        Else
            MessageBox.Show("Database Tables Unavailable")
        End If

    End Sub

Posted 3-Mar-14 9:11am

mrukr

Add a Solution

Comments

Maciej Los 3-Mar-14 15:22pm

And the question is...

mrukr 3-Mar-14 15:43pm

Basically how can I display the list of all tables of the database on a form (mine populated table list in combobox) then select any of these tables that would populate all data in dataview grid.Codes above is where I am stuck?
any advise or help would be greatly appreciated?

mrukr 4-Mar-14 20:20pm

how do i use a button to delete or save the data on the grid??

Thanks so much for your help

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sohail awr · Accepted Answer · 2014-03-04T06:10:00

VB

Public Class Form9

    Public oCn As New System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=MyDatabase;Uid=sa")

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If

        Dim cmd As New SqlClient.SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='Base Table'order by 'TABLE_NAME' asc", oCn)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet("bpl")
        Dim i As Integer = 0

        da.Fill(ds, "bpl")

        Dim MyTableCount As Integer = ds.Tables.Count

        For Each row As DataRow In ds.Tables(0).Rows
            ComboBox1.Items.Add(row.Item("TABLE_NAME"))
        Next row

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If
        Me.DataGridView1.Rows.Clear()
        Me.DataGridView1.Columns.Clear()

        Dim cmd As New SqlClient.SqlCommand("SELECT * FROM " & Me.ComboBox1.SelectedItem.ToString, oCn)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet("bpl")
        Dim i As Integer = 0

        da.Fill(ds, "bpl")

        ''''' CREATE TABLE COLUMNS IN DATAGRID
        If ds.Tables(0).Columns.Count > 0 Then
            While (i <> ds.Tables(0).Columns.Count)
                Me.DataGridView1.Columns.Add(ds.Tables(0).Columns(i).ColumnName, ds.Tables(0).Columns(i).ColumnName)
                i = i + 1
            End While
        End If
        i = 0

        cmd.CommandText = "Select * from " & Me.ComboBox1.SelectedItem.ToString

        da.Fill(ds, "bpl")

        Dim j As Integer = 0
        ''''' DISPLAY THE SELECTED TABLE DATA IN DATAGRID

        If ds.Tables(0).Rows.Count > 0 Then
            While (j <> ds.Tables(0).Rows.Count)
                Me.DataGridView1.Rows.Add()
                While (i <> ds.Tables(0).Columns.Count)
                    Me.DataGridView1.Item(i, j).Value = ds.Tables(0).Rows(j).Item(Me.DataGridView1.Columns(i).HeaderText)
                    i = i + 1
                End While
                i = 0
                j = j + 1
            End While
        End If
        j = 0
    End Sub
End Class

sohail awr · Accepted Answer · 2014-03-05T03:46:00

Solution 2

VB

Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_delete.Click

       If Me.DataGridView1.SelectedRows.Count > 0 Then
           If oCn.State = ConnectionState.Closed Then
               oCn.Open()
           End If
           Dim cmd As New SqlClient.SqlCommand("Delete * from " & Me.ComboBox1.SelectedItem.ToString & " where ID='" & Me.DataGridView1.SelectedRows(0).Cells(0).Value & "'", oCn)
           cmd.ExecuteNonQuery()
           MsgBox("Recodrd Deleted!")
           oCn.Close()
       End If

   End Sub

Posted 5-Mar-14 3:46am

sohail awr

Comments

mrukr 5-Mar-14 10:27am

sohail thanks buddy

below are my codes :so I need to add delete and update button to work within the datagrid view..

plz could you help me following my codes
just having a hard time.

I have my tables loading and showing on grid uusing codes below now I need to make button to save changes and delete any data.
thanks

mrukr 5-Mar-14 10:28am

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call ReadTables()

End Sub

Private Sub ReadTables()
Dim con As New SqlConnection(DBsPath)
Dim com As SqlCommand = Nothing
Dim dr As SqlDataReader = Nothing
'Dim schemaTable As DataTable
Try
ComboBox1.Items.Clear()
con.Open()

'Get table and view names
com = New SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='Base Table'order by 'TABLE_NAME' asc", con)
dr = com.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows Then
While dr.Read
ComboBox1.Items.Add(dr.Item("TABLE_NAME"))
End While
End If
dr.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

If ComboBox1.SelectedIndex > -1 Then

Dim con As New SqlConnection(DBsPath)
Try
con.Open()
Dim sqlCmd As New SqlCommand()

sqlCmd.Connection = con
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = "select * from " + ComboBox1.SelectedItem
Dim sqlDataAdap As New SqlDataAdapter(sqlCmd)

Dim dtRecord As New DataTable()
sqlDataAdap.Fill(dtRecord)
'DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dtRecord
'DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)

End Try
Else
MessageBox.Show("No tables selected")
End If

sohail awr · Accepted Answer · 2014-03-05T06:06:00

VB

Private Sub btn_update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_update.Click
    '''''' I have used 1st Column as Criteria you can choose any other column as per your requirements
    Dim str As String = ""
    Dim oCol As Integer

    If oCn.State = ConnectionState.Closed Then
        oCn.Open()
    End If

    str = "Update " & Me.ComboBox1.SelectedItem.ToString & " Set "

    While (oCol <> Me.DataGridView1.Columns.Count)
        str = str & Me.DataGridView1.Columns(oCol).HeaderText & "='" & Me.DataGridView1.Item(oCol, Me.DataGridView1.CurrentRow.Index).Value & "',"
        oCol = oCol + 1
    End While
    oCol = 0
    str = str.Remove(str.LastIndexOf(","), 1) & " where " & Me.DataGridView1.Columns(0).HeaderText & "=" & Me.DataGridView1.CurrentRow.Cells(0).Value

    Dim cmd As New SqlClient.SqlCommand(str, oCn)
    cmd.ExecuteNonQuery()
    MsgBox("Record Updated")
    oCn.Close()
End Sub

Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_delete.Click
    '''''' I have used 1st Column as Criteria you can choose any other column as per your requirements
    If Me.DataGridView1.SelectedRows.Count > 0 Then
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If
        Dim cmd As New SqlClient.SqlCommand("Delete from " & Me.ComboBox1.SelectedItem.ToString & " where " & Me.DataGridView1.Columns(0).HeaderText & "=" & Me.DataGridView1.SelectedRows(0).Cells(0).Value, oCn)
        cmd.ExecuteNonQuery()
        MsgBox("Recodrd Deleted!")
        oCn.Close()
    End If

End Sub

Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
    Dim str As String = ""
    Dim oCol As Integer

    If oCn.State = ConnectionState.Closed Then
        oCn.Open()
    End If

    str = "Insert Into " & Me.ComboBox1.SelectedItem.ToString & "("

    While (oCol <> Me.DataGridView1.Columns.Count)
        str = str & Me.DataGridView1.Columns(oCol).HeaderText & ","
        oCol = oCol + 1
    End While
    oCol = 0
    str = str.Remove(str.LastIndexOf(","), 1) & ") Values('"

    While (oCol <> Me.DataGridView1.Columns.Count)
        str = str & Me.DataGridView1.Item(oCol, Me.DataGridView1.CurrentRow.Index).Value & "','"
        oCol = oCol + 1
    End While

    str = str.Remove(str.LastIndexOf("','"), 3) & "')"

    Dim cmd As New SqlClient.SqlCommand(str, oCn)
    cmd.ExecuteNonQuery()
    MsgBox("Record Saved")
    oCn.Close()
End Sub

mrukr · Accepted Answer · 2014-03-05T04:26:00

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call ReadTables()

End Sub

Private Sub ReadTables()
Dim con As New SqlConnection(DBsPath)
Dim com As SqlCommand = Nothing
Dim dr As SqlDataReader = Nothing
'Dim schemaTable As DataTable
Try
ComboBox1.Items.Clear()
con.Open()

'Get table and view names
com = New SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='Base Table'order by 'TABLE_NAME' asc", con)
dr = com.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows Then
While dr.Read
ComboBox1.Items.Add(dr.Item("TABLE_NAME"))
End While
End If
dr.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

If ComboBox1.SelectedIndex > -1 Then

Dim con As New SqlConnection(DBsPath)
Try
con.Open()
Dim sqlCmd As New SqlCommand()

sqlCmd.Connection = con
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = "select * from " + ComboBox1.SelectedItem
Dim sqlDataAdap As New SqlDataAdapter(sqlCmd)

Dim dtRecord As New DataTable()
sqlDataAdap.Fill(dtRecord)
'DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dtRecord
'DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)

End Try
Else
MessageBox.Show("No tables selected")
End If

mrukr · Accepted Answer · 2014-03-07T03:48:00

If TextBox1.Text <> "" Then
Dim con As New SqlConnection(DBsPath)
Try
con.Open()
Dim sqlCmd As New SqlCommand()

sqlCmd.Connection = con
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = TextBox1.Text
Dim sqlDataAdap As New SqlDataAdapter(sqlCmd)

Dim dtRecord As New DataTable()
sqlDataAdap.Fill(dtRecord)
DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dtRecord
DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
con.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Else
MessageBox.Show("Please type correct SQL Query")
End If
End Sub

sohail awr · Accepted Answer · 2014-03-08T17:18:00

I have checked your code it is correct and working properly. But what is your main confusion then tell, because I am unable to read your mind. I have used this query in Textbox and slightly modified your code. These are selection queries which are I have checked. The 'Table1' name is automatically replaced by your list of Tables available in Combobox.

SQL

Select * from Table1

Select * from Table1 where name='AAAA'

Delete from Table1 where name='AAAA'

VB

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim i As Integer = 0
        Dim str As String = ""

        If TextBox1.Text <> "" Then
            str = Me.TextBox1.Text
            Dim con As New SqlClient.SqlConnection(DBsPath)
            Try
                con.Open()
                Dim sqlCmd As New SqlClient.SqlCommand()

                sqlCmd.Connection = con
                sqlCmd.CommandType = CommandType.Text
                sqlCmd.CommandText = str.Replace("Table1", Me.ComboBox1.SelectedItem.ToString)
                Dim sqlDataAdap As New SqlClient.SqlDataAdapter(sqlCmd)

                Dim dtRecord As New DataTable()
                sqlDataAdap.Fill(dtRecord)
                DataGridView1.AutoGenerateColumns = True
                DataGridView1.DataSource = dtRecord
                DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                con.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Else
            MessageBox.Show("Please type correct SQL Query")
        End If
    End Sub

Get list of all tables in database then select 1 table of list and populate its column in data grid for sql management

6 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0