<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="System.Data.Oledb" %>
<Script Language="VB" runat="Server">
    '定義公共連接對象
    Dim conn As OledbConnection   '聲明一個OledbConnection對象
    '定義公共的DataAdapter對象
    Dim Da As oledbdataAdapter
    '定義公共的DataSet
    Dim ds As New DataSet()
    '定義公共的連接字符串
    Dim SQLStr As String
sub retrieve()
       '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    '選出最近的十條記錄，按照Id的降序排列
    SQLstr="Select top 10 * from content"
    SQLstr &=" where parent=0 Order by ID desc"
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"head")
    '記錄當前的最后一個記錄號
       lastno.text=ds.Tables("head").rows.Item(0).Item("ID")
    '記錄當前的第一個記錄號
       dim count as integer
       count=ds.Tables("head").rows.count-1
       priorno.text=ds.Tables("head").rows.Item(count).Item("ID")
    '對于一條記錄生成一顆樹
     Tree("head")
    conn.close()    
    '將查詢表單不可見
     panel1.visible=false
End sub
Sub Page_Load(Sender As Object,e As Eventargs)
  If Not Page.IsPostBack Then
     retrieve()
  End If
End sub
'Tree函數采用遞歸生成樹
Sub tree(lasttab As String)  
    Dim rowid As Integer   '定義記錄錄號
    Dim content,img,aux,item,body As String 'HTML元素的ID
    Dim tr As dataRow   
    Dim tablename As String '定義樹的表名
    Response.Write("<table border=0>")
     '遍歷當前表中的記錄
   
     For Each tr In ds.Tables(lasttab).rows
         rowid=tr.Item("ID")
         '將當前記錄的子節點生成一張表
         tablename="table" & rowid
         SQLstr="Select  * from content"
         SQLstr &=" where Parent=" & rowid
         da=New OleDbDataAdapter(SQLstr,conn)
         da.Fill(ds,tablename)
        '檢測時候存在子節點，如果存在繼續使用遞歸來生成樹，如果不存在，退出遞歸
       If ds.Tables(tablename).rows.count=0 then
          '如果當前節點已經是葉節點，顯示當前節點，退出遞歸
          Response.Write("<tr>")
          Response.Write("<td>")
          img="img" & rowid
          item="Item" & rowid
          Content="content" & rowid
          body="body" & rowid
          Response.Write("<p align='right'><img id=" &Img & " src='minus.gif' WIDTH='9' HEIGHT='9'></p>")
          Response.Write("</td>")
          Response.Write("<td id=" & item & " language='JavaScript' onMouseUp='turnout(" & body & ");' style='CURSOR: hand'><font  face='楷體_GB2312' size='2'><strong>" & tr.Item("subject") & "——" & tr.Item("Sender") & tr.Item("SendDate") & "</strong></font></a></td>")
        Response.Write (" </tr>")
        Response.Write("<tr>")
        response.write("<td colspan=2 id=" & body & " style='DISPLAY: none'>" )
        Response.Write("<table border=0 bgcolor='grey'>")
        Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1>" & tr.Item("body") &"</font></td>")
        Response.Write("</tr>")
 Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1><a href=mailto:" & tr.Item("Email") & ">寄信給作者  </a>")
        Response.Write("<a href=post.aspx?ID=" & Tr.Item("id") & "&subject=" & tr.item("subject") & ">回復此文</a></font></td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Response.Write("</td></tr>")
        '如果不是葉節點，則繼續遞歸顯示
       Else 
         content="content" & rowid
         img="img" & rowid
         aux="Aux" & rowid
         body="body" & rowid
         item="Item" & rowid
         '顯示信息頭     
      Response.Write("<tr>")
      Response.Write("<td language='JavaScript' onMouseUp='turnit("& Content &","& Img &"," & Aux & ");' style='CURSOR: hand'>")
        Response.Write("<p align='right'><img id=" &Img & " src='plus.gif' WIDTH='9' HEIGHT='9'></p>")
        Response.Write("</td>")
        Response.Write("<td id=" & item & " language='JavaScript' onMouseUp='turnout(" & body & ");' style='CURSOR: hand'><font  face='楷體_GB2312' size='2'><strong>" & tr.Item("subject") & "——" & tr.Item("Sender") & tr.Item("SendDate") & "</strong></font></a></td>")
        Response.Write (" </tr>")
        Response.Write("<tr>")
        response.write("<td colspan=2 id=" & body & " style='DISPLAY: none'>")
        Response.Write("<table border=0 bgcolor='grey'>")
        Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1>" & tr.Item("body") &"</font></td>")
        Response.Write("</tr>")
        Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1><a href=mailto:" & tr.Item("Email") & ">寄信給作者  </a>")
        Response.Write("<a href=post.aspx?ID=" & Tr.Item("id") & "&subject=" & tr.item("subject") & ">回復此文</a></font></td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Response.Write("</td></tr>")
        Response.Write("<tr>")
        Response.Write("<td id=" & Aux & "  style='DISPLAY: none'></td>")
        Response.Write ("<td id=" & Content & " style='DISPLAY: none'>")
        '將遞歸深度加深
        tree(tablename)
          Response.Write("</tr>")
    End If
         next
        Response.Write("</Table>")
End Sub
Sub prior_click(Sender As object,e As Eventargs)
    '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    '選出最近的十條記錄，按照Id的降序排列
    SQLstr="Select top 10 * from content"
    SQLstr &=" where parent=0 "
    SQLstr &=" and ID<" & priorno.text
    SQLstr &=" Order by ID desc"
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"head")
    if ds.Tables("head").rows.count=0 then
      Response.Write("已經到了第一頁!")
    Else
    '記錄當前的最后一個記錄號
       lastno.text=ds.Tables("head").rows.Item(0).Item("ID")
    '記錄當前的第一個記錄號
       dim count as integer
       count=ds.Tables("head").rows.count-1
       priorno.text=ds.Tables("head").rows.Item(count).Item("ID")
    '對于一條記錄生成一顆樹
     Tree("head")
    End If
    conn.close()    
End Sub
Sub Next_Click(Sender As object,e As Eventargs)
    '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    '選出最近的十條記錄，按照Id的降序排列
    SQLstr="Select top 10 * from content"
    SQLstr &=" where parent=0 "
    SQLstr &=" and ID>" & lastno.text
    SQLstr &=" Order by ID Asc"
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"head")
    if ds.Tables("head").rows.count=0 then
      Response.Write("已經到了最后一頁!")
    Else
     '記錄當前的最后一個記錄號
         Dim count as integer
         count=ds.Tables("head").rows.count-1
       lastno.text=ds.Tables("head").rows.Item(count).Item("ID")
    '記錄當前的第一個記錄號
       priorno.text=ds.Tables("head").rows.Item(0).Item("ID")
       '對于一條記錄生成一顆樹
    Tree("head")
    End If
    conn.close()    
End Sub

'按時間查詢
Sub tm_Click(Sender As object,e as eventargs)
    '首先檢測是否是有效時間
    If IsDate(tm1.text) and IsDate(tm2.text) Then
    '隱藏查詢表單
    panel1.visible=false
    '建立SQL語句
    Dim SQLstr As String 
    SQLstr="Select * From content"
    SQLstr &=" Where  parent=0  and sendDate>#" & tm1.text & "#"
    SQLstr &=" and SendDate<#" & tm2.text & "#"
   '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"time")
    Tree("Time")
    Conn.Close()
    '將上一頁和下一頁按鈕消除
    prior.visible=false
    nxt.visible=false
    Else 
      Response.Write("請輸入正確的時間格式!如2003/12/5")
    End If
End Sub
'重置查詢表單
Sub Rset_Click(Sender As object,e as eventargs)
    '顯示輸入表單
    panel1.visible=true
    '清空表單
    tm1.text=""
    tm2.text=""
    sub1.text=""
    sd1.text=""
    '顯示上一頁和下一頁按鈕
    prior.visible=false
    nxt.visible=false
End Sub
'按標題查詢
Sub Sub_click(sender as object,e as eventargs)
    '隱藏查詢表單
    panel1.visible=false
    '建立SQL語句
    Dim SQLstr As String 
    SQLstr="Select * From content"
    SQLstr &=" Where  parent=0  and subject like '%" & sub1.text & "%'"
   '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"sub")
    Tree("sub")
    Conn.Close()
    '將上一頁和下一頁按鈕消除
    prior.visible=false
    nxt.visible=false
End Sub
'按作者查詢
Sub sd_Click(Sender as object,e as eventargs)
    '隱藏查詢表單
    panel1.visible=false
    '建立SQL語句
    Dim SQLstr As String 
    SQLstr="Select * From content"
    SQLstr &=" Where  sender='" & sd1.text & "'"
   '首先建立數據連接
    conn=New OledbConnection()    '建立一個oledbConnection對象
    '設定連接字符串ConnectionString
    conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath("Discuss.mdb")
    '打開連接
    conn.Open()
    da=New OleDbDataAdapter(SQLstr,conn)
    da.Fill(ds,"sd")
    '寫出所有該用戶的記錄
    Dim tr As dataRow
    Dim rowid as long
    Dim img,content,body,item as string
    Response.Write("<table>")
    '遍歷所有記錄
    for each tr in ds.Tables("sd").rows
        rowid=tr.Item("ID")
        Response.Write("<tr>")
        Response.Write("<td>")
        img="img" & rowid
          item="Item" & rowid
          Content="content" & rowid
          body="body" & rowid
          '顯示標題欄
          Response.Write("<p align='right'><img id=" &Img & " src='minus.gif' WIDTH='9' HEIGHT='9'></p>")
          Response.Write("</td>")
          Response.Write("<td id=" & item & " language='JavaScript' onMouseUp='turnout(" & body & ");' style='CURSOR: hand'><font  face='楷體_GB2312' size='2'><strong>" & tr.Item("subject") & "——" & tr.Item("Sender") & tr.Item("SendDate") & "</strong></font></a></td>")
        Response.Write (" </tr>")
        '顯示文章內容
        Response.Write("<tr>")
        response.write("<td colspan=2 id=" & body & " style='DISPLAY: none'>" )
        Response.Write("<table border=0 bgcolor='grey'>")
        Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1>" & tr.Item("body") &"</font></td>")
        Response.Write("</tr>")
        '顯示回復和寄信鏈接按鈕
        Response.Write("<tr><td></td>")
        Response.Write("<td><font size=1><a href=mailto:" & tr.Item("Email") & ">寄信給作者  </a>")
        Response.Write("<a href=post.aspx?ID=" & Tr.Item("id") & "&subject=" & tr.item("subject") & ">回復此文</a></font></td>")
       Response.Write("</tr>")
        Response.Write("</table>")
     next
    Response.Write("</table>")
    Conn.Close()
    '將上一頁和下一頁按鈕消除
    prior.visible=false
    nxt.visible=false
End sub
'返回主界面
Sub Rback_click(sender as object,e as eventargs)
   retrieve()
   '顯示上一頁和下一頁按鈕
   prior.visible=true
    nxt.visible=true
End Sub
</Script>
<script lanuage="javascript">
function turnit(ss,ii,aa)
{
 if (ss.style.display=="none") 
  {ss.style.display="";
   aa.style.display="";
   ii.src="minus.gif";
 }
 else
  {ss.style.display="none"; 
   aa.style.display="none";
   ii.src="plus.gif";}
}
function turnout(bb)
{
 if (bb.style.display=="none") 
  {
    bb.style.display="";
   } 
 else
{
  bb.style.display="none";
}
}
</script>
<Html>
<Head>
<title>歡迎進入論壇!</Title>
</Head>
<Body bgcolor="lightgrey">
<Form runat="Server">

<asp:linkbutton id="prior" text="上一頁"
      OnClick="prior_click" runat="Server"/>
<asp:linkbutton id="nxt" text="下一頁" 
      OnClick="next_click" runat="Server"/>
<asp:Textbox id="lastno" visible=false runat="server" />
<asp:textbox id="priorno" visible=false runat="Server" />
<asp:panel id="panel1" runat="server"> 
按時間查詢：
<asp:TextBox id="tm1" Runat="Server"/>
~
<asp:TextBox id="tm2" runat="Server"/>
<asp:button id="tm" text="查詢" 
     OnClick="tm_click" runat="server"/><p>
按主題查詢:
<asp:textBox id="sub1" runat="server"/>
<asp:button id="sub" onclick="sub_click" text="查詢" runat="server"/><p>
按作者查詢：
<asp:textBox id="sd1" runat="server"/>
<asp:button id="sd" OnClick="sd_Click" text="查詢" runat="server"/>
</asp:panel>
<p>   
<asp:button id="rset" text="重新查詢" OnClick="rset_click" runat="server"/> 
<asp:button id="rback" text="返回主界面" OnClick="rback_click" runat="server"/> 
</Form>
</Body>
</Html>