vb.net更新数据

Question

我创建了一个程序来连接到数据库

问题是像我改变了名称“圣Fransio”到“史蒂夫” 当我去到下一个页面，并返回到页面我刚刚编辑 它不更新，名称仍然显示'圣Fransio' 但当我关闭它并重新运行程序，它只更新为'史蒂夫'。
有没有办法更新和刷新数据而不关闭它？
我需要添加一些刷新数据代码或什么？

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

上面代码中的很多问题。

• 您不关闭连接，当你打开他们
• 你不使用的参数为您更新查询
• 您不使用OleDbDataAdapter.UpdateCommand更新您的表

关闭与

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

使用参数

连接

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