单击修改，双击保存的datagrid的VB.net代码-CSDN博客

本文链接：https://blog.csdn.net/michaellys/article/details/599240

这是来源于网上那篇著名的单击修改，双击提交的文章。此文所述代码为C#，我把它翻译成VB.net。

虽然代码经翻译后，有一些写法并不规范，但可以运行。实用比形象重要。哈哈！

Public Class modifydata
    Inherits System.Web.UI.Page
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents objModi As etgl.modi
    Protected WithEvents lblPageCount As System.Web.UI.WebControls.Label
    Protected WithEvents lblCurrentIndex As System.Web.UI.WebControls.Label
    Protected WithEvents btnFirst As System.Web.UI.WebControls.LinkButton
    Protected WithEvents btnPrev As System.Web.UI.WebControls.LinkButton
    Protected WithEvents btnNext As System.Web.UI.WebControls.LinkButton
    Protected WithEvents MyDataGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents btnLast As System.Web.UI.WebControls.LinkButton

#Region " Web 窗体设计器生成的代码 "

    '该调用是 Web 窗体设计器所必需的。
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
        Me.objModi = New etgl.modi()
        CType(Me.objModi, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Sheet1", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("ID", "ID"), New System.Data.Common.DataColumnMapping("档号", "档号"), New System.Data.Common.DataColumnMapping("顺序", "顺序"), New System.Data.Common.DataColumnMapping("原带号", "原带号"), New System.Data.Common.DataColumnMapping("分类", "分类"), New System.Data.Common.DataColumnMapping("光盘类别", "光盘类别"), New System.Data.Common.DataColumnMapping("内容", "内容"), New System.Data.Common.DataColumnMapping("拍摄时间", "拍摄时间"), New System.Data.Common.DataColumnMapping("归档", "归档"), New System.Data.Common.DataColumnMapping("数量", "数量")})})
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO Sheet1(档号, 顺序, 原带号, 分类, 光盘类别, 内容, 拍摄时间, 归档, 数量) VALUES (@档号, @顺序, @原带" & _
        "号, @分类, @光盘类别, @内容, @拍摄时间, @归档, @数量); SELECT ID, 档号, 顺序, 原带号, 分类, 光盘类别, 内容, 拍摄时间" & _
        ", 归档, 数量 FROM Sheet1"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@档号", System.Data.SqlDbType.NVarChar, 255, "档号"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@顺序", System.Data.SqlDbType.Int, 4, "顺序"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@原带号", System.Data.SqlDbType.NVarChar, 20, "原带号"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@分类", System.Data.SqlDbType.NVarChar, 255, "分类"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@光盘类别", System.Data.SqlDbType.NVarChar, 255, "光盘类别"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@内容", System.Data.SqlDbType.NVarChar, 255, "内容"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@拍摄时间", System.Data.SqlDbType.NVarChar, 255, "拍摄时间"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@归档", System.Data.SqlDbType.NVarChar, 255, "归档"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@数量", System.Data.SqlDbType.Int, 4, "数量"))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "data source=ZG-DJK3;initial catalog=WEBAPP_DJK;integrated security=SSPI;persist s" & _
        "ecurity info=False;workstation id=ZG-DJK3;packet size=4096"
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT ID, 档号, 顺序, 原带号, 分类, 光盘类别, 内容, 拍摄时间, 归档, 数量 FROM Sheet1"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'objModi
        '
        Me.objModi.DataSetName = "modi"
        Me.objModi.Locale = New System.Globalization.CultureInfo("zh-CN")
        Me.objModi.Namespace = "http://www.tempuri.org/modi.xsd"
        CType(Me.objModi, System.ComponentModel.ISupportInitialize).EndInit()

End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
        '不要使用代码编辑器修改它。
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '在此处放置初始化页的用户代码
        If Not IsPostBack Then
            btnFirst.Text = "最首页"
            btnPrev.Text = "前一页"
            btnNext.Text = "下一页"
            btnLast.Text = "最后页"
            SetBind()
        End If
    End Sub

    Private Sub SetBind()
        Me.SqlSelectCommand1.CommandText = "select * from sheet1"
        Try
            Me.loadDataSet()
            Me.MyDataGrid.DataBind()
            ShowStats()
        Catch eLoad As System.Exception
            Me.Response.Write(eLoad.Message)
        End Try
    End Sub

    Sub loadDataSet()
        Dim objDataSetTemp As etgl.modi
        objDataSetTemp = New etgl.modi()
        Try
            Me.fillDataSet(objDataSetTemp)

        Catch eFillDataSet As System.Exception
            Throw eFillDataSet
        End Try
        Try
            objModi.Clear()
            objModi.Merge(objDataSetTemp)
        Catch eLoadMerge As System.Exception
            Throw eLoadMerge
        End Try

    End Sub
    Sub fillDataSet(ByVal dataSet As etgl.modi)
        dataSet.EnforceConstraints = False
        Try
            Me.SqlConnection1.Open()
            Me.SqlDataAdapter1.Fill(dataSet)
        Catch fillException As System.Exception
            Throw fillException
        Finally
            dataSet.EnforceConstraints = True
            Me.SqlConnection1.Close()
        End Try
    End Sub

    Sub MyDataGrid_Page(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
        Dim startIndex As Integer
        startIndex = MyDataGrid.CurrentPageIndex * MyDataGrid.PageSize
        MyDataGrid.CurrentPageIndex = e.NewPageIndex
        SetBind()
        ShowStats()
    End Sub

    Sub ShowStats()
        lblCurrentIndex.Text = "第 " + (MyDataGrid.CurrentPageIndex + 1).ToString() + " 页"
        lblPageCount.Text = "总共 " + MyDataGrid.PageCount.ToString() + " 页"
    End Sub

    Sub PagerButtonClick(ByVal sender As Object, ByVal e As EventArgs)
        'used by external paging UI
        Dim arg As String = sender.CommandArgument

        Select Case arg
            Case "next"
                If (MyDataGrid.CurrentPageIndex < (MyDataGrid.PageCount - 1)) Then
                    MyDataGrid.CurrentPageIndex += 1
                End If
            Case "prev"
                If (MyDataGrid.CurrentPageIndex > 0) Then
                    MyDataGrid.CurrentPageIndex -= 1
                End If
            Case "last"
                MyDataGrid.CurrentPageIndex = (MyDataGrid.PageCount - 1)
            Case Else
                'page number
                MyDataGrid.CurrentPageIndex = System.Convert.ToInt32(arg)
        End Select
        SetBind()
        ShowStats()
    End Sub

    Private Sub myDataGrid_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles MyDataGrid.ItemDataBound
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            e.Item.Attributes.Add("onclick", "__doPostBack('" + CType(e.Item.Cells(9).Controls(0), LinkButton).ClientID.Replace("__", "$_") + "','')")
        End If
        If e.Item.ItemType = ListItemType.EditItem Then
            e.Item.Attributes.Add("ondblclick", "__doPostBack('" + CType(e.Item.Cells(10).Controls(0), LinkButton).ClientID.Replace("__", "$_") + "','')")
            Dim i As Integer
            For i = 0 To 8
                CType(e.Item.Cells(i).Controls(0), TextBox).Attributes.Add("onmouseover", "this.select()")
            Next i
        End If

End Sub

    Private Sub myDataGrid_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyDataGrid.ItemCommand
        If e.CommandName = "edit" Then
            Me.MyDataGrid.EditItemIndex = e.Item.ItemIndex
            SetBind()
        End If
        If e.CommandName = "update" Then
            Try

                Dim conn As New SqlClient.SqlConnection()
                conn = New SqlClient.SqlConnection()
                conn.ConnectionString = "data source=ZG-DJK3;initial catalog=WEBAPP_DJK;integrated security=SSPI;persist s" & _
        "ecurity info=False;workstation id=ZG-DJK3;packet size=4096"

                Dim Update_str As String
                Update_str = "update sheet1 set 档号=@档号,顺序=@顺序,原带号=@原带号,分类=@分类,光盘类别=@光盘类别,内容=@内容,拍摄时间=@拍摄时间,归档=@归档,数量=@数量 where ID=@ID"
                Dim comm As New SqlClient.SqlCommand(Update_str, conn)

                Dim parm1 As New SqlClient.SqlParameter("@档号", SqlDbType.NVarChar, 255)
                parm1.Value = CType(e.Item.Cells(0).Controls(0), TextBox).Text
                Dim parm2 As New SqlClient.SqlParameter("@顺序", SqlDbType.Int, 4)
                parm2.Value = CType(e.Item.Cells(1).Controls(0), TextBox).Text
                Dim parm3 As New SqlClient.SqlParameter("@原带号", SqlDbType.NVarChar, 50)
                parm3.Value = CType(e.Item.Cells(2).Controls(0), TextBox).Text
                Dim parm4 As New SqlClient.SqlParameter("@分类", SqlDbType.NVarChar, 255)
                parm4.Value = CType(e.Item.Cells(3).Controls(0), TextBox).Text
                Dim parm5 As New SqlClient.SqlParameter("@光盘类别", SqlDbType.NVarChar, 255)
                parm5.Value = CType(e.Item.Cells(4).Controls(0), TextBox).Text
                Dim parm6 As New SqlClient.SqlParameter("@内容", SqlDbType.NVarChar, 255)
                parm6.Value = CType(e.Item.Cells(5).Controls(0), TextBox).Text
                Dim parm7 As New SqlClient.SqlParameter("@拍摄时间", SqlDbType.NVarChar, 255)
                parm7.Value = CType(e.Item.Cells(6).Controls(0), TextBox).Text
                Dim parm8 As New SqlClient.SqlParameter("@归档", SqlDbType.NVarChar, 255)
                parm8.Value = CType(e.Item.Cells(7).Controls(0), TextBox).Text
                Dim parm9 As New SqlClient.SqlParameter("@数量", SqlDbType.Int, 4)
                parm9.Value = CType(e.Item.Cells(8).Controls(0), TextBox).Text
                Dim parm10 As New SqlClient.SqlParameter("@ID", SqlDbType.Int, 4)
                parm10.Value = Me.MyDataGrid.DataKeys(e.Item.ItemIndex)
                comm.Parameters.Add(parm1)
                comm.Parameters.Add(parm2)
                comm.Parameters.Add(parm3)
                comm.Parameters.Add(parm4)
                comm.Parameters.Add(parm5)
                comm.Parameters.Add(parm6)
                comm.Parameters.Add(parm7)
                comm.Parameters.Add(parm8)
                comm.Parameters.Add(parm9)
                comm.Parameters.Add(parm10)
                Response.Write("success!")

                conn.Open()
                comm.ExecuteNonQuery()
                conn.Close()
            Catch
            Finally
                Me.MyDataGrid.EditItemIndex = -1
                SetBind()
            End Try
        End If
    End Sub
End Class