Link to home
Start Free TrialLog in
Avatar of lep11
lep11

asked on

Update a DataGridView after appending records to an Access file

I am using the code below to append new values of a field called "symbol" in an Access table called mydata -- which is bound (via BindingSource) to a DataGridView.  The update works correctly, but I need to add code at the end that will force the DataGridView to show the new field entries.  Right now, I can only see the new record values if I restart the app.

Therefore, how can I force the BindingSource to reload(?) the revised table from the Access file?

 

        Dim i, j, recordsadded As Integer
        Dim buff(), inputsymbolstr, newsymbolstr(), oldsymbolstr() As String
        inputsymbolstr = InputBox("Enter symbols separated by commas, e.g., ABC, BDE, FGH, ...")
        newsymbolstr = Split(inputsymbolstr, ",")


        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydata.mdb"
        Dim cn As New OleDbConnection(ConnString)
        cn.Open()
        Dim qry As String
        Dim cmd As New OleDbCommand(qry, cn)
       
        qry = "SELECT * FROM [" & "mydata" & "] "
        cmd.CommandText = qry
        cmd.Connection = cn
        Dim drdata As OleDbDataReader = cmd.ExecuteReader

        Cnt = 0
        Do While drdata.Read
            Cnt += 1
            ReDim Preserve oldsymbolstr(Cnt)
            oldsymbolstr(Cnt) = drdata.Item("symbol")
        Loop
        drdata.Close()

        For j = 1 To UBound(newsymbolstr)
            If Cnt > 0 Then
                For i = 1 To UBound(oldsymbolstr)
                    If oldsymbolstr(i) = newsymbolstr(j) Then GoTo nextsymb
                Next
            End If
            qry = " INSERT INTO [" & "mydata" & "] ( symbol )"
            qry &= " VALUES (" & "'" & newsymbolstr(j) & "'" & ")"
            Dim cmd1 As New OleDbCommand(qry, cn)
            recordsadded = cmd1.ExecuteNonQuery()
nextsymb:
        Next
        cn.Close()
ASKER CERTIFIED SOLUTION
Avatar of nepaluz
nepaluz
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nasir Razzaq
Try adding a

BindingSource1.CurrencyManager.Refresh()

at the end.
If you used a TableAdapter to read the information into the BindingSource, try calling the Fill function again so that it reads the data from the database:
TableAdapter1.Fill(BindingSource1.DataSource)
Avatar of lep11
lep11

ASKER

Solution seemed to be for real-time data entry rather than how to update a datagridview after the Access file is modified.