(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
