VS2012 Express: Refresh DataGridView When New Record is Saved

[Vladamir]

I have a small project which works with an Access 2003 database. It has some fields in text boxes, a few buttons and a datagridview. When I save the new record, I would like the datagridview to refresh as well. The searches I've come up with thus far show some very complex stuff which I don't think really apply for this simple a setup. Any advice would be appreciated.

Imports System.Data.OleDb

Public Class Form1

    Dim dbname As String = "\\a_very_long_novell_path\CallLog.mdb"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CallLogDataSet.CallLog' table. You can move, or remove it, as needed.
        Me.CallLogTableAdapter.Fill(Me.CallLogDataSet.CallLog)

    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim myConnection As OleDbConnection
        Dim myCommand As OleDbCommand
        Dim mySQLString As String
        Dim NTS As Integer = 0
        Dim Detail As Integer = 0
        Dim Auto As Integer = 0

        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbname & ";")
        myConnection.Open()

        mySQLString = "INSERT INTO CallLog (CallDate, Caller, Reason, DwgNo, AnsweredBy, TransferredTo)" & _
            " Values ('" & DateTimePicker1.Text & "', " & _
                    "'" & UCase(txtCaller.Text) & "', " & _
                    "'" & UCase(txtCallReason.Text) & "', " & _
                    "'" & UCase(txtDwgNo.Text) & "', " & _
                    "'" & UCase(txtAnsweredBy.Text) & "', " & _
                    "'" & UCase(txtTransferredTo.Text) & "');"

        myCommand = New OleDbCommand(mySQLString, myConnection)
        myCommand.ExecuteNonQuery()
        MessageBox.Show("New Call Log has been added.", "Record Added")
        myConnection.Close()

    End Sub

    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        txtAnsweredBy.Text = ""
        txtCaller.Text = ""
        txtCallReason.Text = ""
        txtDwgNo.Text = ""
        txtTransferredTo.Text = ""
        DateTimePicker1.Text = Now
    End Sub

End Class

[dunfiddlin]

The datagridview can't refresh because the datatable it's bound to does not contain the new information which you've sent direct to the database. There is no choice therefore but to refill the datatable.

[.paul.]

does the dgv contain columns for the 6 fields?
do you allow your user to add rows to the dgv?

[Vladamir]

The dgv contains the six fields, and no the users are not making any changes using the dgv. It was a nice feature that the users wanted to see to relate to the adjacent entries in the database for reference only.

[.paul.]

can you show us the full code?
it'd be easiest to add the 6 values as a new row in the dgv + update any changes to the datatable in 1 go later in the app.

[Vladamir]

Thanks again...and I once again must apologize for my lack of knowledge on this subject. That is the complete code. I made a simple form with the data source for the DGV being this simple database which contains only those 6 fields and an ID field. I tried to zip up the project and attach it, but our connections keeps timing out for the attachment upload.

[jmcilhinney]

I feel like I must address this same issue at least once every week. I don't know whether there is a lot of bad information out there or people just make the same bad decision but you are doing things backwards. DO NOT make a change to the database then retrieve the results of that change. DO NOT use ExecuteNonQuery to insert a record when you already have a DataTable. As .paul. says, make the change to the grid first. Use your table adapter to Fill the DataTable bound to your grid. If the user adds a new record, add it to the DataTable first. That will update the grid. You then use the same table adapter to Update the database from the DataTable.

[Vladamir]

I think my problem may be that I used a method without understanding it fully. When I built this project I did build the datagridview with a datasource. When I check the properties on it the datasource shows CallLogBindingSource. CallLog is the name of the MS Access 2003 database this uses. And yes, there are many sources coming up in my searches which only give enough information for a noob like me to be dangerous. As I look for certain lines of code I'm finding in my searches, I can see that by doing the datagridview with the datasource, massive amounts of something were written by VS 2012 Express and I cannot see them anywhere. There is the one section in Sub Form1_Load that I'm trying to understand and I am researching more information in jmcilhinney's post now. Will do my best to put this together.

[jmcilhinney]

Presumably you used the Data Source wizard to generate a typed DataSet. The DataSet is basically an in-memory representation of your database and each DataTable it contains is an in-memory representation of a table in that database. There is a table adapter for each DataTable that provides the means to move data from the database to the DataTable and back again. When you call Fill on the table adapter, it fills the DataTable with data from the database. When you call Update on the table adapter, it updates the database with the changes from the DataTable. The table adapter wraps up the database connection and the SQL commands for selecting, inserting, updating and deleting data in the corresponding database table.

The DataTable is bound to the DataGridView via a BindingSource. If the user makes changes to the data in the grid, those changes get pushed down to the BindingSource, which pushes them down to the DataTable. If you make changes to the DataTable in code, those changes get pushed up through the BindingSource to the grid. When working with the data in code, you work with the DataTable directly to Fill and Update but the rest of the time you should sue the BindingSource. It's Current property returns the record underlying the row currently selected in the grid. It's CurrentChanged event is raised when the user selects a different record in the grid. You can call its MoveNext, MovePrevious, MoveFirst and MoveLast methods and its Position property to navigate through the data.