How to refresh database after adding a new record?

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?

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.

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

Code:

---

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

---

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.

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.

• 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