Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    8

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

    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.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,561

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

    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.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,580

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    8

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

    Attachment 179060

    below is my code :

    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.
    Last edited by si_the_geek; Oct 18th, 2020 at 11:43 AM. Reason: added Code tags

  5. #5
    New Member
    Join Date
    Jul 2020
    Posts
    9

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

    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

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,561

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

    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.

    Code:
    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    8

    Re: Reloading DatagridView automatically when new record is inserted in relative sql

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width