displayquery.aspx

asp做的新闻系统

<%@ 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>