How to refresh database after adding a new record?

[ArunJ]

Hi,

I am working on vb.net project & i have added a record to my SQL database & now I want to refresh it but don't understand how to do this. Any help?

[Dino.M]

If you set the database objects that you use to reference your database to nothing, and then whenever you want to have them refresh, just recreate the objects, and pass it another query. That should effectively load the new information.

[Kiran123]

I have this code please have a look
I hope this helps!

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

  LoadDb()

End Sub


Private Sub LoadDb()
       Conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"
       Conn.Open()

       Sql = "Select * from tblContacts"
       da = New OleDb.OleDbDataAdapter(Sql, Conn)

       da.Fill(ds, "AddressBook")

       MaxRows = ds.Tables("AddressBook").Rows.Count <--- it doubles the count after adding a row

       Conn.Close()

End Sub

Private Sub cmdCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCommit.Click

       If iRow <> -1 Then
           Dim cb As New OleDb.OleDbCommandBuilder(da)
           Dim dsNewRow As DataRow

           dsNewRow = ds.Tables("AddressBook").NewRow()

           dsNewRow.Item("FirstName") = txtFirstName.Text
           dsNewRow.Item("SurName") = txtSurname.Text

           ds.Tables("AddressBook").Rows.Add(dsNewRow)
           da.Update(ds, "AddressBook")

           MsgBox("New Record has been added to the Database")
       End If

       
       LoadDb()

End Sub


Private Sub cmdNextRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNextRow.Click

       If ds.Tables("AddressBook").Rows.Count > 0 Then
           If iRow < ds.Tables("AddressBook").Rows.Count Then
               NavigateRecords() ' display the record
               iRow = iRow + 1
               lblRecordNumber.Visible = True
               lblRecordNumber.Text = "Record Number " & iRow & " of " & ds.Tables(0).Rows.Count
           Else
               MsgBox("No More Rows")
           End If
       End If

End Sub

[Geo3213]

What is the actual database you are using?

Another thing you can try is closing and reopening the dataset, e.g.
MyADOQuery1.ExecSQL;
MyADOTable1.Close;
MyADOTable1.Open;

The problem with opening and closing is getting your newly inserted record to appear as the selected record in the DBGrid after you re-open the dataset.
To do that you could use the Locate method (MyADOTable1.Locate) (the syntax can be a little complicated, here's an UNTESTED example:
MyADOTable1.Locate(['FieldName1', 'FieldName'2'], ['Smith', 'John'], []);
But that is untested so it may be wrong - look in the help file if necessary.

[Alphonsus]

You could possibly use the FileSystemWatcher ... have an INSERT trigger on the SQL table that creates a text file in an otherwise empty folder, the FileSystemWatcher in VB recognises this new file and refreshes the datagrid, then deletes the file.

There is probably an easier way but I can't think of it right now.

[Ubimia]

• To add a record to the database, we invoke the AddNew method. The syntax for our example data control is:

dtaExample.Recordset.AddNew

This statement will blank out any bound data tools and move the current record to the end of the database. At this point, you enter the new values. When you move off of this record, the changes are automatically made to the database. Another way to update the database with the changes is via the Update method.

After adding a record to a database, you should invoke the Refresh property of the data control to insure proper sorting (established by RecordSource SQL statement) of the new entry. The format is:

dtaExample.Refresh