'----------------------------------
'一個打開數據庫並返回一個DataTable
'-------------------------------------
<script Language="VB" runat="server">
Function OpenMdbTable( Db As String, Table As String ) As System.Data.DataTable
Dim Provider, ConnStr, SQL As String
Dim Conn As System.Data.OleDb.OleDbConnection
Dim Adapter As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet
Provider = "Microsoft.Jet.OLEDB.4.0;"
ConnStr = "Provider=" & Provider & _
"Data Source=" & Server.MapPath( Db )
Conn = New System.Data.OleDb.OleDbConnection( ConnStr )
Conn.Open()
Adapter = New System.Data.OleDb.OleDbDataAdapter( "Select * from " & Table, Conn )
Adapter.Fill(ds, Table)
OpenMdbTable = ds.Tables(Table)
Conn.Close()
End Function
</script>
'---------------------------
'逐欄逐列填入資料 DataTable
'----------------------------
<%@ Import Namespace="System.Data" %>
<Html>
<Body BgColor="White">
<H3>DataTable的建構：逐欄逐列填入資料<HR></H3>
<asp:Label runat="server" id="Msg" />
<p>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Dim 姓名() = { "陳桶一", "黃光權", "胡生妙", "王為全", _
"李日正", "劉德菖", "方正一", "劉康寶", _
"謝掬花", "王美蘭", "徐小噹", "葉小毛" }
Dim 國文() = { 90, 58, 41, 95, 59, 28, 98, 0, 95, 41, 91, 0 }
Dim 英文() = { 76, 77, 14, 97, 66, 11, 100, 0, 74, 46, 99, 10 }
Dim 數學() = { 98, 75, 33, 87, 57, 33, 100, 10, 89, 49, 84, 0 }
Sub Page_Load(sender As Object, e As EventArgs)
Dim I As Integer
' Part 1: 宣告並且建立 DataTable 物件
Dim Table As New DataTable
' Part 2: 建立欄位
Table.Columns.Add(New DataColumn("姓名", GetType(String)))
Table.Columns.Add(New DataColumn("國文", GetType(Integer)))
Table.Columns.Add(New DataColumn("英文", GetType(Integer)))
Table.Columns.Add(New DataColumn("數學", GetType(Integer)))
' Part 3: 加入資料列
For I = 0 To UBound(姓名)
Dim Row As DataRow
Row = Table.NewRow()
Row("姓名") = 姓名(I)
Row("國文") = 國文(I)
Row("英文") = 英文(I)
Row("數學") = 數學(I)
Table.Rows.Add(Row)
Next
Msg.Text = "逐欄逐列填入資料 DataTable 完成!"
End Sub
</script>
'----------------------------------
'按下 DataGrid 的抬頭以排序資料
'----------------------------------
<%@ Import Namespace="System.Data" %>
<!-- #include File="Mdb.vb" -->
<Html>
<Body BgColor="White"><Center>
<H3>Sort03.aspx -- 按下 DataGrid 的抬頭以排序資料<HR></H3>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="ChangePage"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="下一頁"
PagerStyle-PrevPageText="上一頁"
AllowSorting="true"
OnSortCommand="DgSortPage"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" /><p>
<asp:Label runat="server" id="SortField" Text="學號" Visible="False" />
<asp:Label runat="server" id="SortType" Text="" Visible="False" />
</Form>
<HR></Center></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Table As DataTable
Table = OpenMdbTable( "Sample.mdb", "成績單" )
Table.DefaultView.Sort = SortField.Text & SortType.Text
MyGrid.DataSource = Table.DefaultView
MyGrid.DataBind()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub DgSortPage(sender As Object, e As DataGridSortCommandEventArgs)
If SortField.Text = e.SortExpression Then
If SortType.Text = "" Then
SortType.Text = " Desc"
Else
SortType.Text = ""
End If
Else
SortField.Text = e.SortExpression
SortType.Text = ""
End If
OpenDataBase_And_BindToDataGrid()
End Sub
Sub ChangePage(sender As Object, e As DataGridPageChangedEventArgs)
MyGrid.CurrentPageIndex = e.NewPageIndex
OpenDataBase_And_BindToDataGrid()
End Sub
</script>
'-------------------------------------------------------------------------
'資料庫存取路徑二: Connection - Command - DataReader
'---------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim TIME1 As Date = DateTime.Now()
' 相關物件的宣告
Dim Conn As OleDbConnection ' 宣告一個 Connection 物件
Dim Cmd As OleDbCommand ' 宣告一個 Command 物件
Dim Rd As OleDbDataReader ' 宣告一個 DataReader 物件
' Connection 物件開啟 Sample.mdb 資料庫
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath("China-IP.mdb")
Conn = New OleDbConnection(Provider & ";" & Database)
Conn.Open()
' Command 物件開啟「成績單」資料表
Dim SQL = "Select top 2000 * From address"
Cmd = New OleDbCommand(SQL, Conn)
' DataReader 物件連結「成績單」資料表
Rd = Cmd.ExecuteReader()
' 利用DataReader物件逐欄逐列讀取資料表，然後填入輸出用的表格
OutputToTable(Rd)
' 關閉 Sample.mdb 資料庫
Conn.Close()
Dim time2 As Date = DateTime.Now()
Dim time3 As Decimal = (time2 - TIME1).Minutes * 60 + (time2 - TIME1).Seconds + (time2 - TIME1).Milliseconds() / 1000
Response.Write("Data Run Times:" & time3 * 1000 & "MS")
End Sub
Sub OutputToTable( Rd As OleDbDataReader )
Dim I As Integer
Dim row As TableRow
Dim cell As TableCell
' 將資料表的「抬頭」填入表格中
row = New TableRow()
row.BackColor = Drawing.Color.Yellow
For I = 0 To Rd.FieldCount - 1
cell = New TableCell()
cell.Text = Rd.GetName(I)
row.Cells.Add( cell )
Next
Table1.Rows.Add( row )
' 逐列讀出資料表，然後填入表格中
While Rd.Read()
row = New TableRow()
For I = 0 To Rd.FieldCount - 1
cell = New TableCell()
cell.Text = Rd.Item(I)
row.Cells.Add( cell )
Next
Table1.Rows.Add( row )
End While
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>資料庫存取路徑二: Connection - Command - DataReader
<HR></H3>
<Center>
<Form runat="server">
<asp:Table runat="server" id="Table1" Border=1 CellPadding=2 CellSpacing=1 />
</Form>
<p><Center>
<HR></Body>
</Html>
'-----------------------------------------------------------------
'讀取資料表每一個欄位的抬頭，並將其顯示在網頁上
'-----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
Msg.Text = "成績單：<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & "</LI>"
Next
Msg.Text &= "</UL>"
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表每一個欄位的抬頭，並將其顯示在網頁上
<HR></H3>
<Form runat="server">
<asp:Label runat="server" id="Msg" />
</Form>
<p>
<HR></Body>
</Html>
'---------------------------------------------------------
'讀取資料表的所有資料列, 使用 Item 屬性
'---------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
While Rd.Read()
Msg.Text &= "<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & " = " & _
Rd.Item(I) & "</LI>"
Next
Msg.Text &= "</UL>"
End While
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表的所有資料列
<HR></H3>
<Form runat="server">
<asp:Label runat="server" id="Msg" />
</Form>
<p>
<HR></Body>
</Html>
'---------------------------------------------------------
'讀取資料表的所有資料列, 使用 GetValues 方法
'-------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
Dim Fields(Rd.FieldCount-1)
While Rd.Read()
Rd.GetValues(Fields)
Msg.Text &= "<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & " = " & _
Fields(I) & "</LI>"
Next
Msg.Text &= "</UL>"
End While
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表的所有資料列
</H3><hr />
<Form runat="server">
<asp:Label runat="server" id="Msg" EnableViewState="False" />
</Form>
<hr /></Body>
</Html>
'---------------------------------------------------------------------------
'使用 Default.RowFilter 篩選資料
'-----------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Filter.aspx -- 使用 Default.RowFilter 篩選資料<HR></H3>
<Form runat="server">
<asp:TextBox runat="Server" id="Filter" Size="40" />
(請輸入篩選條件)<Br>
<asp:Button runat="server" Text="篩選" OnClick="Button_Click" />
<Blockquote>
<asp:DataGrid runat="server" id="MyGrid"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="ChangePage"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="下一頁"
PagerStyle-PrevPageText="上一頁"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0"/><p>
</Form>
</Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
Ds.Tables("成績單").DefaultView.RowFilter = Filter.Text
MyGrid.DataSource = Ds.Tables("成績單").DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub Button_Click(sender As Object, e As EventArgs)
OpenDataBase_And_BindToDataGrid()
End Sub
Sub SortPage(sender As Object, e As EventArgs)
OpenDataBase_And_BindToDataGrid()
End Sub
Sub ChangePage(sender As Object, e As DataGridPageChangedEventArgs)
MyGrid.CurrentPageIndex = e.NewPageIndex
OpenDataBase_And_BindToDataGrid()
End Sub
</script>
'------------------------------------------------------------
'利用 DataTable 物件建立「總成績」欄位
'------------------------------------------------------------
'<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
' 建立「總成績」欄位
Dim I
Dim Table1 As DataTable = Ds.Tables( "成績單" )
Table1.Columns.Add(New DataColumn("總成績", GetType(Integer)))
' 逐列填入資料
For I = 0 To Table1.Rows.Count - 1
Dim 總成績 As Integer
總成績 = Table1.Rows(I).Item("國文") + _
Table1.Rows(I).Item("英文") + _
Table1.Rows(I).Item("數學")
Table1.Rows(I).Item("總成績") = 總成績
Next
MyGrid.DataSource = Table1.DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>NewField.aspx -- 利用 DataTable 物件建立「總成績」欄位
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" />
</Form>
<p><Center>
<HR></Body>
</Html>
'----------------------------------------------------
'設定 HeaderTemplate 及 FooterTemplate 樣版
'----------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Repeat5.aspx -- 設定 HeaderTemplate 及 FooterTemplate 樣版<HR></H3>
<Blockquote>
<Form runat="server">
<asp:Repeater runat="server" id="MyRepeater">
<HeaderTemplate>
<Table Border="0" BgColor="Blue"><Tr><Td>
<Font Color="White" Size="-1">★★★★ 三年甲班第一次期中考 ★★★★</Font>
</Td></Tr></Table><p>
</HeaderTemplate>
<ItemTemplate>
<B><%# Container.DataItem("姓名") %>(<%# Container.DataItem("學號") %>)
</B>
<blockquote>
國文: <%# Container.DataItem("國文") %>,
英文: <%# Container.DataItem("英文") %>,
數學: <%# Container.DataItem("數學") %>
</blockquote>
</ItemTemplate>
<FooterTemplate>
<Table Border="0" BgColor="Blue"><Tr><Td>
<Font Color="White" Size="-1">☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆</Font>
</Td></Tr></Table>
</FooterTemplate>
</asp:Repeater>
</Form>
<p></Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
MyRepeater.DataSource = Ds.Tables( "成績單" ).DefaultView
MyRepeater.DataBind()
Conn.Close()
End Sub
</script>
'---------------------------------------------------------------
'設定 SelectedItemTemplate (被選項樣版)
'----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>DList6.aspx -- 設定 SelectedItemTemplate (被選項樣版)<HR></H3>
<Blockquote>
<Form runat="server">
<asp:DataList id="MyDataList" runat="server"
Border="1" BorderColor="Black"
CellPadding="2" CellSpacing="0"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="lightgray"
SelectedItemStyle-BackColor="yellow"
OnItemCommand="DataList_ItemCommand" >
<HeaderTemplate>
姓名(學號)
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("姓名")%>
(<%# Container.DataItem("學號")%>)
<asp:LinkButton id="Detail" runat="server" Text="成績查詢"/>
</ItemTemplate>
<SelectedItemTemplate>
姓名: <%# Container.DataItem("姓名")%><BR>
學號: <%# Container.DataItem("學號")%><BR>
國文: <%# Container.DataItem("國文")%><BR>
英文: <%# Container.DataItem("英文")%><BR>
數學: <%# Container.DataItem("數學")%><BR>
<asp:LinkButton id="Title" runat="server" Text="關閉查詢"/>
</SelectedItemTemplate>
</asp:DataList>
</Form>
<p></Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDatabase_and_LinkToDataList()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
MyDataList.DataSource = Ds.Tables( "成績單" ).DefaultView
MyDataList.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDatabase_and_LinkToDataList()
End If
End Sub
Sub DataList_ItemCommand(sender As Object, e As DataListCommandEventArgs)
If e.CommandSource.Text = "成績查詢" Then
MyDataList.SelectedIndex = e.Item.ItemIndex
ElseIf e.CommandSource.Text = "關閉查詢" Then
MyDataList.SelectedIndex = -1
End If
OpenDatabase_and_LinkToDataList()
End Sub
</script>
'----------------------------------------------------------------------------
'新增資料到「成績單」資料表, 使用具名的參數
'----------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Insert02.aspx -- 新增資料到「成績單」資料表<HR></H3>
<Form runat="server">
<Blockquote>
學號: <asp:TextBox runat="server" id="學號" /><br>
姓名: <asp:TextBox runat="server" id="姓名" /><br>
國文: <asp:TextBox runat="server" id="國文" /><br>
英文: <asp:TextBox runat="server" id="英文" /><br>
數學: <asp:TextBox runat="server" id="數學" /><p>
<asp:Button runat="server" Text="新增" OnClick="InsertData" />
</Blockquote>
<HR><asp:Label runat="server" id="Msg" ForeColor="Red" />
</Form>
</Body>
</Html>
<script Language="VB" runat="server">
Sub InsertData(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL As String
SQL = "Insert Into 成績單 (學號, 姓名, 國文, 英文, 數學 ) Values( @學號, @姓名, @國文, @英文, @數學)"
Cmd = New OleDbCommand( SQL, Conn )
Cmd.Parameters.Add( New OleDbParameter("@學號", OleDbType.Integer))
Cmd.Parameters.Add( New OleDbParameter("@姓名", OleDbType.Char, 10))
Cmd.Parameters.Add( New OleDbParameter("@國文", OleDbType.SmallInt))
Cmd.Parameters.Add( New OleDbParameter("@英文", OleDbType.SmallInt))
Cmd.Parameters.Add( New OleDbParameter("@數學", OleDbType.SmallInt))
Cmd.Parameters("@學號").Value = Val(學號.Text)
Cmd.Parameters("@姓名").Value = 姓名.Text
Cmd.Parameters("@國文").Value = Val(國文.Text)
Cmd.Parameters("@英文").Value = Val(英文.Text)
Cmd.Parameters("@數學").Value = Val(數學.Text)
Cmd.ExecuteNonQuery()
If Err.Number <> 0 Then
Msg.Text = Err.Description
Else
Msg.Text = "資料新增成功!"
End If
Conn.Close()
End Sub
</script>
'--------------------------------------
'將上網者所修改的資料寫入資料庫中
'-------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 員工資料"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "員工資料")
MyGrid.DataSource = Ds.Tables("員工資料").DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub EditData(sender As Object, e As DataGridCommandEventArgs)
MyGrid.EditItemIndex = e.Item.ItemIndex
OpenDataBase_And_BindToDataGrid()
End Sub
Sub CancelEdit(sender As Object, e As DataGridCommandEventArgs)
MyGrid.EditItemIndex = -1
OpenDataBase_And_BindToDataGrid()
End Sub
Sub UpdateData(sender As Object, e As DataGridCommandEventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim SQL As String
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
SQL = "Update 員工資料 Set 姓名=@姓名, 性別=@性別, 血型=@血型, 已婚=@已婚 Where 員工代號=@Key"
Cmd = New OleDbCommand( SQL, Conn )
Cmd.Parameters.Add( New OleDbParameter("@姓名", OleDbType.Char, 50))
Cmd.Parameters.Add( New OleDbParameter("@性別", OleDbType.Char, 2))
Cmd.Parameters.Add( New OleDbParameter("@血型", OleDbType.Char, 2))
Cmd.Parameters.Add( New OleDbParameter("@已婚", OleDbType.Boolean))
Cmd.Parameters.Add( New OleDbParameter("@Key", OleDbType.Integer))
Cmd.Parameters("@Key").Value = MyGrid.DataKeys(e.Item.ItemIndex)
Dim Text姓名 As TextBox
Text姓名 = e.Item.FindControl("姓名")
Cmd.Parameters("@姓名").Value = Text姓名.Text
Dim List性別 As ListBox
List性別 = e.Item.FindControl("性別")
Cmd.Parameters("@性別").Value = List性別.SelectedItem.Text
Dim List血型 As ListBox
List血型 = e.Item.FindControl("血型")
Cmd.Parameters("@血型").Value = List血型.SelectedItem.Text
Dim Check已婚 As CheckBox
Check已婚 = e.Item.FindControl("已婚")
Cmd.Parameters("@已婚").Value = Check已婚.Checked
Cmd.ExecuteNonQuery()
Conn.Close()
MyGrid.EditItemIndex = -1
OpenDataBase_And_BindToDataGrid()
End Sub
Function 性別編號( 性別 As String ) As Integer
If 性別 = "男" Then Return 0
If 性別 = "女" Then Return 1
End Function
Function 血型編號( 血型 As String ) As Integer
If 血型 = "A" Then Return 0
If 血型 = "B" Then Return 1
If 血型 = "O" Then Return 2
If 血型 = "AB" Then Return 3
End Function
</script>
<Html>
<Body BgColor="White">
<H3>Update04.aspx -- 設定 ListBox 及 CheckBox 欄位
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0"
OnEditCommand="EditData"
OnUpdateCommand="UpdateData"
OnCancelCommand="CancelEdit"
DataKeyField="員工代號"
AutoGenerateColumns="False" >
<Columns>
<asp:EditCommandColumn
HeaderText="修改" ItemStyle-Wrap="False"
EditText="編輯" UpdateText="更新" CancelText="取消" />
<asp:BoundColumn DataField="員工代號" HeaderText="員工代號"
ReadOnly="True" ItemStyle-HorizontalAlign="Right"/>
<asp:TemplateColumn HeaderText="姓名">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("姓名") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" id="姓名" Size=10
Text='<%# Container.DataItem("姓名") %>'/>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="性別">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("性別") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:ListBox runat="server" id="性別" Rows=1
SelectedIndex='<%# 性別編號(Container.DataItem("性別")) %>'>
<asp:ListItem>男</asp:ListItem>
<asp:ListItem>女</asp:ListItem>
</asp:ListBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="血型">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("血型") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:ListBox runat="server" id="血型" Rows=1
SelectedIndex='<%# 血型編號(Container.DataItem("血型")) %>'>
<asp:ListItem>A</asp:ListItem>
<asp:ListItem>B</asp:ListItem>
<asp:ListItem>O</asp:ListItem>
<asp:ListItem>AB</asp:ListItem>
</asp:ListBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="已婚">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("已婚") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox runat="server" id="已婚"
Checked='<%# Container.DataItem("已婚") %>'/>
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</Form>
<p><Center>
<HR></Body>
</Html>
'------------------------------------
'讀取 Score.xml 成為「成績單」資料表
'---------------------------------------
<%@ Import Namespace="System.Data" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Ds As New DataSet
Ds.ReadXml( Server.MapPath("Score.xml") )
MyGrid.DataSource = Ds.Tables( "成績單" ).DefaultView
MyGrid.DataBind()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>XmlRead.aspx -- 讀取 Score.xml 成為「成績單」資料表
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" />
</Form>
<p></Center>
<HR></Body>
</Html>
'-----------------------------------------------------------------------
'請輸入使用者資料 - DataSet物件更新資料庫, 也就是插入一筆記錄
'--------------------------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-2SQL.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 新增一列
objDataRow = objDataSet.Tables("Users").NewRow()
' 輸入欄位內容
objDataRow("userID") = 200
objDataRow("name") = name.Text.ToString()
objDataRow("birthday") = birthday.Text
objDataRow("salary") = salary.Text
objDataRow("email") = email.Text
objDataRow("username") = username.Text
objDataRow("userpassword") = password.Text
' 新增到DataSet
objDataSet.Tables("Users").Rows.Add(objDataRow)
' 更新資料庫, 也就是插入一筆記錄
count = objDataAdapter.Update(objDataSet, "Users")
If count = 1 Then
msg.Text = "插入一筆記錄成功!"
Else
msg.Text = "記錄插入失敗!"
End If
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入使用者資料 - DataSet物件</h3>
<form runat="Server">
姓名: <asp:TextBox id="name" Text="Joe Chen"
Width="100px" runat="Server"/><br>
生日: <asp:TextBox id="birthday" Text="1960/12/31"
Width="200px" runat="Server"/><br>
薪水: <asp:TextBox id="salary" Text="25000"
Width="200px" runat="Server"/><br>
電子郵件: <asp:TextBox id="email" Text="hueyan@ms2.hinet.net"
Width="200px" runat="Server"/><br>
使用者名稱: <asp:TextBox id="username" Text="net3333"
Width="200px" runat="Server"/><br>
使用者密碼: <asp:TextBox id="password" TextMode="Password"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/>
<br>
<asp:Label id="msg" Width="200px" ForeColor="red" runat="Server"/>
<br></form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------------------------------
'請輸入使用者資料 - DataSet物件更新資料庫, 也就是刪除記錄
'--------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-3.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 使用迴路搜尋刪除的記錄
For count = 0 To objDataSet.Tables("Users").Rows.Count - 1
objDataRow = objDataSet.Tables("Users").Rows(Count)
If objDataRow("UserName") = username.Text Then
objDataRow.Delete() ' 刪除此記錄
End If
Next
' 更新資料庫, 也就是刪除記錄
count = objDataAdapter.Update(objDataSet, "Users")
msg.Text = "刪除使用者[" & username.Text & "]的記錄數: " & count
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入刪除的使用者帳號 - DataSet物件</h3>
<form runat="Server">
使用者帳號: <asp:TextBox id="username" Text="net3333"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/><br>
<asp:Label id="msg" ForeColor="red" runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'-----------------------------------------------------------------
'請輸入更新的使用者資料 - DataSet物件更新資料庫, 也就是更新記錄
'-----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-4SQL.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 使用迴路搜尋更新的記錄
For Each objDataRow in objDataSet.Tables("Users").Rows
If objDataRow("UserName") = username.Text Then
' 更新記錄資料
objDataRow("UserPassword") = password.Text
objDataRow("Email") = email.Text
End If
Next
' 更新資料庫, 也就是更新記錄
count = objDataAdapter.Update(objDataSet, "Users")
msg.Text = "更新使用者[" & username.Text & "]的記錄數: " & count
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入更新的使用者資料 - DataSet物件</h3>
<form runat="Server">
使用者帳號: <asp:TextBox id="username" Text="net3333"
Width="100px" runat="Server"/><hr>
電子郵件: <asp:TextBox id="email" Text="hueyan@tpts1.seed.net.tw"
Width="200px" runat="Server"/><br>
使用者密碼: <asp:TextBox id="password" TextMode="Password"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/><br>
<asp:Label id="msg" ForeColor="red" runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'--------------------------------------------
'CreateDatabase.aspx
'--------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>CreateDataBase.aspx</title>
</head>
<body>
<%
Dim objCon As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL, strDbCon As String
' Sql提供者字串
strDbCon = "server=WS1;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
strSQL = "CREATE DATABASE [Users];"
' 建立Command物件的SQL指令
objCmd = New SqlCommand(strSQL, objCon)
' 執行SQL指令
objCmd.ExecuteNonQuery()
Response.Write("已經成功建立資料庫Users<br>")
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'----------------------------------------------
'CreateTable.aspx
'---------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>CreateTable.aspx</title>
</head>
<body>
<%
Dim objCon As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL, strDbCon As String
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立SQL指令
strSQL = "CREATE TABLE [Users] ("
strSQL = strSQL & "[UserID] [int] NOT NULL PRIMARY KEY, "
strSQL = strSQL & "[UserName] [varchar](10) NULL, "
strSQL = strSQL & "[UserPassword] [varchar](10) NULL, "
strSQL = strSQL & "[UserLevel] [int] NULL, "
strSQL = strSQL & "[Name] [varchar](10) NULL, "
strSQL = strSQL & "[Birthday] [datetime] NULL, "
strSQL = strSQL & "[Salary] [decimal] NULL, "
strSQL = strSQL & "[Email] [varchar](25) NULL );"
' 建立Command物件的SQL指令
objCmd = New SqlCommand(strSQL, objCon)
' 執行SQL指令
objCmd.ExecuteNonQuery()
Response.Write("已經成功建立資料表Users")
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'------------------------------------------------
'使用者資料庫的欄位資料
'-------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-2-1.aspx</title>
</head>
<body>
<h3>使用者資料庫的欄位資料</h3><hr>
<table border=1>
<tr>
<td>順序</td><td>名稱</td>
<td>資料型態</td><td>大小</td>
</tr>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim i As Integer
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Users", objCon)
' 取得DataReader物件
objDataReader = objCmd.ExecuteReader()
' 顯示資料表的記錄
Dim schemaTable As DataTable = objDataReader.GetSchemaTable()
Dim dataRow As DataRow
For i = 0 To schemaTable.Rows.Count - 1
Response.Write("<tr>")
dataRow = schemaTable.Rows(i)
Response.Write("<td>" & dataRow("ColumnOrdinal") & "</td>")
Response.Write("<td>" & dataRow("ColumnName") & "</td>")
Response.Write("<td>" & dataRow("DataType").ToString() & "</td>")
Response.Write("<td>" & dataRow("ColumnSize") & "</td>")
Response.Write("<tr>")
Next
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
%>
</table>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'--------------------------------------------------
'DataReader分頁程式
'-------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-2-3.aspx</title>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim intFCount, intI As Integer
Dim pageNo, pageSize As String
Dim intPageNo, intPageSize, intStartRec, intStopRec As Integer
Dim intMaxPageCount, intMaxRec, intCount As Integer
' 取得目前資料表記錄的頁數
pageNo = Request.QueryString("PageNo")
If pageNo = "" Then
intPageNo = 1
Else
intPageNo = Convert.ToInt32(pageNo)
End If
' 取得每一頁顯示的記錄數
pageSize = Request.QueryString("PageSize")
If pageSize = "" Then
intPageSize = 2
Else
intPageSize = Convert.ToInt32(pageSize)
End If
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand()
objCmd.CommandText = "SELECT Count(*) FROM Users"
objCmd.Connection = objCon
' 取得DataReader物件的記錄數
objDataReader = objCmd.ExecuteReader()
objDataReader.Read()
intMaxRec = objDataReader.GetValue(0)
objDataReader.Close() ' 關閉DataReader物件
' 第二次取得記錄內容
objCmd.CommandText = "SELECT * FROM Users"
objDataReader = objCmd.ExecuteReader()
' 取得欄位數目
intFCount = objDataReader.FieldCount - 1
' 是否有查詢到記錄
If intMaxRec > 0 Then
' 計算開始的記錄
intStartRec = intPageSize * (intPageNo - 1) + 1
' 計算結束的記錄
intStopRec = intStartRec + intPageSize - 1
' 計算頁數
intMaxPageCount = intMaxRec \ intPageSize
If (intMaxRec MOD intPageSize) > 0 Then
intMaxPageCount = intMaxPageCount + 1
End If
Response.Write("<table border=1><tr>")
' 顯示資料庫的欄位名稱
For intI = 0 to intFCount
Response.Write("<td><b>" & objDataReader.GetName(intI) & "</b></td>")
Next
Response.Write("</tr>")
intCount = 0
' 顯示資料表的記錄
While objDataReader.Read() AND intCount < intStopRec
intCount = intCount + 1
If intCount >= intStartRec Then
Response.Write("<tr>")
' 顯示每筆記錄的欄位
For intI = 0 to intFCount
If objDataReader.IsDBNull(intI) = False Then
Response.Write("<td valign=""top"">" & objDataReader.Item(intI) & "</td>")
Else
Response.Write("<td valign=""top"">---</td>")
End If
Next
Response.Write("</tr>")
End If
End While
Response.Write("</table>")
objDataReader.Close() ' 關閉DataReader
Response.Write("一共有" & intMaxRec & "筆<br>")
' 目前的頁數
Response.Write("目前為第" & intPageNo & "頁/總共有" & intMaxPageCount & "頁<br>")
' 建立數字的超連結
Dim strURL, intPreviousPageNo, intNextPageNo
For intI = 1 To intMaxPageCount
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intI
strURL = strURL & "&PageSize=" & intPageSize & "'>" & intI & "</a>"
Response.Write(strURL & " ")
If intI mod 10 = 0 Then
Response.Write("<br>")
End If
next
' 上一頁的超連結
intPreviousPageNo = intPageNo - 1
If intPreviousPageNo > 0 Then
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intPreviousPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>上一頁</a>"
Response.Write(strURL & " ")
End If
' 下一頁的超連結
intNextPageNo = intPageNo + 1
If intNextPageNo <= intMaxPageCount Then
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intNextPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>下一頁</a>"
Response.Write(strURL & " ")
End If
End If
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------------------------------------------
'-----------------------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-3.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim intFCount, intI As Integer
Dim strOutput As String = ""
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Products.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand()
objCmd.CommandText = sql.Text
objCmd.Connection = objCon
' 取得DataReader物件的記錄數
objDataReader = objCmd.ExecuteReader()
' 取得欄位數目
intFCount = objDataReader.FieldCount - 1
strOutput = "<table border=1><tr>"
' 顯示資料庫的欄位名稱
For intI = 0 to intFCount
strOutput = strOutput & "<td><b>" & objDataReader.GetName(intI) & "</b></td>"
Next
strOutput = strOutput & "</tr>"
' 顯示資料表的記錄
While objDataReader.Read()
strOutput = strOutput & "<tr>"
' 顯示每筆記錄的欄位
For intI = 0 to intFCount
If objDataReader.IsDBNull(intI) = False Then
strOutput = strOutput & "<td valign=""top"">" & objDataReader.Item(intI) & "</td>"
Else
strOutput = strOutput & "<td valign=""top"">---</td>"
End If
Next
strOutput = strOutput & "</tr>"
End While
strOutput = strOutput & "</table>"
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
msg.Text = strOutput
End If
End Sub
</script>
</head>
<body>
<asp:Label id="msg" runat="Server"/><br>
<h3>請輸入SQL指令</h3>
<form runat="Server">
SQL指令: <asp:TextBox id="sql" Text="SELECT * FROM Products" Width="500px" runat="Server"/><br>
<asp:Button id="Button" Text="查詢" Runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------
'-----DataTable分頁程式
'--------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-4-2.aspx</title>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objDataAdapter As OleDbDataAdapter
Dim strDbCon As String
Dim intFCount, intI, intJ As Integer
Dim pageNo, pageSize As String
Dim intPageNo, intPageSize, intStartRec, intStopRec As Integer
Dim intMaxPageCount, intMaxRec As Integer
' 取得目前資料表記錄的頁數
pageNo = Request.QueryString("PageNo")
If pageNo = "" Then
intPageNo = 1
Else
intPageNo = Convert.ToInt32(pageNo)
End If
' 取得每一頁顯示的記錄數
pageSize = Request.QueryString("PageSize")
If pageSize = "" Then
intPageSize = 2
Else
intPageSize = Convert.ToInt32(pageSize)
End If
' OLEDB提供者字串
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New OleDbDataAdapter("Select * From Users", objCon)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
' 最大的記錄數
intMaxRec = objDataSet.Tables("Users").Rows.Count
' 是否有查詢到記錄
If intMaxRec > 0 Then
' 計算開始的記錄
intStartRec = intPageSize * (intPageNo - 1) + 1
' 計算結束的記錄
intStopRec = intStartRec + intPageSize - 1
If intStopRec >= intMaxRec Then
intStopRec = intMaxRec - 1
End If
' 計算頁數
intMaxPageCount = intMaxRec \ intPageSize
If (intMaxRec MOD intPageSize) > 0 Then
intMaxPageCount = intMaxPageCount + 1
End If
Dim objColumn As DataColumn
Response.Write("<table border=1><tr>")
' 顯示資料庫的欄位名稱
For Each objColumn in objDataSet.Tables("Users").Columns
Response.Write("<td><b>" & objColumn.ColumnName & "</b></td>")
Next
intFCount = objDataSet.Tables("Users").Columns.Count
Response.Write("</tr>")
Dim objRow As DataRow
' 使用迴路顯示記錄
intJ = intStartRec
Do
objRow = objDataSet.Tables("Users").Rows(intJ - 1)
Response.Write("<tr>")
For intI = 0 To intFCount - 1
If objRow.IsNull(intI) = False Then
Response.Write("<td valign=""top"">" & objRow(intI) & "</td>")
Else
Response.Write("<td valign=""top"">---</td>")
End If
Next
Response.Write("</tr>")
intJ = intJ + 1
Loop Until intJ > intStopRec
Response.Write("</table>")
Response.Write("一共有" & intMaxRec & "筆<br>")
' 目前的頁數
Response.Write("目前為第" & intPageNo & "頁/總共有" & intMaxPageCount & "頁<br>")
' 建立數字的超連結
Dim strURL, intPreviousPageNo, intNextPageNo
For intI = 1 To intMaxPageCount
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intI
strURL = strURL & "&PageSize=" & intPageSize & "'>" & intI & "</a>"
Response.Write(strURL & " ")
If intI mod 10 = 0 Then
Response.Write("<br>")
End If
next
' 上一頁的超連結
intPreviousPageNo = intPageNo - 1
If intPreviousPageNo > 0 Then
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intPreviousPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>上一頁</a>"
Response.Write(strURL & " ")
End If
' 下一頁的超連結
intNextPageNo = intPageNo + 1
If intNextPageNo <= intMaxPageCount Then
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intNextPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>下一頁</a>"
Response.Write(strURL & " ")
End If
End If
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------
'顯示關聯的記錄資料
'-------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-5-3.aspx</title>
</head>
<body>
<h3>使用者資料的清單 - DataTable的關聯</h3><hr>
<%
Dim objCon As OleDbConnection
Dim objDataAdapter As OleDbDataAdapter
Dim strDbCon As String
Dim User As DataRow
Dim Level As DataRow
' OLEDB提供者字串
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New OleDbDataAdapter("Select * From Users", objCon)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
' 再取得Levels資料表的記錄
objDataAdapter.SelectCommand = New OleDbCommand("Select * From Levels", objCon)
objDataAdapter.Fill(objDataSet, "Levels")
Dim objRelation As DataRelation
' 建立DataTable的關聯
objRelation = New DataRelation("LevelName", objDataSet.Tables("Levels").Columns("no"), objDataSet.Tables("Users").Columns("userLevel"))
objDataSet.Relations.Add(objRelation)
' 顯示關聯的記錄資料
For Each Level In objDataSet.Tables("Levels").Rows
Response.Write("<b>" & Level("LevelName") & "</b>: <br>" )
For Each User In Level.GetChildRows("LevelName")
Response.Write("+------------ " & User("name") & "<br>")
Next
Next
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'------------------------------------------
隱藏的顯示按鈕
'-------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch11-4-2.aspx</title>
<Script Language="VB" runat="Server">
Sub Page_Load(sender As Object, e As EventArgs)
If Not isPostBack Then
' 第一次載入
BindDataBase()
End If
End Sub
Sub BindDataBase()
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
' 建立Connection物件
objCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Products.mdb"))
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Products", objCon)
' 取得DataReader物件的資料來源
DataGrid1.DataSource = objCmd.ExecuteReader()
DataGrid1.DataBind()
objCon.Close()
End Sub
Sub booksCommand(sender As Object, e As DataGridCommandEventArgs)
Dim counter As Integer
Dim pos As Integer = 4
If e.CommandSource.CommandName = "showBook" Then
For counter = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Columns(counter).Visible = True
Next
End If
If e.CommandSource.CommandName = "hideBook" Then
For counter = pos to DataGrid1.Columns.Count - 1
DataGrid1.Columns(counter).Visible = False
Next
End If
BindDataBase()
End Sub
</script>
</head>
<body>
<form runat="Server">
<asp:DataGrid
id="DataGrid1"
HeaderStyle-BackColor="#FFCC99"
BorderColor="black"
AutoGenerateColumns="False"
OnItemCommand="booksCommand"
runat="Server">
<Columns>
<asp:ButtonColumn HeaderText="功能" ButtonType="PushButton"
Text="顯示" CommandName="showBook"/>
<asp:ButtonColumn HeaderText="功能" ButtonType="LinkButton"
Text="隱藏" CommandName="hideBook"/>
<asp:HyperLinkColumn HeaderText="書號" NavigateUrl="Ch11-4-2.aspx" DataTextField="ProductNo" Target="_new"/>
<asp:BoundColumn HeaderText="書名" DataField="ProductName"/>
<asp:BoundColumn HeaderText="書價" DataField="ProductPrice" DataFormatString="{0:c}"/>
<asp:BoundColumn HeaderText="說明" DataField="ProductNote" Visible="False"/>
</Columns>
</asp:DataGrid>
</form>
<p><a href="../ViewAspx.aspx?File=Ch11/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'一個打開數據庫並返回一個DataTable
'-------------------------------------
<script Language="VB" runat="server">
Function OpenMdbTable( Db As String, Table As String ) As System.Data.DataTable
Dim Provider, ConnStr, SQL As String
Dim Conn As System.Data.OleDb.OleDbConnection
Dim Adapter As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet
Provider = "Microsoft.Jet.OLEDB.4.0;"
ConnStr = "Provider=" & Provider & _
"Data Source=" & Server.MapPath( Db )
Conn = New System.Data.OleDb.OleDbConnection( ConnStr )
Conn.Open()
Adapter = New System.Data.OleDb.OleDbDataAdapter( "Select * from " & Table, Conn )
Adapter.Fill(ds, Table)
OpenMdbTable = ds.Tables(Table)
Conn.Close()
End Function
</script>
'---------------------------
'逐欄逐列填入資料 DataTable
'----------------------------
<%@ Import Namespace="System.Data" %>
<Html>
<Body BgColor="White">
<H3>DataTable的建構：逐欄逐列填入資料<HR></H3>
<asp:Label runat="server" id="Msg" />
<p>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Dim 姓名() = { "陳桶一", "黃光權", "胡生妙", "王為全", _
"李日正", "劉德菖", "方正一", "劉康寶", _
"謝掬花", "王美蘭", "徐小噹", "葉小毛" }
Dim 國文() = { 90, 58, 41, 95, 59, 28, 98, 0, 95, 41, 91, 0 }
Dim 英文() = { 76, 77, 14, 97, 66, 11, 100, 0, 74, 46, 99, 10 }
Dim 數學() = { 98, 75, 33, 87, 57, 33, 100, 10, 89, 49, 84, 0 }
Sub Page_Load(sender As Object, e As EventArgs)
Dim I As Integer
' Part 1: 宣告並且建立 DataTable 物件
Dim Table As New DataTable
' Part 2: 建立欄位
Table.Columns.Add(New DataColumn("姓名", GetType(String)))
Table.Columns.Add(New DataColumn("國文", GetType(Integer)))
Table.Columns.Add(New DataColumn("英文", GetType(Integer)))
Table.Columns.Add(New DataColumn("數學", GetType(Integer)))
' Part 3: 加入資料列
For I = 0 To UBound(姓名)
Dim Row As DataRow
Row = Table.NewRow()
Row("姓名") = 姓名(I)
Row("國文") = 國文(I)
Row("英文") = 英文(I)
Row("數學") = 數學(I)
Table.Rows.Add(Row)
Next
Msg.Text = "逐欄逐列填入資料 DataTable 完成!"
End Sub
</script>
'----------------------------------
'按下 DataGrid 的抬頭以排序資料
'----------------------------------
<%@ Import Namespace="System.Data" %>
<!-- #include File="Mdb.vb" -->
<Html>
<Body BgColor="White"><Center>
<H3>Sort03.aspx -- 按下 DataGrid 的抬頭以排序資料<HR></H3>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="ChangePage"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="下一頁"
PagerStyle-PrevPageText="上一頁"
AllowSorting="true"
OnSortCommand="DgSortPage"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" /><p>
<asp:Label runat="server" id="SortField" Text="學號" Visible="False" />
<asp:Label runat="server" id="SortType" Text="" Visible="False" />
</Form>
<HR></Center></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Table As DataTable
Table = OpenMdbTable( "Sample.mdb", "成績單" )
Table.DefaultView.Sort = SortField.Text & SortType.Text
MyGrid.DataSource = Table.DefaultView
MyGrid.DataBind()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub DgSortPage(sender As Object, e As DataGridSortCommandEventArgs)
If SortField.Text = e.SortExpression Then
If SortType.Text = "" Then
SortType.Text = " Desc"
Else
SortType.Text = ""
End If
Else
SortField.Text = e.SortExpression
SortType.Text = ""
End If
OpenDataBase_And_BindToDataGrid()
End Sub
Sub ChangePage(sender As Object, e As DataGridPageChangedEventArgs)
MyGrid.CurrentPageIndex = e.NewPageIndex
OpenDataBase_And_BindToDataGrid()
End Sub
</script>
'-------------------------------------------------------------------------
'資料庫存取路徑二: Connection - Command - DataReader
'---------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim TIME1 As Date = DateTime.Now()
' 相關物件的宣告
Dim Conn As OleDbConnection ' 宣告一個 Connection 物件
Dim Cmd As OleDbCommand ' 宣告一個 Command 物件
Dim Rd As OleDbDataReader ' 宣告一個 DataReader 物件
' Connection 物件開啟 Sample.mdb 資料庫
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath("China-IP.mdb")
Conn = New OleDbConnection(Provider & ";" & Database)
Conn.Open()
' Command 物件開啟「成績單」資料表
Dim SQL = "Select top 2000 * From address"
Cmd = New OleDbCommand(SQL, Conn)
' DataReader 物件連結「成績單」資料表
Rd = Cmd.ExecuteReader()
' 利用DataReader物件逐欄逐列讀取資料表，然後填入輸出用的表格
OutputToTable(Rd)
' 關閉 Sample.mdb 資料庫
Conn.Close()
Dim time2 As Date = DateTime.Now()
Dim time3 As Decimal = (time2 - TIME1).Minutes * 60 + (time2 - TIME1).Seconds + (time2 - TIME1).Milliseconds() / 1000
Response.Write("Data Run Times:" & time3 * 1000 & "MS")
End Sub
Sub OutputToTable( Rd As OleDbDataReader )
Dim I As Integer
Dim row As TableRow
Dim cell As TableCell
' 將資料表的「抬頭」填入表格中
row = New TableRow()
row.BackColor = Drawing.Color.Yellow
For I = 0 To Rd.FieldCount - 1
cell = New TableCell()
cell.Text = Rd.GetName(I)
row.Cells.Add( cell )
Next
Table1.Rows.Add( row )
' 逐列讀出資料表，然後填入表格中
While Rd.Read()
row = New TableRow()
For I = 0 To Rd.FieldCount - 1
cell = New TableCell()
cell.Text = Rd.Item(I)
row.Cells.Add( cell )
Next
Table1.Rows.Add( row )
End While
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>資料庫存取路徑二: Connection - Command - DataReader
<HR></H3>
<Center>
<Form runat="server">
<asp:Table runat="server" id="Table1" Border=1 CellPadding=2 CellSpacing=1 />
</Form>
<p><Center>
<HR></Body>
</Html>
'-----------------------------------------------------------------
'讀取資料表每一個欄位的抬頭，並將其顯示在網頁上
'-----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
Msg.Text = "成績單：<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & "</LI>"
Next
Msg.Text &= "</UL>"
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表每一個欄位的抬頭，並將其顯示在網頁上
<HR></H3>
<Form runat="server">
<asp:Label runat="server" id="Msg" />
</Form>
<p>
<HR></Body>
</Html>
'---------------------------------------------------------
'讀取資料表的所有資料列, 使用 Item 屬性
'---------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
While Rd.Read()
Msg.Text &= "<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & " = " & _
Rd.Item(I) & "</LI>"
Next
Msg.Text &= "</UL>"
End While
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表的所有資料列
<HR></H3>
<Form runat="server">
<asp:Label runat="server" id="Msg" />
</Form>
<p>
<HR></Body>
</Html>
'---------------------------------------------------------
'讀取資料表的所有資料列, 使用 GetValues 方法
'-------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Rd As OleDbDataReader
Dim I As Integer
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Cmd = New OleDbCommand( SQL, Conn )
Rd = Cmd.ExecuteReader()
Dim Fields(Rd.FieldCount-1)
While Rd.Read()
Rd.GetValues(Fields)
Msg.Text &= "<UL>"
For I = 0 To Rd.FieldCount - 1
Msg.Text &= "<LI>" & Rd.GetName(I) & " = " & _
Fields(I) & "</LI>"
Next
Msg.Text &= "</UL>"
End While
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>讀取資料表的所有資料列
</H3><hr />
<Form runat="server">
<asp:Label runat="server" id="Msg" EnableViewState="False" />
</Form>
<hr /></Body>
</Html>
'---------------------------------------------------------------------------
'使用 Default.RowFilter 篩選資料
'-----------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Filter.aspx -- 使用 Default.RowFilter 篩選資料<HR></H3>
<Form runat="server">
<asp:TextBox runat="Server" id="Filter" Size="40" />
(請輸入篩選條件)<Br>
<asp:Button runat="server" Text="篩選" OnClick="Button_Click" />
<Blockquote>
<asp:DataGrid runat="server" id="MyGrid"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="ChangePage"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="下一頁"
PagerStyle-PrevPageText="上一頁"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0"/><p>
</Form>
</Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
Ds.Tables("成績單").DefaultView.RowFilter = Filter.Text
MyGrid.DataSource = Ds.Tables("成績單").DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub Button_Click(sender As Object, e As EventArgs)
OpenDataBase_And_BindToDataGrid()
End Sub
Sub SortPage(sender As Object, e As EventArgs)
OpenDataBase_And_BindToDataGrid()
End Sub
Sub ChangePage(sender As Object, e As DataGridPageChangedEventArgs)
MyGrid.CurrentPageIndex = e.NewPageIndex
OpenDataBase_And_BindToDataGrid()
End Sub
</script>
'------------------------------------------------------------
'利用 DataTable 物件建立「總成績」欄位
'------------------------------------------------------------
'<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
' 建立「總成績」欄位
Dim I
Dim Table1 As DataTable = Ds.Tables( "成績單" )
Table1.Columns.Add(New DataColumn("總成績", GetType(Integer)))
' 逐列填入資料
For I = 0 To Table1.Rows.Count - 1
Dim 總成績 As Integer
總成績 = Table1.Rows(I).Item("國文") + _
Table1.Rows(I).Item("英文") + _
Table1.Rows(I).Item("數學")
Table1.Rows(I).Item("總成績") = 總成績
Next
MyGrid.DataSource = Table1.DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>NewField.aspx -- 利用 DataTable 物件建立「總成績」欄位
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" />
</Form>
<p><Center>
<HR></Body>
</Html>
'----------------------------------------------------
'設定 HeaderTemplate 及 FooterTemplate 樣版
'----------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Repeat5.aspx -- 設定 HeaderTemplate 及 FooterTemplate 樣版<HR></H3>
<Blockquote>
<Form runat="server">
<asp:Repeater runat="server" id="MyRepeater">
<HeaderTemplate>
<Table Border="0" BgColor="Blue"><Tr><Td>
<Font Color="White" Size="-1">★★★★ 三年甲班第一次期中考 ★★★★</Font>
</Td></Tr></Table><p>
</HeaderTemplate>
<ItemTemplate>
<B><%# Container.DataItem("姓名") %>(<%# Container.DataItem("學號") %>)
</B>
<blockquote>
國文: <%# Container.DataItem("國文") %>,
英文: <%# Container.DataItem("英文") %>,
數學: <%# Container.DataItem("數學") %>
</blockquote>
</ItemTemplate>
<FooterTemplate>
<Table Border="0" BgColor="Blue"><Tr><Td>
<Font Color="White" Size="-1">☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆</Font>
</Td></Tr></Table>
</FooterTemplate>
</asp:Repeater>
</Form>
<p></Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
MyRepeater.DataSource = Ds.Tables( "成績單" ).DefaultView
MyRepeater.DataBind()
Conn.Close()
End Sub
</script>
'---------------------------------------------------------------
'設定 SelectedItemTemplate (被選項樣版)
'----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>DList6.aspx -- 設定 SelectedItemTemplate (被選項樣版)<HR></H3>
<Blockquote>
<Form runat="server">
<asp:DataList id="MyDataList" runat="server"
Border="1" BorderColor="Black"
CellPadding="2" CellSpacing="0"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="lightgray"
SelectedItemStyle-BackColor="yellow"
OnItemCommand="DataList_ItemCommand" >
<HeaderTemplate>
姓名(學號)
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("姓名")%>
(<%# Container.DataItem("學號")%>)
<asp:LinkButton id="Detail" runat="server" Text="成績查詢"/>
</ItemTemplate>
<SelectedItemTemplate>
姓名: <%# Container.DataItem("姓名")%><BR>
學號: <%# Container.DataItem("學號")%><BR>
國文: <%# Container.DataItem("國文")%><BR>
英文: <%# Container.DataItem("英文")%><BR>
數學: <%# Container.DataItem("數學")%><BR>
<asp:LinkButton id="Title" runat="server" Text="關閉查詢"/>
</SelectedItemTemplate>
</asp:DataList>
</Form>
<p></Blockquote>
<HR></Body>
</Html>
<script Language="VB" runat="server">
Sub OpenDatabase_and_LinkToDataList()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 成績單"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "成績單")
MyDataList.DataSource = Ds.Tables( "成績單" ).DefaultView
MyDataList.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDatabase_and_LinkToDataList()
End If
End Sub
Sub DataList_ItemCommand(sender As Object, e As DataListCommandEventArgs)
If e.CommandSource.Text = "成績查詢" Then
MyDataList.SelectedIndex = e.Item.ItemIndex
ElseIf e.CommandSource.Text = "關閉查詢" Then
MyDataList.SelectedIndex = -1
End If
OpenDatabase_and_LinkToDataList()
End Sub
</script>
'----------------------------------------------------------------------------
'新增資料到「成績單」資料表, 使用具名的參數
'----------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Html>
<Body BgColor="White">
<H3>Insert02.aspx -- 新增資料到「成績單」資料表<HR></H3>
<Form runat="server">
<Blockquote>
學號: <asp:TextBox runat="server" id="學號" /><br>
姓名: <asp:TextBox runat="server" id="姓名" /><br>
國文: <asp:TextBox runat="server" id="國文" /><br>
英文: <asp:TextBox runat="server" id="英文" /><br>
數學: <asp:TextBox runat="server" id="數學" /><p>
<asp:Button runat="server" Text="新增" OnClick="InsertData" />
</Blockquote>
<HR><asp:Label runat="server" id="Msg" ForeColor="Red" />
</Form>
</Body>
</Html>
<script Language="VB" runat="server">
Sub InsertData(sender As Object, e As EventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL As String
SQL = "Insert Into 成績單 (學號, 姓名, 國文, 英文, 數學 ) Values( @學號, @姓名, @國文, @英文, @數學)"
Cmd = New OleDbCommand( SQL, Conn )
Cmd.Parameters.Add( New OleDbParameter("@學號", OleDbType.Integer))
Cmd.Parameters.Add( New OleDbParameter("@姓名", OleDbType.Char, 10))
Cmd.Parameters.Add( New OleDbParameter("@國文", OleDbType.SmallInt))
Cmd.Parameters.Add( New OleDbParameter("@英文", OleDbType.SmallInt))
Cmd.Parameters.Add( New OleDbParameter("@數學", OleDbType.SmallInt))
Cmd.Parameters("@學號").Value = Val(學號.Text)
Cmd.Parameters("@姓名").Value = 姓名.Text
Cmd.Parameters("@國文").Value = Val(國文.Text)
Cmd.Parameters("@英文").Value = Val(英文.Text)
Cmd.Parameters("@數學").Value = Val(數學.Text)
Cmd.ExecuteNonQuery()
If Err.Number <> 0 Then
Msg.Text = Err.Description
Else
Msg.Text = "資料新增成功!"
End If
Conn.Close()
End Sub
</script>
'--------------------------------------
'將上網者所修改的資料寫入資料庫中
'-------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="VB" runat="server">
Sub OpenDataBase_And_BindToDataGrid()
Dim Conn As OleDbConnection
Dim Adpt As OleDbDataAdapter
Dim Ds As DataSet
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
Dim SQL = "Select * From 員工資料"
Adpt = New OleDbDataAdapter( SQL, Conn )
Ds = New Dataset()
Adpt.Fill(Ds, "員工資料")
MyGrid.DataSource = Ds.Tables("員工資料").DefaultView
MyGrid.DataBind()
Conn.Close()
End Sub
Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
OpenDataBase_And_BindToDataGrid()
End If
End Sub
Sub EditData(sender As Object, e As DataGridCommandEventArgs)
MyGrid.EditItemIndex = e.Item.ItemIndex
OpenDataBase_And_BindToDataGrid()
End Sub
Sub CancelEdit(sender As Object, e As DataGridCommandEventArgs)
MyGrid.EditItemIndex = -1
OpenDataBase_And_BindToDataGrid()
End Sub
Sub UpdateData(sender As Object, e As DataGridCommandEventArgs)
Dim Conn As OleDbConnection
Dim Cmd As OleDbCommand
Dim SQL As String
Dim Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
Dim Database = "Data Source=" & Server.MapPath( "Sample.mdb" )
Conn = New OleDbConnection( Provider & ";" & DataBase )
Conn.Open()
SQL = "Update 員工資料 Set 姓名=@姓名, 性別=@性別, 血型=@血型, 已婚=@已婚 Where 員工代號=@Key"
Cmd = New OleDbCommand( SQL, Conn )
Cmd.Parameters.Add( New OleDbParameter("@姓名", OleDbType.Char, 50))
Cmd.Parameters.Add( New OleDbParameter("@性別", OleDbType.Char, 2))
Cmd.Parameters.Add( New OleDbParameter("@血型", OleDbType.Char, 2))
Cmd.Parameters.Add( New OleDbParameter("@已婚", OleDbType.Boolean))
Cmd.Parameters.Add( New OleDbParameter("@Key", OleDbType.Integer))
Cmd.Parameters("@Key").Value = MyGrid.DataKeys(e.Item.ItemIndex)
Dim Text姓名 As TextBox
Text姓名 = e.Item.FindControl("姓名")
Cmd.Parameters("@姓名").Value = Text姓名.Text
Dim List性別 As ListBox
List性別 = e.Item.FindControl("性別")
Cmd.Parameters("@性別").Value = List性別.SelectedItem.Text
Dim List血型 As ListBox
List血型 = e.Item.FindControl("血型")
Cmd.Parameters("@血型").Value = List血型.SelectedItem.Text
Dim Check已婚 As CheckBox
Check已婚 = e.Item.FindControl("已婚")
Cmd.Parameters("@已婚").Value = Check已婚.Checked
Cmd.ExecuteNonQuery()
Conn.Close()
MyGrid.EditItemIndex = -1
OpenDataBase_And_BindToDataGrid()
End Sub
Function 性別編號( 性別 As String ) As Integer
If 性別 = "男" Then Return 0
If 性別 = "女" Then Return 1
End Function
Function 血型編號( 血型 As String ) As Integer
If 血型 = "A" Then Return 0
If 血型 = "B" Then Return 1
If 血型 = "O" Then Return 2
If 血型 = "AB" Then Return 3
End Function
</script>
<Html>
<Body BgColor="White">
<H3>Update04.aspx -- 設定 ListBox 及 CheckBox 欄位
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0"
OnEditCommand="EditData"
OnUpdateCommand="UpdateData"
OnCancelCommand="CancelEdit"
DataKeyField="員工代號"
AutoGenerateColumns="False" >
<Columns>
<asp:EditCommandColumn
HeaderText="修改" ItemStyle-Wrap="False"
EditText="編輯" UpdateText="更新" CancelText="取消" />
<asp:BoundColumn DataField="員工代號" HeaderText="員工代號"
ReadOnly="True" ItemStyle-HorizontalAlign="Right"/>
<asp:TemplateColumn HeaderText="姓名">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("姓名") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" id="姓名" Size=10
Text='<%# Container.DataItem("姓名") %>'/>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="性別">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("性別") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:ListBox runat="server" id="性別" Rows=1
SelectedIndex='<%# 性別編號(Container.DataItem("性別")) %>'>
<asp:ListItem>男</asp:ListItem>
<asp:ListItem>女</asp:ListItem>
</asp:ListBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="血型">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("血型") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:ListBox runat="server" id="血型" Rows=1
SelectedIndex='<%# 血型編號(Container.DataItem("血型")) %>'>
<asp:ListItem>A</asp:ListItem>
<asp:ListItem>B</asp:ListItem>
<asp:ListItem>O</asp:ListItem>
<asp:ListItem>AB</asp:ListItem>
</asp:ListBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="已婚">
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Container.DataItem("已婚") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox runat="server" id="已婚"
Checked='<%# Container.DataItem("已婚") %>'/>
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</Form>
<p><Center>
<HR></Body>
</Html>
'------------------------------------
'讀取 Score.xml 成為「成績單」資料表
'---------------------------------------
<%@ Import Namespace="System.Data" %>
<script Language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim Ds As New DataSet
Ds.ReadXml( Server.MapPath("Score.xml") )
MyGrid.DataSource = Ds.Tables( "成績單" ).DefaultView
MyGrid.DataBind()
End Sub
</script>
<Html>
<Body BgColor="White">
<H3>XmlRead.aspx -- 讀取 Score.xml 成為「成績單」資料表
<HR></H3>
<Center>
<Form runat="server">
<asp:DataGrid runat="server" id="MyGrid"
HeaderStyle-BackColor="#AAAADD"
AlternatingItemStyle-BackColor="#FFFFC0"
BorderColor="Black"
CellPadding="2"
CellSpacing="0" />
</Form>
<p></Center>
<HR></Body>
</Html>
'-----------------------------------------------------------------------
'請輸入使用者資料 - DataSet物件更新資料庫, 也就是插入一筆記錄
'--------------------------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-2SQL.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 新增一列
objDataRow = objDataSet.Tables("Users").NewRow()
' 輸入欄位內容
objDataRow("userID") = 200
objDataRow("name") = name.Text.ToString()
objDataRow("birthday") = birthday.Text
objDataRow("salary") = salary.Text
objDataRow("email") = email.Text
objDataRow("username") = username.Text
objDataRow("userpassword") = password.Text
' 新增到DataSet
objDataSet.Tables("Users").Rows.Add(objDataRow)
' 更新資料庫, 也就是插入一筆記錄
count = objDataAdapter.Update(objDataSet, "Users")
If count = 1 Then
msg.Text = "插入一筆記錄成功!"
Else
msg.Text = "記錄插入失敗!"
End If
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入使用者資料 - DataSet物件</h3>
<form runat="Server">
姓名: <asp:TextBox id="name" Text="Joe Chen"
Width="100px" runat="Server"/><br>
生日: <asp:TextBox id="birthday" Text="1960/12/31"
Width="200px" runat="Server"/><br>
薪水: <asp:TextBox id="salary" Text="25000"
Width="200px" runat="Server"/><br>
電子郵件: <asp:TextBox id="email" Text="hueyan@ms2.hinet.net"
Width="200px" runat="Server"/><br>
使用者名稱: <asp:TextBox id="username" Text="net3333"
Width="200px" runat="Server"/><br>
使用者密碼: <asp:TextBox id="password" TextMode="Password"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/>
<br>
<asp:Label id="msg" Width="200px" ForeColor="red" runat="Server"/>
<br></form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------------------------------
'請輸入使用者資料 - DataSet物件更新資料庫, 也就是刪除記錄
'--------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-3.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 使用迴路搜尋刪除的記錄
For count = 0 To objDataSet.Tables("Users").Rows.Count - 1
objDataRow = objDataSet.Tables("Users").Rows(Count)
If objDataRow("UserName") = username.Text Then
objDataRow.Delete() ' 刪除此記錄
End If
Next
' 更新資料庫, 也就是刪除記錄
count = objDataAdapter.Update(objDataSet, "Users")
msg.Text = "刪除使用者[" & username.Text & "]的記錄數: " & count
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入刪除的使用者帳號 - DataSet物件</h3>
<form runat="Server">
使用者帳號: <asp:TextBox id="username" Text="net3333"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/><br>
<asp:Label id="msg" ForeColor="red" runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'-----------------------------------------------------------------
'請輸入更新的使用者資料 - DataSet物件更新資料庫, 也就是更新記錄
'-----------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>Ch9-6-4SQL.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As SqlConnection
Dim objDataAdapter As SqlDataAdapter
Dim strDbCon As String
Dim count As Integer
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New SqlDataAdapter("Select * from Users", objCon)
' 建立CommandBuilder物件, 可以產生DataAdapter需要的SQL指令
Dim objCmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
Dim objDataRow As DataRow
' 使用迴路搜尋更新的記錄
For Each objDataRow in objDataSet.Tables("Users").Rows
If objDataRow("UserName") = username.Text Then
' 更新記錄資料
objDataRow("UserPassword") = password.Text
objDataRow("Email") = email.Text
End If
Next
' 更新資料庫, 也就是更新記錄
count = objDataAdapter.Update(objDataSet, "Users")
msg.Text = "更新使用者[" & username.Text & "]的記錄數: " & count
objCon.Close() ' 關閉資料庫連結
End If
End Sub
</script>
</head>
<body>
<h3>請輸入更新的使用者資料 - DataSet物件</h3>
<form runat="Server">
使用者帳號: <asp:TextBox id="username" Text="net3333"
Width="100px" runat="Server"/><hr>
電子郵件: <asp:TextBox id="email" Text="hueyan@tpts1.seed.net.tw"
Width="200px" runat="Server"/><br>
使用者密碼: <asp:TextBox id="password" TextMode="Password"
Width="100px" runat="Server"/><br>
<asp:Button id="Button" Text="送出" Runat="Server"/><br>
<asp:Label id="msg" ForeColor="red" runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'--------------------------------------------
'CreateDatabase.aspx
'--------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>CreateDataBase.aspx</title>
</head>
<body>
<%
Dim objCon As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL, strDbCon As String
' Sql提供者字串
strDbCon = "server=WS1;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
strSQL = "CREATE DATABASE [Users];"
' 建立Command物件的SQL指令
objCmd = New SqlCommand(strSQL, objCon)
' 執行SQL指令
objCmd.ExecuteNonQuery()
Response.Write("已經成功建立資料庫Users<br>")
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'----------------------------------------------
'CreateTable.aspx
'---------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>
<html>
<head runat=server>
<title>CreateTable.aspx</title>
</head>
<body>
<%
Dim objCon As SqlConnection
Dim objCmd As SqlCommand
Dim strSQL, strDbCon As String
' Sql提供者字串
strDbCon = "server=WS1;database=users;uid=sa;pwd=1234"
' 建立Connection物件
objCon = New SqlConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立SQL指令
strSQL = "CREATE TABLE [Users] ("
strSQL = strSQL & "[UserID] [int] NOT NULL PRIMARY KEY, "
strSQL = strSQL & "[UserName] [varchar](10) NULL, "
strSQL = strSQL & "[UserPassword] [varchar](10) NULL, "
strSQL = strSQL & "[UserLevel] [int] NULL, "
strSQL = strSQL & "[Name] [varchar](10) NULL, "
strSQL = strSQL & "[Birthday] [datetime] NULL, "
strSQL = strSQL & "[Salary] [decimal] NULL, "
strSQL = strSQL & "[Email] [varchar](25) NULL );"
' 建立Command物件的SQL指令
objCmd = New SqlCommand(strSQL, objCon)
' 執行SQL指令
objCmd.ExecuteNonQuery()
Response.Write("已經成功建立資料表Users")
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch09/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'------------------------------------------------
'使用者資料庫的欄位資料
'-------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-2-1.aspx</title>
</head>
<body>
<h3>使用者資料庫的欄位資料</h3><hr>
<table border=1>
<tr>
<td>順序</td><td>名稱</td>
<td>資料型態</td><td>大小</td>
</tr>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim i As Integer
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Users", objCon)
' 取得DataReader物件
objDataReader = objCmd.ExecuteReader()
' 顯示資料表的記錄
Dim schemaTable As DataTable = objDataReader.GetSchemaTable()
Dim dataRow As DataRow
For i = 0 To schemaTable.Rows.Count - 1
Response.Write("<tr>")
dataRow = schemaTable.Rows(i)
Response.Write("<td>" & dataRow("ColumnOrdinal") & "</td>")
Response.Write("<td>" & dataRow("ColumnName") & "</td>")
Response.Write("<td>" & dataRow("DataType").ToString() & "</td>")
Response.Write("<td>" & dataRow("ColumnSize") & "</td>")
Response.Write("<tr>")
Next
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
%>
</table>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'--------------------------------------------------
'DataReader分頁程式
'-------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-2-3.aspx</title>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim intFCount, intI As Integer
Dim pageNo, pageSize As String
Dim intPageNo, intPageSize, intStartRec, intStopRec As Integer
Dim intMaxPageCount, intMaxRec, intCount As Integer
' 取得目前資料表記錄的頁數
pageNo = Request.QueryString("PageNo")
If pageNo = "" Then
intPageNo = 1
Else
intPageNo = Convert.ToInt32(pageNo)
End If
' 取得每一頁顯示的記錄數
pageSize = Request.QueryString("PageSize")
If pageSize = "" Then
intPageSize = 2
Else
intPageSize = Convert.ToInt32(pageSize)
End If
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand()
objCmd.CommandText = "SELECT Count(*) FROM Users"
objCmd.Connection = objCon
' 取得DataReader物件的記錄數
objDataReader = objCmd.ExecuteReader()
objDataReader.Read()
intMaxRec = objDataReader.GetValue(0)
objDataReader.Close() ' 關閉DataReader物件
' 第二次取得記錄內容
objCmd.CommandText = "SELECT * FROM Users"
objDataReader = objCmd.ExecuteReader()
' 取得欄位數目
intFCount = objDataReader.FieldCount - 1
' 是否有查詢到記錄
If intMaxRec > 0 Then
' 計算開始的記錄
intStartRec = intPageSize * (intPageNo - 1) + 1
' 計算結束的記錄
intStopRec = intStartRec + intPageSize - 1
' 計算頁數
intMaxPageCount = intMaxRec \ intPageSize
If (intMaxRec MOD intPageSize) > 0 Then
intMaxPageCount = intMaxPageCount + 1
End If
Response.Write("<table border=1><tr>")
' 顯示資料庫的欄位名稱
For intI = 0 to intFCount
Response.Write("<td><b>" & objDataReader.GetName(intI) & "</b></td>")
Next
Response.Write("</tr>")
intCount = 0
' 顯示資料表的記錄
While objDataReader.Read() AND intCount < intStopRec
intCount = intCount + 1
If intCount >= intStartRec Then
Response.Write("<tr>")
' 顯示每筆記錄的欄位
For intI = 0 to intFCount
If objDataReader.IsDBNull(intI) = False Then
Response.Write("<td valign=""top"">" & objDataReader.Item(intI) & "</td>")
Else
Response.Write("<td valign=""top"">---</td>")
End If
Next
Response.Write("</tr>")
End If
End While
Response.Write("</table>")
objDataReader.Close() ' 關閉DataReader
Response.Write("一共有" & intMaxRec & "筆<br>")
' 目前的頁數
Response.Write("目前為第" & intPageNo & "頁/總共有" & intMaxPageCount & "頁<br>")
' 建立數字的超連結
Dim strURL, intPreviousPageNo, intNextPageNo
For intI = 1 To intMaxPageCount
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intI
strURL = strURL & "&PageSize=" & intPageSize & "'>" & intI & "</a>"
Response.Write(strURL & " ")
If intI mod 10 = 0 Then
Response.Write("<br>")
End If
next
' 上一頁的超連結
intPreviousPageNo = intPageNo - 1
If intPreviousPageNo > 0 Then
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intPreviousPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>上一頁</a>"
Response.Write(strURL & " ")
End If
' 下一頁的超連結
intNextPageNo = intPageNo + 1
If intNextPageNo <= intMaxPageCount Then
strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intNextPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>下一頁</a>"
Response.Write(strURL & " ")
End If
End If
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------------------------------------------
'-----------------------------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-3.aspx</title>
<script language="VB" runat="Server">
Sub Page_Load(Sender As Object, e As Eventargs)
If Page.IsPostBack Then
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim intFCount, intI As Integer
Dim strOutput As String = ""
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Products.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand()
objCmd.CommandText = sql.Text
objCmd.Connection = objCon
' 取得DataReader物件的記錄數
objDataReader = objCmd.ExecuteReader()
' 取得欄位數目
intFCount = objDataReader.FieldCount - 1
strOutput = "<table border=1><tr>"
' 顯示資料庫的欄位名稱
For intI = 0 to intFCount
strOutput = strOutput & "<td><b>" & objDataReader.GetName(intI) & "</b></td>"
Next
strOutput = strOutput & "</tr>"
' 顯示資料表的記錄
While objDataReader.Read()
strOutput = strOutput & "<tr>"
' 顯示每筆記錄的欄位
For intI = 0 to intFCount
If objDataReader.IsDBNull(intI) = False Then
strOutput = strOutput & "<td valign=""top"">" & objDataReader.Item(intI) & "</td>"
Else
strOutput = strOutput & "<td valign=""top"">---</td>"
End If
Next
strOutput = strOutput & "</tr>"
End While
strOutput = strOutput & "</table>"
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
msg.Text = strOutput
End If
End Sub
</script>
</head>
<body>
<asp:Label id="msg" runat="Server"/><br>
<h3>請輸入SQL指令</h3>
<form runat="Server">
SQL指令: <asp:TextBox id="sql" Text="SELECT * FROM Products" Width="500px" runat="Server"/><br>
<asp:Button id="Button" Text="查詢" Runat="Server"/><br>
</form>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------
'-----DataTable分頁程式
'--------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-4-2.aspx</title>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objDataAdapter As OleDbDataAdapter
Dim strDbCon As String
Dim intFCount, intI, intJ As Integer
Dim pageNo, pageSize As String
Dim intPageNo, intPageSize, intStartRec, intStopRec As Integer
Dim intMaxPageCount, intMaxRec As Integer
' 取得目前資料表記錄的頁數
pageNo = Request.QueryString("PageNo")
If pageNo = "" Then
intPageNo = 1
Else
intPageNo = Convert.ToInt32(pageNo)
End If
' 取得每一頁顯示的記錄數
pageSize = Request.QueryString("PageSize")
If pageSize = "" Then
intPageSize = 2
Else
intPageSize = Convert.ToInt32(pageSize)
End If
' OLEDB提供者字串
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New OleDbDataAdapter("Select * From Users", objCon)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
' 最大的記錄數
intMaxRec = objDataSet.Tables("Users").Rows.Count
' 是否有查詢到記錄
If intMaxRec > 0 Then
' 計算開始的記錄
intStartRec = intPageSize * (intPageNo - 1) + 1
' 計算結束的記錄
intStopRec = intStartRec + intPageSize - 1
If intStopRec >= intMaxRec Then
intStopRec = intMaxRec - 1
End If
' 計算頁數
intMaxPageCount = intMaxRec \ intPageSize
If (intMaxRec MOD intPageSize) > 0 Then
intMaxPageCount = intMaxPageCount + 1
End If
Dim objColumn As DataColumn
Response.Write("<table border=1><tr>")
' 顯示資料庫的欄位名稱
For Each objColumn in objDataSet.Tables("Users").Columns
Response.Write("<td><b>" & objColumn.ColumnName & "</b></td>")
Next
intFCount = objDataSet.Tables("Users").Columns.Count
Response.Write("</tr>")
Dim objRow As DataRow
' 使用迴路顯示記錄
intJ = intStartRec
Do
objRow = objDataSet.Tables("Users").Rows(intJ - 1)
Response.Write("<tr>")
For intI = 0 To intFCount - 1
If objRow.IsNull(intI) = False Then
Response.Write("<td valign=""top"">" & objRow(intI) & "</td>")
Else
Response.Write("<td valign=""top"">---</td>")
End If
Next
Response.Write("</tr>")
intJ = intJ + 1
Loop Until intJ > intStopRec
Response.Write("</table>")
Response.Write("一共有" & intMaxRec & "筆<br>")
' 目前的頁數
Response.Write("目前為第" & intPageNo & "頁/總共有" & intMaxPageCount & "頁<br>")
' 建立數字的超連結
Dim strURL, intPreviousPageNo, intNextPageNo
For intI = 1 To intMaxPageCount
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intI
strURL = strURL & "&PageSize=" & intPageSize & "'>" & intI & "</a>"
Response.Write(strURL & " ")
If intI mod 10 = 0 Then
Response.Write("<br>")
End If
next
' 上一頁的超連結
intPreviousPageNo = intPageNo - 1
If intPreviousPageNo > 0 Then
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intPreviousPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>上一頁</a>"
Response.Write(strURL & " ")
End If
' 下一頁的超連結
intNextPageNo = intPageNo + 1
If intNextPageNo <= intMaxPageCount Then
strURL = "<a href='Ch10-4-2.aspx?PageNo=" & intNextPageNo
strURL = strURL & "&PageSize=" & intPageSize & "'>下一頁</a>"
Response.Write(strURL & " ")
End If
End If
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'---------------------------
'顯示關聯的記錄資料
'-------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch10-5-3.aspx</title>
</head>
<body>
<h3>使用者資料的清單 - DataTable的關聯</h3><hr>
<%
Dim objCon As OleDbConnection
Dim objDataAdapter As OleDbDataAdapter
Dim strDbCon As String
Dim User As DataRow
Dim Level As DataRow
' OLEDB提供者字串
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
' 建立DataAdapter物件
objDataAdapter = New OleDbDataAdapter("Select * From Users", objCon)
' 建立DataSet物件
Dim objDataSet As DataSet = New DataSet()
' 將取得的記錄資料填入DataSet物件
objDataAdapter.Fill(objDataSet, "Users")
' 再取得Levels資料表的記錄
objDataAdapter.SelectCommand = New OleDbCommand("Select * From Levels", objCon)
objDataAdapter.Fill(objDataSet, "Levels")
Dim objRelation As DataRelation
' 建立DataTable的關聯
objRelation = New DataRelation("LevelName", objDataSet.Tables("Levels").Columns("no"), objDataSet.Tables("Users").Columns("userLevel"))
objDataSet.Relations.Add(objRelation)
' 顯示關聯的記錄資料
For Each Level In objDataSet.Tables("Levels").Rows
Response.Write("<b>" & Level("LevelName") & "</b>: <br>" )
For Each User In Level.GetChildRows("LevelName")
Response.Write("+------------ " & User("name") & "<br>")
Next
Next
objCon.Close() ' 關閉資料庫連結
%>
<p><a href="../ViewAspx.aspx?File=Ch10/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>
'------------------------------------------
隱藏的顯示按鈕
'-------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title>Ch11-4-2.aspx</title>
<Script Language="VB" runat="Server">
Sub Page_Load(sender As Object, e As EventArgs)
If Not isPostBack Then
' 第一次載入
BindDataBase()
End If
End Sub
Sub BindDataBase()
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
' 建立Connection物件
objCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Products.mdb"))
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Products", objCon)
' 取得DataReader物件的資料來源
DataGrid1.DataSource = objCmd.ExecuteReader()
DataGrid1.DataBind()
objCon.Close()
End Sub
Sub booksCommand(sender As Object, e As DataGridCommandEventArgs)
Dim counter As Integer
Dim pos As Integer = 4
If e.CommandSource.CommandName = "showBook" Then
For counter = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Columns(counter).Visible = True
Next
End If
If e.CommandSource.CommandName = "hideBook" Then
For counter = pos to DataGrid1.Columns.Count - 1
DataGrid1.Columns(counter).Visible = False
Next
End If
BindDataBase()
End Sub
</script>
</head>
<body>
<form runat="Server">
<asp:DataGrid
id="DataGrid1"
HeaderStyle-BackColor="#FFCC99"
BorderColor="black"
AutoGenerateColumns="False"
OnItemCommand="booksCommand"
runat="Server">
<Columns>
<asp:ButtonColumn HeaderText="功能" ButtonType="PushButton"
Text="顯示" CommandName="showBook"/>
<asp:ButtonColumn HeaderText="功能" ButtonType="LinkButton"
Text="隱藏" CommandName="hideBook"/>
<asp:HyperLinkColumn HeaderText="書號" NavigateUrl="Ch11-4-2.aspx" DataTextField="ProductNo" Target="_new"/>
<asp:BoundColumn HeaderText="書名" DataField="ProductName"/>
<asp:BoundColumn HeaderText="書價" DataField="ProductPrice" DataFormatString="{0:c}"/>
<asp:BoundColumn HeaderText="說明" DataField="ProductNote" Visible="False"/>
</Columns>
</asp:DataGrid>
</form>
<p><a href="../ViewAspx.aspx?File=Ch11/<%=Page.Title.ToString() %>">查看源碼</a></p>
</body>
</html>