Datagridview refresh

[spooke2k]

after my sql update at bottom i really wanted to refresh the datagridview prob i got is there could up to 30 records that change with that update.

 Requestcount = Nothing        Requestcount = Convert.ToInt32(InputBox("Please confirm how many cases you want?", "Records", "0"))


        If IsNumeric(Requestcount) Then
            MsgBox("Your Entered Value Is : " & Requestcount)
            sql1 = "SELECT TOP " & Requestcount & "  DataImport.SERVICE_PROBLEM_ID, DataImport.OPERATOR_REFERENCE, DataImport.DIRECTORY_NUMBER, DataImport.DATE_LAST_TOUCHED, DataImport.TIMES_TRANSFERRED, DataImport.IssuedtoAgent, DataImport.AgentIssuedtoo, DataImport.[Date Issued], DataImport.Completed FROM DataImport WHERE(((DataImport.IssuedtoAgent)=False)) ORDER BY DataImport.DATE_LAST_TOUCHED;"
        Else : GoTo home1
        End If




        dbPath1 = VB6.GetPath & "\" & "House.mdb"


        Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & dbPath1 & "'; Jet OLEDB:database password=123")
        Dim adapter As New OleDbDataAdapter(sql1, con1)
        Dim dt As New DataTable("DataImport")
        Dim dr As DataRow
        Dim holding(30) As String
        Dim touched(30) As String
        Dim Countit As Integer


        adapter.Fill(dt)


        For Each dr In dt.Rows
            holding(Countit) += dr("SERVICE_PROBLEM_ID").ToString()
            touched(Countit) += dr("TIMES_TRANSFERRED").ToString()
            Countit = Countit + 1
        Next


        con1.Close()


        DataGridView1.DataSource = dt 'dont need it now


        Dim sql As String
        Dim loopcount As Integer
        loopcount = 0




        Do Until Loopcount = Countit


            sql = "UPDATE DataImport SET DataImport.IssuedtoAgent = 1, DataImport.AgentIssuedtoo = '" & ActUser & "', DataImport.[Date Issued] = '" & Hitme & "' WHERE DataImport.SERVICE_PROBLEM_ID =" & holding(loopcount) & " AND  DataImport.TIMES_TRANSFERRED = " & touched(loopcount) & ""


            Dim cmd As New OleDbCommand(sql, con1)


            con1.Open()
            cmd.ExecuteNonQuery()
            con1.Close()


            loopcount = loopcount + 1


        Loop

also if anyone know how to to mass update from that data grid back to a different access table wouold be good too

tnaks

 

[jmcilhinney]

You're going about this in completely the wrong way. What you need to do is answer your last question and the first issue goes away. What you should be doing is:

1. Use a data adapter to populate a DataTable.
2. Bind that DataTable to the DataGridView via a BindingSource.
3a. If the user needs to make changes, they do so through the grid.
3b. If you need to make changes in code, you do so via the BindingSource.
4. You save all the changes from the DataTable back to the database using the same data adapter.

That's it, that's all. Here's a quick example:

Private ReadOnly table As New DataTable
Private ReadOnly adapter As New OleDbDataAdapter("SELECT * FROM MyTable", "connection string here")
Private ReadOnly builder As New OleDbCommandBuilder(Me.adapter)

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.adapter.Fill(Me.table)
    Me.BindingSource1.DataSource = Me.table
    Me.DataGridView1.DataSource = Me.BindingSource1
End Sub

Private Sub AddNewRow(column1 As String, column2 As String)
    Dim row = DirectCast(Me.BindingSource1.AddNew(), DataRowView)

    row("Column1") = column1
    row("Column2") = column2

    Me.BindingSource1.EndEdit()
End Sub

Private Sub UpdateExistingRow(id As Integer, column1 As String, column2 As String)
    Dim rowIndex = Me.BindingSource1.Find("ID", id)
    Dim row = DirectCast(Me.BindingSource1(rowIndex), DataRowView)

    row("Column1") = column1
    row("Column2") = column2
End Sub

Private Sub saveButton_Click(sender As Object, e As EventArgs) Handles saveButton.Click
    Me.BindingSource1.EndEdit()
    Me.adapter.Update(Me.table)
End Sub

There's no need to refresh anything after saving because the local data is modified first.

 

[spooke2k]

hi thank you so much for help, i have one linked question onhighlighted gives error GlobalVariables without an as clause do i need to declare something extra.


thank you

Dim rowIndex = Me.BindingSource1.Find("ID", id)
Dim row = DirectCast(Me.BindingSource1(rowIndex), DataRowView)

 

[jmcilhinney]

That first line should be:

Dim rowIndex = CInt(Me.BindingSource1.Find("ID", id))

Both should then work if you have Option Infer On, which it is by default.

 

[spooke2k]

Resolved thanks