Reloading DatagridView automatically when new record is inserted in relative sql db

[arvinddx]

hi there,
I am on a project where i need that the datagridview on a vb form automatically update itself when new record is added to the table from which the dgv is fetching data. I am able to load the data in dgv but no new inserted records are showing.

[wes4dbt]

How is the new record added to the table? Manually/from your project? Also you need to post the code your using to load the data into the dgv.

[jmcilhinney]

If it's your application that is inserting the new record then there's every chance that you're doing it wrong and there's no need to reload anything. A lot of people design their app such that a grid displays data from a table on one form, then they open a second form to add or edit a record and save that to the database, then they query the database again to refresh the first form. That's backwards. The second form should have no communication with the database at all. The second form edits an existing row or adds a new row and passes that data back to the first form. That first form then merges those changes into the DataTable that is bound to the DataGridView and the grid updates to reflect that. The first form then saves the changes from the DataTable back to the database. There's no need to requery at all.

If the changes to the database originate with a different source then, for SQL Server at least, you can look at the SqlDependency class for change notifications. Just be aware that this places a load on the server so you must only use it if there are a small number of clients listening for notifications. Once you receive a notification, you get data the same way you did in the first place. A query is a query. The problem is that, unless you've designed your database such that you can determine which records have changed since your last query, you have no choice but to simply discard everything you have and pull everything back again, which is quite inefficient.

[arvinddx]

Attachment 179060

below is my code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles bt_search.Click
        mainformcon.Open()

        Dim mainformstr As String = "select customer_name, ord_number, inv_number, total_amount from outdoor_orders where inv_date between @dt1 and @dt2 order by customer_name asc"

        Dim mainformcmd As New SqlCommand(mainformstr, mainformcon)

        mainformcmd.Parameters.AddWithValue("@dt1", SqlDbType.DateTime).Value = DateTimePicker1.Value
        mainformcmd.Parameters.AddWithValue("@dt2", SqlDbType.DateTime).Value = DateTimePicker2.Value

        Dim mainformda As New SqlDataAdapter(mainformcmd)

        Dim mainformdt As New DataTable()

        mainformda.Fill(mainformdt)

        dgv_outdorr_orders.DataSource = mainformdt

        addamount()

        If tx_total_amount.Text = 0 Then

            tx_total_amount.BackColor = Color.LightPink
            MessageBox.Show("No Sales for Today :" & Date.Today)

        Else

            tx_total_amount.BackColor = Color.LightGreen

        End If

        mainformcon.Close()

    End Sub

My data insertion form is a different form(i.e working fine). This Form is just to display the current sales data for the present date.

[user001]

If you are able to save to database and bind to the GridView that means you have it. You just need to refresh the GridView anytime new record is inserted to database.
A good example you can find here https://www.emmason247.com.ng/tutori...ase/ICGWDICDVG

[wes4dbt]

Still not enough information to know exactly what your doing but if your opening the add form from the form with the dgv then I would suggest using a bindingsource for the dgv datasource and then you can do something like this and it will update the dgv.

Imports System.Data.SqlClient

Public Class PassBoundData
    'I always store my connectionstrings in the project Setting
    Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
    Private WithEvents da As New SqlDataAdapter("Select BookId, BookName, Author From Books ORDER BY BookName", con) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    Private WithEvents dt As New DataTable

    'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
    Private bs As New BindingSource

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
    Private bldr As New SqlCommandBuilder(da)

    Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load

        Try
            con.Open()

            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        Try
            bs.EndEdit()
            da.Update(dt)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub DataGridView1_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick

        Dim row As DataRowView = DirectCast(bs.Current, DataRowView)

        Dim frm As New ReceiveDataRow(row)

        frm.ShowDialog()
        bs.EndEdit()
    End Sub

    Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click

        Dim row As DataRowView = DirectCast(bs.AddNew, DataRowView)
        bs.EndEdit()
        Dim frm As New ReceiveDataRow(row)
        frm.ShowDialog()
        bs.EndEdit()

    End Sub

End Class

[arvinddx]

i changed the scenario and it is working now. got new design for my dashboard. Thanks for the help.