(VB.net) 利用DataGrid实现查找， 编辑， 修改， 更新， 删除的功能。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class WebTest
    Inherits System.Web.UI.Page
    Protected WithEvents ddlSalesArea As System.Web.UI.WebControls.DropDownList
    Protected WithEvents dgSFDetail As System.Web.UI.WebControls.DataGrid
    Protected WithEvents dgsave As System.Web.UI.WebControls.DataGrid
    Protected WithEvents Btnsearch As System.Web.UI.WebControls.Button
    Dim myConn As SqlConnection
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '在此处放置初始化页的用户代码 dgsave.EditItemIndex = 2 可编辑的行号
        myConn = New SqlConnection(ConfigurationSettings.AppSettings("SqlServerConnectionString"))

 

    End Sub
    '查询数据的按钮
    Private Sub Btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnsearch.Click
        BindData()
        BindData2()
        dgsave_BindDataGrid()
    End Sub

    '执行不返回结果集的sql 语句
    Private Sub ExecuteSql(ByVal strsql)
        Try
            Dim myConn As SqlConnection
            Dim MyCommand As SqlCommand
            myConn = New SqlConnection(ConfigurationSettings.AppSettings("SqlServerConnectionString")) 'web.config中读取
            myConn.Open()
            MyCommand = New SqlCommand(strsql, myConn)
            MyCommand.ExecuteNonQuery()
            myConn.Close()
        Catch ex As Exception
            Response.Write("<script language = 'javascript'>alert('" + ex.Message + "');</script>")
        Finally
            myConn.Close()
        End Try

    End Sub
    '绑定数据， 重新显示资料 
    Private Sub BindData()
        Dim ls_dept As String
        Dim ls_sql As String
        Dim li_count As Integer
        Dim i As Integer
        Dim Dset1 As DataSet
        Dim MyCommand As SqlDataAdapter
        ls_dept = ddlSalesArea.SelectedValue.ToString()
        ls_sql = ls_sql & "SELECT * FROM PUB_TELPHONE WHERE DEPT = '" & ls_dept & "' "
        MyCommand = New SqlDataAdapter(ls_sql, myConn)
        Dset1 = New DataSet
        MyCommand.Fill(Dset1, "phone")
        dgSFDetail.DataSource = Dset1.Tables("phone").DefaultView
        dgSFDetail.DataBind()
        li_count = dgSFDetail.Items.Count

        For i = 1 To li_count

            If (i Mod 2 = 0) Then
                'dgSFDetail.SelectedItemStyle.BackColor = ""

            Else

            End If

        Next

    End Sub

    'dgsave的绑定
    Private Sub BindData2()
        Dim ls_dept As String
        Dim ls_sql As String
        Dim Dset2 As DataSet
        Dim MyCommand As SqlDataAdapter
        'ls_dept = ddlSalesArea.SelectedValue.ToString()
        ls_dept = ""
        ls_sql = ls_sql & "SELECT * FROM PUB_TELPHONE WHERE DEPT = '" & ls_dept & "' "
        MyCommand = New SqlDataAdapter(ls_sql, myConn)
        Dset2 = New DataSet
        MyCommand.Fill(Dset2, "phone")
        dgsave.DataSource = Dset2.Tables("phone").DefaultView
        dgsave.DataBind()
    End Sub

    '绑定DataGrid

    Private Sub dgsave_BindDataGrid()
        Dim ls_dept As String
        Dim ls_sql As String
        Dim Dset2 As DataSet
        Dim MyCommand As SqlDataAdapter

        ls_dept = ""
        ls_sql = ls_sql & "SELECT * FROM PUB_TELPHONE WHERE DEPT = '" & ls_dept & "' "
        MyCommand = New SqlDataAdapter(ls_sql, myConn)
        Dset2 = New DataSet
        MyCommand.Fill(Dset2, "phone")
        dgsave.DataSource = Dset2.Tables("phone").DefaultView

        Dim phoneTable As DataTable = Dset2.Tables("phone")
        Dim phoneView As DataView = phoneTable.DefaultView

        phoneView.AllowDelete = False
        Dim AddRow As DataRowView = phoneView.AddNew()
        AddRow("user_name") = "大山"
        AddRow("user_account") = ""
        AddRow("dept") = ""
        AddRow("telphone") = ""
        AddRow("fax") = ""
        AddRow("mobile") = ""
        dgsave.EditItemIndex = 0
        dgsave.DataBind()
    End Sub

    '点击编辑后更新的代码
    Private Sub dgsave_UpdateCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgsave.UpdateCommand
        Dim ls_username, ls_account, ls_mobile, ls_telphone, ls_fax, ls_dept As String
        Dim StrSql As String
        Dim tb As TextBox

        tb = CType(e.Item.Cells(0).Controls(0), TextBox)
        ls_username = tb.Text
        tb = CType(e.Item.Cells(1).Controls(0), TextBox)
        ls_account = tb.Text
        tb = CType(e.Item.Cells(2).Controls(0), TextBox)
        ls_dept = tb.Text
        tb = CType(e.Item.Cells(3).Controls(0), TextBox)
        ls_fax = tb.Text
        tb = CType(e.Item.Cells(4).Controls(0), TextBox)
        ls_telphone = tb.Text
        tb = CType(e.Item.Cells(5).Controls(0), TextBox)
        ls_mobile = tb.Text

        'StrSql = StrSql & "update pub_telphone set user_name = '" & ls_username & "', user_account = '" & ls_account & "' , dept = '" & ls_dept & "', "
        'StrSql = StrSql & " mobile = '" & ls_mobile & "', telphone = '" & ls_telphone & "' , fax = '" & ls_fax & "'"
        'StrSql = StrSql & " where user_name = '" & ls_username & "'"
        '插入的Sql语句

        StrSql = StrSql & "Insert into pub_telphone (user_name , user_account , dept , telphone , fax , mobile ) values ('" & ls_username & "','" & ls_account & "', '" & ls_dept & "', '" & ls_fax & "', '" & ls_telphone & "' , '" & ls_mobile & "' )"

        ExecuteSql(StrSql)
        dgsave_BindDataGrid()
        '将项切换出编辑模式dgsave.EditItemIndex = -1
    End Sub
    '编辑按钮的代码
    Private Sub dgsave_EditCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgsave.EditCommand
        dgsave.EditItemIndex = e.Item.ItemIndex
        BindData2()
    End Sub

    '页面改变时执行的代码
    Private Sub dgsave_PageIndexChanged(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgsave.PageIndexChanged
        dgsave.CurrentPageIndex = e.NewPageIndex
        BindData2()
    End Sub
    '删除的代码
    Private Sub dgsave_DeleteCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgsave.DeleteCommand
        If (dgsave.Items.Count = 1) Then
            If (dgsave.CurrentPageIndex <> 0) Then
                dgsave.CurrentPageIndex = dgsave.CurrentPageIndex - 1
            End If
        End If
        Dim strsql = "delete from pub_telphone where user_name = ' " & e.Item.Cells(0).Text & " ' "
        ExecuteSql(strsql)
        BindData2()

    End Sub

    '取消
    Private Sub dgsave_CancelCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgsave.CancelCommand
        dgsave.EditItemIndex = -1
        BindData2()
    End Sub

    Private Sub InitializeComponent()

    End Sub

    '更新的代码

    Private Sub dgSFDetail_UpdateCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgSFDetail.UpdateCommand
        Dim ls_username, ls_account, ls_mobile, ls_telphone, ls_fax, ls_dept As String
        Dim tb As TextBox
        Dim StrSql As String

        tb = CType(e.Item.Cells(0).Controls(0), TextBox)
        ls_username = tb.Text
        tb = CType(e.Item.Cells(1).Controls(0), TextBox)
        ls_account = tb.Text
        tb = CType(e.Item.Cells(2).Controls(0), TextBox)
        ls_dept = tb.Text
        tb = CType(e.Item.Cells(3).Controls(0), TextBox)
        ls_fax = tb.Text
        tb = CType(e.Item.Cells(4).Controls(0), TextBox)
        ls_telphone = tb.Text
        tb = CType(e.Item.Cells(5).Controls(0), TextBox)
        ls_mobile = tb.Text

        StrSql = StrSql & "update pub_telphone set user_name = '" & ls_username & "', user_account = '" & ls_account & "' , dept = '" & ls_dept & "', "
        StrSql = StrSql & " mobile = '" & ls_mobile & "', telphone = '" & ls_telphone & "' , fax = '" & ls_fax & "'"
        StrSql = StrSql & " where user_name = '" & ls_username & "'"
        ExecuteSql(StrSql)
        dgSFDetail.EditItemIndex = -1
        BindData()
        '将项切换出编辑模式dgsave.EditItemIndex = -1
    End Sub
    '删除按钮的代码
    Private Sub dgSFDetail_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgSFDetail.DeleteCommand
        ' Add code to delete data from data source.
        Dim StrSql As String
        If (dgSFDetail.Items.Count = 1) Then
            If (dgSFDetail.CurrentPageIndex <> 0) Then
                dgSFDetail.CurrentPageIndex = dgSFDetail.CurrentPageIndex - 1
            End If
        End If
        'Dim myDeleteButton As Button

        'myDeleteButton = CType(e.Item.FindControl("Delete"), Button)

        'myDeleteButton.Text = "删除此行"
        'myDeleteButton.Attributes.Add("onclick", "return confirm('您真的要删除第 " + e.Item.ItemIndex.ToString() + " 行吗？');")
        StrSql = StrSql & "delete from pub_telphone where user_name = '" & e.Item.Cells(0).Text & " ' "
        ExecuteSql(StrSql)
        BindData()
    End Sub

    '编辑的代码
    Private Sub dgSFDetail_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgSFDetail.EditCommand

        dgSFDetail.EditItemIndex = e.Item.ItemIndex
        BindData()

    End Sub
    '取消的代码
    Private Sub dgSFDetail_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgSFDetail.CancelCommand
        dgSFDetail.EditItemIndex = -1
        BindData()
    End Sub

    '保存代码 
    'Private Sub dgSFDetail_ItemCreated(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
    '    e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor=currentcolor;this.style.cursor='default';")
    '    e.Item.Attributes.Add("onmouseover", "currentcolor=this.style.backgroundColor;this.style.backgroundColor='#FFFFCC';this.style.cursor='hand';")
    'End Sub

    'Private Sub dgSFDetail_ItemDataBound(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)

    '    e.Item.BackColor = Color.Blue

    'End Sub
End Class

转载于:https://www.cnblogs.com/yqy542006/archive/2007/03/19/679548.html