ok here is my first DOTnet page is there a better way to do this?
Code:
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<html>
<head>
</head>
<body>
<script runat="server">
Function GetDataForYear(ByVal year As String)
Dim MyDataReader As System.Data.SQLClient.SqlDataReader
Dim MyDataReaderTest As System.Data.SQLClient.SqlDataReader 'work around till I can test it without killing the first entry
Dim MyCommand As System.Data.SQLClient.SqlCommand
Dim MyConnection As System.Data.SQLClient.SqlConnection
MyConnection = New System.Data.SQLClient.SqlConnection("server='ip'; trusted_connection=true; database='newspub'")
MyConnection.Open()
MyCommand = New System.Data.SQLClient.SqlCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "select year from onlinearticles WHERE year='" & year &"'"
MyDataReader = MyCommand.ExecuteReader()
if MyDataReader.Read 'read 1 to test for results
Response.Write("<h2 class=subtitle>News Articles for "& year &"</h2>")
END IF
MyDataReader = Nothing
MyCommand = Nothing
MyConnection.Close()
MyConnection = Nothing
End Function
Function GetData(ByVal month As String, ByVal year As String)
Dim MyDataReader As System.Data.SQLClient.SqlDataReader
Dim MyDataReaderTest As System.Data.SQLClient.SqlDataReader 'work around till I can test it without killing the first entry
Dim MyCommand As System.Data.SQLClient.SqlCommand
Dim MyConnection As System.Data.SQLClient.SqlConnection
MyConnection = New System.Data.SQLClient.SqlConnection("server='ip'; trusted_connection=true; database='newspub'")
MyConnection.Open()
MyCommand = New System.Data.SQLClient.SqlCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "select sort_date,title,link from onlinearticles WHERE month = '" & month & "'AND year='" & year &"'"
MyDataReader = MyCommand.ExecuteReader()
if MyDataReader.Read 'read 1 to test for results
Response.Write("<strong>"& month &"</strong>")
Response.Write("<dl>")
Response.Write("<dd>" & MyDataReader.Item("sort_date") &" ")
Response.Write("<a class=external href=" & MyDataReader.Item("link") & ">")
Response.Write(MyDataReader.Item("title") & "</a></dd>")
While MyDataReader.Read
Response.Write("<dd>" & MyDataReader.Item("sort_date") &" ")
Response.Write("<a class=external href=" & MyDataReader.Item("link") & ">")
Response.Write(MyDataReader.Item("title") & "</a></dd>")
End While
Response.Write("</dl>")
END IF
MyDataReader = Nothing
MyCommand = Nothing
MyConnection.Close()
MyConnection = Nothing
End Function
Sub Page_load
Dim currentMonth as Integer
Dim currentYear as Integer
currentYear = DateTime.Now.Year
currentMonth = 1
While currentYear >= 1999
GetDataForYear(currentYear)
While currentMonth <= 12
Dim monthNum as String
Select Case currentMonth ' Must be a primitive data type
Case 1
monthNum = "January"
Case 2
monthNum = "Febuary"
Case 3
monthNum = "March"
Case 4
monthNum = "April"
Case 5
monthNum = "May"
Case 6
monthNum = "June"
Case 7
monthNum = "July"
Case 8
monthNum = "August"
Case 9
monthNum = "September"
Case 10
monthNum = "October"
Case 11
monthNum = "November"
Case 12
monthNum = "December"
Case Else
End Select
GetData(monthNum,currentYear)
currentMonth += 1
End While
currentMonth = 1
currentYear -= 1
End While
End Sub
</script>
</body>
</html>