index.aspx
<%@ Page Debug="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
If Not IsPostBack Then
Call myDataBind() '绑定数据
End If
End Sub
'单击编辑时,执行该事件过程。
Sub myDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
myDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
Call myDataBind()
End Sub
'单击取消时,执行该事件过程。
Sub myDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
myDataGrid.EditItemIndex = -1
Call myDataBind()
End Sub
'单击更新时,执行该事件过程。
Sub myDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
'以下首先获取客户更新的值
Dim objName,objTel,objEmail,objIntro As textbox '声明文本框控件变量
objName=E.Item.Cells(3).Controls(1) '获取姓名
objTel=E.Item.Cells(4).Controls(1) '获取电话
objEmail=E.Item.Cells(5).Controls(1) '获取E-mail
objIntro=E.Item.Cells(6).Controls(1) '获取简介
'下面建立Connection对象,并利用含有参数的SQL语句更新数据库
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("address.mdb"))
Dim strSql="Update users Set name=@name,tel=@tel,email=@email,intro=@intro Where id=@id"
Dim cmd As New OleDbCommand(strSql, conn)
'下面给参数赋值
cmd.Parameters.Add(New OleDbParameter("@name",OleDbType.VarWChar,5))
cmd.Parameters("@name").Value=objName.Text
cmd.Parameters.Add(New OleDbParameter("@tel",OleDbType.VarWChar,13))
cmd.Parameters("@tel").Value=objTel.Text
'下面给@email赋值,如果客户没有输入,则赋NULL值
cmd.Parameters.Add(New OleDbParameter("@email",OleDbType.VarWChar,50))
If objEmail.Text<>"" Then
cmd.Parameters("@email").Value=objEmail.Text
Else
cmd.Parameters("@email").Value=DBNull.Value '赋值NULL
End If
'下面给@intro赋值,如果客户没有输入,则赋NULL值
cmd.Parameters.Add(New OleDbParameter("@intro",OleDbType.VarWChar))
If objIntro.Text<>"" Then
cmd.Parameters("@intro").Value=objIntro.Text
Else
cmd.Parameters("@intro").Value=DBNull.Value '赋值NULL
End If
cmd.Parameters.Add(New OleDbParameter("@id",OleDbType.Integer))
cmd.Parameters("@id").Value=myDataGrid.DataKeys(E.Item.ItemIndex)
'执行更新操作
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
'重新绑定数据
myDataGrid.EditItemIndex = -1
Call myDataBind()
End Sub
'该过程用于删除行,不过要注意其中会判断是最后一页,且只有一行的情况
Sub MyDataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
'建立Connection对象
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("address.mdb"))
'建立Command对象
Dim strSql As String
strSql="Delete from users Where id=" & MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
Dim cmd As New OleDbCommand(strSql, conn)
'执行删除操作
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
'下面先判断是否是最后一页,且只有一行的情况
If myDataGrid.PageCount>1 And myDataGrid.CurrentPageIndex=(myDataGrid.PageCount-1) And myDataGrid.Items.Count=1 Then
lblPageIndex.Text=(myDataGrid.CurrentPageIndex-1) '将新页码保存在标签中
End If
'重新绑定数据
Call myDataBind()
End Sub
'该过程用于添加删除确认框
Sub MyDataGrid_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
'这个判断语句表示,只有对于数据行才执行,对于标题栏和脚注栏则不执行
If e.Item.ItemType<>ListItemType.Header And e.Item.ItemType<>ListItemType.Footer Then
'下面找到删除按钮控件,它其实是一个LinkButton控件
Dim lbtnDel As LinkButton '定义一个LinkButton控件
lbtnDel=e.Item.Cells(1).Controls(0) '它位于第1列第0个控件
'下面添加JavaScript事件
lbtnDel.Attributes.Add("onclick","javascript:return confirm('您真的要删除 " & e.Item.DataItem("name") & " 吗?');")
End If
End Sub
'该过程用于分页
Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
lblPageIndex.Text=e.NewPageIndex '将要显示的页码保存在标签中
Call myDataBind() '绑定数据
End Sub
'该过程用于排序
Sub MyDataGrid_Sort(Sender As Object, E As DataGridSortCommandEventArgs)
lblSortField.Text=E.SortExpression '将排序字段保存在标签中
myDataBind() '绑定数据
End Sub
'该过程用来查询数据
Sub Enter_Click(Sender As Object, E As EventArgs)
lblKeyword.Text=Trim(txtKeyword.Text) '将查询关键词保存在标签中
lblPageIndex.Text=0 '查询数据后,原来的页码就有问题了,此时应该显示第0页
myDataBind() '绑定数据
End Sub
'该过程用于绑定数据
Sub myDataBind()
'首先读取3个重要变量,分别用来设置页码、查询和排序
Dim intPageIndex As Integer
Dim strKeyword,strSortField As String
intPageIndex=Convert.ToInt32(lblPageIndex.Text)
strKeyword=lblKeyword.Text
strSortField=lblSortField.Text
'下面根据三个变量绑定数据
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("address.mdb")) '建立Connection对象
Dim adp As New OleDbDataAdapter("select * from users", conn) '建立DataAdapter对象
Dim ds As New DataSet() '建立DataSet对象
adp.Fill(ds, "users") '填充DataSet
'下面根据查找关键词设置筛选条件
Dim strSql As String
If strKeyword<>"" Then
Dim arryKeyword(),strFilter As String
Dim I As Integer
arryKeyword=Split(strKeyword," ") '按空格拆分多个关键词
strFilter="name Like '%" & arryKeyword(0) & "%'" '先添加第一个关键词
For I=1 To arryKeyword.Length-1
strFilter &= " And name Like '%" & arryKeyword(I) & "%'" '注意And前留一个空格
Next
ds.Tables("users").DefaultView.RowFilter = strFilter '设置筛选条件
End If
'下面根据排序字段设置排序效果,为了简单,始终按倒叙排列
If strSortField<>"" Then
ds.Tables("users").DefaultView.Sort=strSortField & " Desc" '注意Desc前留一个空格
End If
'下面根据页面变量设置要显示的页面
myDataGrid.CurrentPageIndex = intPageIndex
'下面绑定数据
myDataGrid.DataSource=ds.Tables("users").DefaultView
myDataGrid.DataBind()
End Sub
</script>
<html>
<body>
<h3 align="center">通讯录综合示例(一)</h3>
<form runat="server">
请输入关键词<asp:TextBox id="txtKeyword" runat="server"/>
<asp:Button id="Enter" Text="确定" onClick="Enter_Click" runat="server"/>
<asp:HyperLink Text="添加新记录" NavigateUrl="add.aspx" runat="server"/>
<p>
<asp:DataGrid id="myDataGrid" Width="100%" HeaderStyle-BackColor="#aaaadd"
DataKeyField="id"
OnEditCommand="myDataGrid_Edit"
OnCancelCommand="myDataGrid_Cancel"
OnUpdateCommand="myDataGrid_Update"
OnDeleteCommand="myDataGrid_Delete"
OnItemDataBound="MyDataGrid_ItemDataBound"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="MyDataGrid_Page"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
AllowSorting="True"
OnSortCommand="MyDataGrid_Sort"
AutoGenerateColumns="False"
runat="server">
<Columns>
<asp:EditCommandColumn EditText="编辑" UpdateText="更新" CancelText="取消" ItemStyle-Wrap="False"/>
<asp:ButtonColumn Text="删除" CommandName="Delete"/>
<asp:BoundColumn HeaderText="编号" DataField="id" ReadOnly="True" SortExpression="id" />
<asp:TemplateColumn HeaderText="姓名" SortExpression="name">
<ItemTemplate>
<a href="#" onClick="open('particular.aspx?id=<%# Container.DataItem("id") %>','详细信息','width=500,height=300,left=150,top=150,resizable=0,scrollbars=1,status=no,toolbar=no,location=no,menu=no')"><%# Container.DataItem("name") %></a>
</ItemTemplate>
<EditItemTemplate>
<asp:Textbox id="txtName" Text='<%# Container.DataItem("name") %>' runat="server"/>
<asp:RequiredFieldValidator id="require1" ControlToValidate="txtName" ErrorMessage="必须输入用户名" runat="server"/>
<asp:RegularExpressionValidator id="Regular1" ControlToValidate="txtName" ValidationExpression="^[/u4e00-/u9fa5]{2,5}$" ErrorMessage="必须输入2位到5位汉字" runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="电话" SortExpression="tel">
<ItemTemplate>
<asp:Label id="lblTel" Text='<%# Container.DataItem("tel") %>' runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:Textbox id="txtTel" Text='<%# Container.DataItem("tel") %>' runat="server"/>
<asp:RequiredFieldValidator id="require2" ControlToValidate="txtTel" ErrorMessage="必须输入电话" runat="server"/>
<asp:RegularExpressionValidator id="Regular2" ControlToValidate="txtTel" ValidationExpression="^([0-9]{3,4}-[0-9]{7,8})|([0-9]{11})$" ErrorMessage="电话格式不正确" runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="E-mail" SortExpression="email">
<ItemTemplate>
<asp:HyperLink Text='<%# Container.DataItem("email") %>' NavigateUrl='<%# "mailto:" & Container.DataItem("email") %>' runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:Textbox id="txtEmail" Columns="20" Text='<%# Container.DataItem("email") %>' runat="server"/>
<asp:RegularExpressionValidator id="Regular3" ControlToValidate="txtEmail" ValidationExpression="^.{1,}@.{1,}/.[a-zA-Z]{2,3}$" ErrorMessage="E-mail格式不正确" runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="简介" SortExpression="intro">
<ItemTemplate>
<asp:Label id="lblIntro" Text='<%# Container.DataItem("intro") %>' runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:Textbox id="txtIntro" TextMode="multiline" Columns="20" Rows="2" Text='<%# Container.DataItem("intro") %>' runat="server"/>
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
<asp:Label id="lblPageIndex" Text="0" Visible="False" runat="server"/>
<asp:Label id="lblKeyword" Text="" Visible="False" runat="server"/>
<asp:Label id="lblSortField" Text="id" Visible="False" runat="server"/>
</form>
</body>
</html>
======================================================
add.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub Enter_Click(Sender As Object, E As EventArgs)
'建立Connection对象
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("address.mdb"))
'建立Command对象,注意这里使用了含有参数的SQL语句
Dim strSql="Insert Into users(name,tel,email,intro,submit_date) Values(@name,@tel,@email,@intro,@submit_date)"
Dim cmd As New OleDbCommand(strSql, conn)
'下面给参数赋值
cmd.Parameters.Add(New OleDbParameter("@name",OleDbType.VarWChar,5))
cmd.Parameters("@name").Value=txtName.Text
cmd.Parameters.Add(New OleDbParameter("@tel",OleDbType.VarWChar,13))
cmd.Parameters("@tel").Value=txtTel.Text
'下面给@email赋值,如果客户没有输入,则赋NULL值
cmd.Parameters.Add(New OleDbParameter("@email",OleDbType.VarWChar,50))
If txtEmail.Text<>"" Then
cmd.Parameters("@email").Value=txtEmail.Text
Else
cmd.Parameters("@email").Value=DBNull.Value '赋值NULL
End If
'下面给@intro赋值,如果客户没有输入,则赋NULL值
cmd.Parameters.Add(New OleDbParameter("@intro",OleDbType.VarWChar))
If txtIntro.Text<>"" Then
cmd.Parameters("@intro").Value=txtIntro.Text
Else
cmd.Parameters("@intro").Value=DBNull.Value '赋值NULL
End If
cmd.Parameters.Add(New OleDbParameter("@submit_date",OleDbType.Date))
cmd.Parameters("@submit_date").Value=Now()
conn.open() '打开数据库
cmd.ExecuteNonQuery()
conn.close() '关闭数据库
Response.Redirect("index.aspx") '返回首页
End Sub
</script>
<html>
<body>
<h3 align="center">添加新记录</h3>
<center>
<form runat="server">
<table border="1" align="center" cellpadding="2" bgcolor="#FFFFFF" cellspacing="0" style="border-collapse: collapse" bordercolor="#D4D4D4" width="80%" >
<tr>
<td>姓名:</td>
<td>
<asp:Textbox id="txtName" runat="server"/>
<asp:RequiredFieldValidator id="require1" ControlToValidate="txtName" ErrorMessage="必须输入用户名" runat="server"/>
<asp:RegularExpressionValidator id="Regular1" ControlToValidate="txtName" ValidationExpression="^[/u4e00-/u9fa5]{2,5}$" ErrorMessage="必须输入2位到5位汉字" runat="server" />
</td>
</tr>
<tr>
<td>电话:</td>
<td>
<asp:Textbox id="txtTel" runat="server"/>(格式:010-12345678或手机号码)
<asp:RequiredFieldValidator id="require2" ControlToValidate="txtTel" ErrorMessage="必须输入电话" runat="server"/>
<asp:RegularExpressionValidator id="Regular2" ControlToValidate="txtTel" ValidationExpression="^([0-9]{3,4}-[0-9]{7,8})|([0-9]{11})$" ErrorMessage="电话格式不正确" runat="server" />
</td>
</tr>
<tr>
<td>E-mail:</td>
<td>
<asp:Textbox id="txtEmail" columns="40" runat="server"/>
<asp:RegularExpressionValidator id="Regular3" ControlToValidate="txtEmail" ValidationExpression="^.{1,}@.{1,}/.[a-zA-Z]{2,3}$" ErrorMessage="E-mail格式不正确" runat="server" />
</td>
</tr>
<tr>
<td>简介:</td>
<td><asp:Textbox id="txtIntro" Textmode="multiline" columns="40" rows="4" runat="server"/></td>
</tr>
<tr>
<td></td>
<td><asp:button id="Enter" Text=" 提 交 " onClick="Enter_Click" runat="server"/></td>
</tr>
</table>
</form>
</center>
</body>
</html>
======================================================
particular.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<HTML>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("address.mdb")) '建立Connection对象
'建立SQL语句,请注意要获取传递过来的id的值
Dim strSql As String
strSql="Select * From users Where id=" & Request.QueryString("id")
Dim cmd As New OleDbCommand(strSql, conn) '建立Command对象
conn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader() '建立DataReader对象
'下面开始显示数据
dr.Read() 'dr.Read()就会读取当前记录
message.Text = "姓名:" & dr.Item("name")
message.Text &= "<br>电话:" & dr.Item("tel")
message.Text &= "<br>email:<a href='mailto:" & dr.Item("email") & "' target='_blank'>" & dr.Item("email") & "</a>"
message.Text &= "<br>简介:" & dr.Item("intro")
message.Text &= "<br>提交时间:" & dr.Item("submit_date")
conn.Close()
End Sub
</script>
<body bgcolor="#efefef">
<h3 align="center">详细信息</h3>
<asp:Label id="message" runat="server" />
<center><a href="JavaScript:close();">关闭窗口</a></center>
</body>
</HTML>