vb.net update data

Question

I have created a program to connected to database

the problem is like I changing the Name 'San Fransio' to 'Steve' When I go to the next page and back to the page that I just edited it dons't update, the name still showing 'San Fransio' but when I closed it and rerun the program, that it only updated to 'Steve'.
it's there any way to update and refresh the data without closed it ?
i need add some refresh data code or something?

Public Class Form2  
    Dim cnn As New OleDb.OleDbConnection  
    Dim sql As String = "SELECT * FROM sr"  
    Dim ds As New DataSet  
    Dim da As OleDb.OleDbDataAdapter  
    Dim i As Integer  
    Dim len As Integer  
    Dim ind As Integer = 0  
    Dim arrNumbers() As String  
    Dim no As Integer  



    Private Sub btnSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSrch.Click
        Dim sql1 = InputBox("Service Number :", "Search", " ")
        Dim no As Integer
        Dim found As Boolean = False

        For no = 0 To len - 1
            If ds.Tables("db").Rows(no).Item(0).ToString.ToLower = sql1.ToString.ToLower Then
                i = (no)
                nav()
                found = True

            End If

        Next
        If (found = False) Then
            MsgBox("Search Not Found")
        End If
    End Sub

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cnn = New OleDb.OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=C:\Users\Lim\Documents\db.mdb"
        da = New OleDb.OleDbDataAdapter(sql, cnn)
        da.Fill(ds, "db")
        len = da.Fill(ds, "db")

        i = len - 1
        nav()

    End Sub
    Private Sub nav()
        txtsr.Text = ds.Tables("db").Rows(i).Item(0)
        txtname.Text = ds.Tables("db").Rows(i).Item(1)
        txttel.Text = ds.Tables("db").Rows(i).Item(2)
        txtdate.Text = ds.Tables("db").Rows(i).Item(3)
        txtprob.Text = ds.Tables("db").Rows(i).Item(4)
        txtmodel.Text = ds.Tables("db").Rows(i).Item(5)
        txtacc.Text = ds.Tables("db").Rows(i).Item(6)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If (i = 0) Then
            MsgBox("This is the first page")
        Else
            i = i - 1
            nav()

        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If (i = len - 1) Then
            MsgBox("This is the last page")
        Else
            i = i + 1
            nav()
        End If
    End Sub



    Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
        Dim cnn As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=C:\Users\Lim\Documents\db.mdb"
        cnn.Open()

        Dim sqlstr = "UPDATE sr set cname='" & txtname.Text & "',tel='" & txttel.Text & "',cdate='" & txtdate.Text & "',prob='" & txtprob.Text & "',model='" & txtacc.Text & "'WHERE SR=" & txtsr.Text & ""

        cmd = New OleDb.OleDbCommand(sqlstr, cnn)
        cmd.ExecuteNonQuery()

        cnn.Close()


    End Sub

End Class

Answer 1

Many problems in the code above.

• You don't close the connections when you open them
• You don't use parameters for your update query
• You don't use the OleDbDataAdapter.UpdateCommand to update your table

Close the connection with

    Using cnn = New OleDb.OleDbConnection
       .....
    End Using

use parameters

 Dim sqlstr = "UPDATE sr set cname=?,tel=?,cdate=?,prob=?,model=? WHERE SR=?"
 da.UpdateCommand = new OleDbCommand(sqlstr, cnn)
 da.UpdateCommand.AddWithValue("@cName", txtname.Text)
 da.UpdateCommand.AddWithValue("@tel", txttel.Text)

 ' and so on but remember to follow exactly the order in which they are expected