实例136SqlServer更新数据

1.打开SMSS，创建一个Login.mdf数据库

录入测试数据

2.VS2010，创建一个VB项目，添加数据源Login.mdf，并保存

3.设计窗体

4.代码

Imports System.Data.SqlClient
Public Class Form1
    Public Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.LoginConnectionString)
    End Function
    Dim conn As SqlConnection = GetConnection()

    Private Sub Display1()
        conn.Open()

        Dim strSql As String = "Select * from userTable"
        Dim comm As New SqlCommand(strSql, conn)
        Dim myReader As SqlDataReader = comm.ExecuteReader
        Dim myTable As New DataTable
        myTable.Load(myReader)
        DataGridView1.DataSource = myTable

        conn.Close()

        DataGridView1.Rows(0).Cells(0).Selected = True
        txtUsername.Text = DataGridView1.CurrentRow.Cells(1).Value
        txtPassword.Text = DataGridView1.CurrentRow.Cells(2).Value
    End Sub
    Private Sub Display2()
        conn.Open()

        Dim strSql As String = "Select * from userTable"
        Dim comm As New SqlCommand(strSql, conn)
        Dim myReader As SqlDataReader = comm.ExecuteReader
        Dim myTable As New DataTable
        myTable.Load(myReader)
        DataGridView2.DataSource = myTable

        conn.Close()
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Display1()
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        txtUsername.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value
        txtPassword.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value
    End Sub

    Private Sub DataGridView1_RowHeaderCellChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.RowHeaderCellChanged
        txtUsername.Text = DataGridView1.Rows(e.Row.Index).Cells(1).Value
        txtPassword.Text = DataGridView1.Rows(e.Row.Index).Cells(2).Value
    End Sub

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        Display1()
        txtUsername.Text = ""
        txtPassword.Text = ""

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If txtUsername.Text = "" Or txtPassword.Text = "" Then
            MsgBox("输入用户名和密码")
            txtUsername.Focus()
            Exit Sub
        End If

        conn.Open()
        Dim strSql As String = "Insert into usertable (username,password) " _
                               & "values ('" & txtUsername.Text & "','" & txtPassword.Text & "')"
        Dim comm As New SqlCommand(strSql, conn)
        comm.ExecuteNonQuery()
        conn.Close()

        Display2()

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim intID As Integer = InputBox("输入要删除的序号")

        conn.Open()
        Dim strSql As String = "Delete userTable Where id=" & intID.ToString
        Dim comm As New SqlCommand(strSql, conn)
        comm.ExecuteNonQuery()
        conn.Close()

        Display2()
    End Sub

    Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Click

        Dim intID As Integer = DataGridView1.SelectedRows(0).Cells(0).Value

        conn.Open()
        Dim strSql As String = "Update userTable" _
                               & " set username='" & txtUsername.Text & "',password='" & txtPassword.Text & "'" _
                               & " where id=" & intID
        Dim comm As New SqlCommand(strSql, conn)
        comm.ExecuteNonQuery()
        conn.Close()

        Display2()
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
End Class