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?
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
Bookmarks