<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Diagnostics" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
BindGrid()
End If
End Sub
Public Sub BindGrid()
MyDataGrid.DataSource = GetAllUsers()
MyDataGrid.DataBind()
End Sub
Public Function GetDatabaseConnectionString() As String
return "Data Source=xxxx; User Id=xxxx; Password=xxxx; Initial Catalog=xxxx"
End Function
Public Sub MyDataGrid_Edit(sender As Object, e As DataGridCommandEventArgs)
label1.Text = ""
MyDataGrid.EditItemIndex = CType(e.Item.ItemIndex, Integer)
BindGrid()
End Sub
Public Sub MyDataGrid_Cancel(sender As Object, e As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Public Sub MyDataGrid_Update(sender As Object, e As DataGridCommandEventArgs)
Dim UserId_value As Integer = Integer.Parse(CType(e.Item.FindControl("lblUserID"),Label).Text)
Dim Name_value As String = CType(e.Item.FindControl("txtName"), TextBox).Text
Dim UserName_value As String = CType(e.Item.FindControl("txtUserName"), TextBox).Text
Dim Password_value As String = CType(e.Item.FindControl("txtPassword"), TextBox).Text
Dim RoleLevel_value As Integer = Integer.Parse(CType(e.Item.FindControl("ddlRoleLevel"), DropDownList).SelectedItem.Value)
Try
Dim numRowsAffected As Integer = UpdateUser(UserID_value, Name_value, UserName_value, Password_value, RoleLevel_value)
label1.Text = "<font color=red>Record Successfully Updated</font>"
Catch myError As Exception
label1.Text = "Database Error: " & myError.ToString()
End Try
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Public Function UpdateUser(UserID As Integer, Name As String, UserName As String, Password As String, RoleLevel As Integer) As Integer
Dim connection As SqlConnection = new SqlConnection(GetDatabaseConnectionString())
Dim command As SqlCommand = new SqlCommand("spEditUser", connection)
command.CommandType = CommandType.StoredProcedure
Dim param0 As SqlParameter = new SqlParameter("@UserID",SqlDbType.Int)
param0.Direction = ParameterDirection.Input
param0.Value = UserID
command.Parameters.Add(param0)
Dim param1 As SqlParameter = new SqlParameter("@RealName",SqlDbType.VarChar, 100)
param1.Direction = ParameterDirection.Input
param1.Value = Name
command.Parameters.Add(param1)
Dim param2 As SqlParameter = new SqlParameter("@UserName",SqlDbType.VarChar,50)
param2.Direction = ParameterDirection.Input
param2.Value = UserName
command.Parameters.Add(param2)
Dim param3 As SqlParameter = new SqlParameter("@Password",SqlDbType.VarChar,50)
param3.Direction = ParameterDirection.Input
param3.Value = Password
command.Parameters.Add(param3)
Dim param4 As SqlParameter = new SqlParameter("@RoleLevel",SqlDbType.Int)
param4.Direction = ParameterDirection.Input
param4.Value = RoleLevel
command.Parameters.Add(param4)
connection.Open()
Dim numRowsAffected As Integer = command.ExecuteNonQuery()
connection.Close()
return numRowsAffected
End Function
Public Sub MyDataGrid_Delete(sender As Object, e As DataGridCommandEventArgs)
label1.Text = "Thanks for trying to delete an item in the DataGrid!"
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Public Function GetAllUsers() As DataView
Dim command As SqlDataAdapter = new SqlDataAdapter("spGetAllUsers", GetDatabaseConnectionString())
command.SelectCommand.CommandType = CommandType.StoredProcedure
Dim ds As DataSet = new DataSet()
command.Fill(ds, "Users")
return ds.Tables("Users").DefaultView
End Function
Public Function GetRoles() As DataView
Dim command As SqlDataAdapter = new SqlDataAdapter("spGetRoles", GetDatabaseConnectionString())
command.SelectCommand.CommandType = CommandType.StoredProcedure
Dim ds As DataSet = new DataSet()
command.Fill(ds, "Roles")
return ds.Tables("Roles").DefaultView
End Function
Public Sub DataGrid_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
'Determine the ListItem Type
Dim itemType As ListItemType = e.Item.ItemType
'We only want to run this code for items in edit mode
If (itemType = ListItemType.EditItem) Then
'Locate our DropDownList
Dim myList As DropDownList = CType(e.Item.FindControl("ddlRoleLevel"), DropDownList)
'Grab the current row of data - we need that so we know what value to select in the ddl
Dim myRow As DataRowView = CType(e.Item.DataItem, DataRowView)
'Grab the value we want to select from the current row of data in our DataGrid/List/Repeater
Dim myRole As String = myRow("RoleLevel").ToString()
'Set the Text and Value field for our DropDownList
myList.DataTextField = "RoleName"
myList.DataValueField = "RoleID"
'Fill our DropDownList with the standard list of data
myList.DataSource = GetRoles()
myList.DataBind()
'Use the FindByValue function to locate the item we want to select
'There is also a FindByText function that can be used in the same way
myList.Items.FindByValue(myRole).Selected = true
End If
End Sub
</script>
<html>
<body style="font: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Edit/Delete Users</font></h3>
<asp:datagrid id="MyDataGrid"
runat="server"
bordercolor="black"
borderwidth="1"
cellpadding="3"
font-name="Verdana"
font-size="8pt"
headerstyle-backcolor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
OnDeleteCommand="MyDataGrid_Delete"
OnItemDataBound="DataGrid_ItemDataBound"
AutoGenerateColumns="false">
<columns>
<asp:editcommandcolumn edittext="Edit" canceltext="Cancel" updatetext="Update" itemstyle-wrap="false"/>
<asp:buttoncolumn text="Delete" commandname="Delete"/>
<asp:templatecolumn headertext="UserID" visible="false">
<itemtemplate>
<asp:label id="lblUserID" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "UserID") %>'/>
</itemtemplate>
<edititemtemplate>
<asp:label id="lblUserID" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "UserID") %>'/>
</edititemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="Name">
<itemtemplate>
<asp:label id="lblName" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "RealName") %>'/>
</itemtemplate>
<edititemtemplate>
<asp:textbox id="txtName" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "RealName") %>'/>
</edititemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="UserName">
<itemtemplate>
<asp:label id="lblUserName" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "UserName") %>'/>
</itemtemplate>
<edititemtemplate>
<asp:textbox id="txtUserName" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "UserName") %>'/>
</edititemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="Password">
<itemtemplate>
<asp:label id="lblPassword" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "Password") %>'/>
</itemtemplate>
<edititemtemplate>
<asp:textbox id="txtPassword" runat="server" text='<%# DataBinder.Eval(Container.DataItem, "Password") %>'/>
</edititemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="Role">
<itemtemplate>
<asp:label id="lblRoleLevel" text='<%# DataBinder.Eval(Container.DataItem, "RoleName") %>' runat="server"/>
</itemtemplate>
<edititemtemplate>
<asp:dropdownlist id="ddlRoleLevel" runat="server"/>
</edititemtemplate>
</asp:templatecolumn>
</columns>
</asp:datagrid>
</form>
<br>
<asp:label id="label1" runat="server"/>
</body>
</html>
浙公网安备 33010602011771号