How to code a button to get data from a table in database and display in datagrid view ?

Question

I'm trying to code a button which has a SELECT statement to get information from one table but I want the information displayed in a data grid view.

From the data grid view, this data will be stored in a different table within the same database.

Previously i had used a list box to display the information but i could not save it in the data base.

This is the code i used for a list box:

listbox.items.add
{while mydatareader.read
{add.("item_name")

Is there any way you can display this on a data grid view like i did on the listbox?

Im using a datagrid view textboxcolumn column.

Answer 1

Why not just copy the first table to the second table and then bind the gridview to the second table instead of trying to bind the gridview to both?

Answer 2

Am sure there are plenty of ways of doing this but try this. Lets say you have a Database with a Table named "TestSample" which has three columns named "Column1","Column2","Column3". Next add a ListView Control on your form and name it "ListDisplay" and maybe two buttons for testing purposes. Next you need to create Columns, you can do it by code like this

Private Sub CreateColumns()
    'ListDisplay is the name of the ListView Control'
    ListDisplay.View = View.Details
    ListDisplay.FullRowSelect = True

    'Create Columns'
    ListDisplay.Columns.Add("Column1", 200, HorizontalAlignment.Left)
    ListDisplay.Columns.Add("Column2", 200, HorizontalAlignment.Left)
    ListDisplay.Columns.Add("Column3", 200, HorizontalAlignment.Left)
End Sub

this code can be called from the "Form Load" event like

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Setup Colums'
    CreateColumns()
End Sub

Next thing is to Collect Data from database and insert back to the database.. this two functions can help

 Private Sub FillTable(ByVal connString As String, ByVal QueryString As String)
    'Clear Populated Items'
    ListDisplay.Items.Clear()

    'Setup Connection and query'
    Dim conn As SqlConnection = New SqlConnection(connString)
    Dim cmd As SqlCommand = New SqlCommand(QueryString, conn)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

    'Setup Dataset'
    Dim ds As New DataSet

    'Populate Dataset'
    conn.Open()
    da.Fill(ds, "TestSample")
    conn.Close()

    'Free up memory'
    cmd.Dispose()
    conn.Dispose()

    'Populate ListView Control'
    For Each dr As DataRow In ds.Tables("TestSample").Rows
        Dim items As New ListViewItem
        items.Text = dr.Item(0).ToString()
        items.SubItems.Add(dr.Item(1).ToString())
        items.SubItems.Add(dr.Item(2).ToString())

        ListDisplay.Items.Add(items)
    Next

End Sub

Private Sub InsertBack(ByVal connString As String, ByVal QueryString As String, ByVal ParameterOne As String, ByVal ParameterTwo As String, ByVal ParameterThree As String)
    'Setup Connection'
    Dim conn As SqlConnection = New SqlConnection(connString)
    Dim cmd As SqlCommand = New SqlCommand(QueryString, conn)

    'Setup Parameters'
    cmd.Parameters.Add("@Column1", SqlDbType.Text).Value = ParameterOne
    cmd.Parameters.Add("@Column2", SqlDbType.Text).Value = ParameterTwo
    cmd.Parameters.Add("@Column3", SqlDbType.Text).Value = ParameterThree

    'Process data: Insert to database'
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    'Free up memory'
    cmd.Dispose()
    conn.Dispose()

End Sub

using the button's "Click Event" in the form you can call populate the ListView like this

 'Get data from database and populate ListView: Parameter requires connection string'
    FillTable("Data Source=SERVER-PC;Initial Catalog=Database;Persist Security Info=True;User ID=UsernameIfRequired;Password=PasswordIfRequired", "SELECT * FROM TestSample")

the connection string is a sample, you will have to provide your own, also the query string will need to be modified according to your database. Lastly to insert data into the database, lets say from your ListView items if they have been modified you can do it like...

  'Loop through all Items in the ListView and send to database'
    For Each item As ListViewItem In ListDisplay.Items
        'Add data to the database from ListView Items: Parameter requres connection string and the 3 input items (could be as many as you want)'
        InsertBack("Data Source=SERVER-PC;Initial Catalog=Database;Persist Security Info=True;User ID=UsernameIfRequired;Password=PasswordIfRequired", "INSERT INTO TestSample (Column1,Column2,Column3) values (@Column1,@Column2,@Column3)", item.Text, item.SubItems(1).Text, item.SubItems(2).Text)

        'Add data to the database from ListView Items: Parameter requres connection string and the 3 input items (could be as many as you want)'
        InsertBack("Data Source=SERVER-PC;Initial Catalog=Database;Persist Security Info=True;User ID=UsernameIfRequired;Password=PasswordIfRequired", "INSERT INTO AnotherSample (Column1,Column2,Column3) values (@Column1,@Column2,@Column3)", item.Text, item.SubItems(1).Text, item.SubItems(2).Text)
    Next

As you can see the data can be sent to two different tables..