asked on

Is there a better way? Datareader help needed to get this small app running.

Hello,

If someone can provide me with a working solution I would be very pleased. I'm converting a website from classic asp to asp.net. I'm having some issues around looping through the recordset like I used to do with plain ASP. I dont think that binding to a datalist/datagrid will accomplish what I need to do. But then again I am a newbie to .NET. I basicly need to loop through a set of records and build up web links from the data in the recordset. Below you will find basicly what I'm trying to do. There proably are some syntax errors for instance in the <title> I used an old classic asp way of displaying variable data wher maybe I should have used an asp:label? I didnt know if I should have used any of the Page_Load event handlers. Thank you for your time. A perfect answer will get you a decent amount of points for you time.

Regards, Mike

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="vb" runat="server">

      'OnPageLoad
      Sub DisplayJokeLinks()

            'Declare vars
            Dim CurrentPage as integer
            Dim JokeTypeID as integer
            Dim PageTitle as String

            'Grab the TypeID from the URL.
            If Request.QueryString("Type") = "" Then
                  JokeTypeID = 1
            Else
                  JokeTypeID = Request.QueryString("Type")
            End if

            'Set a page title depending on JokeTypeID .
            If JokeTypeID = 1 Then
                  PageTitle = "Page Title 1"
            ElseIf JokeTypeID = 2 Then
                  PageTitle = "Page Title 2"
            ElseIf JokeTypeID = 3 Then
                  PageTitle = "Page Title 3"
            ElseIf JokeTypeID = 4 Then
                  PageTitle = "Page Title 4"
            End If

            'Grab the Page number from the URL for implementing paging later on.
            If Request.QueryString("Page") = "" Then
                  CurrentPage = 1
            Else
                  CurrentPage = Request.QueryString("Page")
            End if

            Dim SQLQuery as String
            Dim Connection As SqlConnection
            Dim mySQLCommand As SqlCommand
            Dim mySQLDataReader As SqlDataReader

            'Stored Procedure - Returns results like...
            'JokeID,JokeTypeID,JokeDescription
            '-----------------------------------------
            '125,1,Why Did the Chicken Cross the Road?
            SQLQuery = "GetJokeList "& JokeTypeID

            'Create new SQL Connection.
            Connection = New SQLConnection("server=local;User id=sa;password=pass;database=Jokes")
      Connection.Open()

      'Create a command object
            mySQLCommand = New SqlCommand(SQLQuery, Connection)

            'Get the recordset
            mySQLDataReader = mySQLCommand.ExecuteReader()

            'Loop through the recordset and create urls.
            While mySQLDataReader.Read()
                  'Write out the record.
                  Response.write("<li><a href='Joke.asp?Type="& mySQLDataReader.Item("JokeTypeID") &"&ID="& mySQLDataReader.Item("JokeID") &"'>"& mySQLDataReader.Item("JokeDescription") &"</a></li>")
            End While

      End sub

</script>

<html>
<head>
<title><%=PageTitle%></title>
</head>
<body>
<table border="0" width="500" cellpadding="0" cellspacing="0">
<tr>
      <td><% DisplayJokeLinks() %></td>
</tr>
</table>
</body>
</html>

Rejojohny

u can do the above using datagrids and templetcolumn or hyperlink column .. have a look here on how it can be done
How to use HyperLink Column of DataGrid?
http://www.c-sharpcorner.com/Code/2003/June/DataGridHyperLinkColumn.asp

Rejojohny

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaddinghyperlinkcolumnstodatagridwebcontrol.asp

b1xml2

try this
=====
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
Sub Page_Load(ByVal sender As Object,ByVal e As EventArgs)
      If Not IsPostBack Then
            TitleLiteral.Text = "Page Title " & CStr(JokeTypeID)
            BindData()
      End If
End Sub

ReadOnly Property JokeTypeID() As Integer
      Get
            Dim value As Integer = 1
            If Not Request.QueryString("Type") Is Nothing AndAlso IsNumeric(Request.QueryString("Type")) Then
                  value = CInt(value)
            End If
            Return value
      End Get
End Property

Sub BindData()
      Dim connection As New SqlConnection(...)
      Dim selectCommand As New SqlCommand("EXEC GetJokeList @Id",connection)
      selectCommand.Parameters.Add("@Id",SqlDbType.Int).Value = Me.JokeTypeID
      connection.Open()
      Dim reader As SqlDataReader = selectCommand.ExecuteReader()
      LinksRepeater.DataSource = reader
      reader.Close()
      connection.Close()
End Sub
</script>
<html>
<head>
<title><asp:Literal Id="TitleLiteral" Runat="server" /></title>
</head>
<body>
<form runat="server">
<asp:Repeater Id="LinksRepeater">
<HeaderTemplate>
<table border="0" width="500" cellpadding="0" cellspacing="0">
</HeaderTemplate>
<ItemTemplate>
<tr>
      <td><a href='Joke.asp?Type=<%# DataBinder.Eval(Container.DataItem,"JokeTypeID") %>&ID=<%# DataBinder.Eval(Container.DataItem,"JokeID") %>'><%# DataBinder.Eval(Container.DataItem,"JokeDescription")%></a></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>

maddhacker24

ASKER

I get the following error: Name 'LinksRepeater' is not declared.

Rejojohny

hmm... now y would u recommend using a itemtemplate with anchor tags when u have hyperlink column or controls available? would u use it if u had to construct a similar page?

maddhacker24

ASKER

Rejojohny,

I took a look at the urls you gave to me for reference and came up with a working version. Is there any way to add style sheets to the links that I'm building up? CssClass appears to not work for me.

I also need this app to do recordset paging. I'm assuming its a few extra lines of code? Any help would be great. THis is what I ended up using.

<form runat="server">
      <asp:datagrid id="Jokes" runat="server" AutoGenerateColumns="False" ShowHeader="False">
            <Columns>
                  <asp:HyperLinkColumn DataNavigateUrlField="JokeID" DataNavigateUrlFormatString="Joke.aspx?ID={0}" DataTextField="JokeDescription"></asp:HyperLinkColumn>
            </Columns>
      </asp:datagrid>
</form>

b1xml2

errata
====
<asp:Repeater Id="LinksRepeater" runat="server">

also, note that the Repeater Server Control is the lightest when compared to the DataGrid and serves the function you require well. DataList is th next one and DataGrid is the heaviest. Now as far as the DataGrid is concerned, the HyperLinkColumn does not cut the ice as TWO, yes, TWO field values, not ONE are required.

simplicity is best.

b1xml2

<a href='Joke.asp?Type=<%# DataBinder.Eval(Container.DataItem,"JokeTypeID") %>&ID=<%# DataBinder.Eval(Container.DataItem,"JokeID") %>'><%# DataBinder.Eval(Container.DataItem,"JokeDescription")%></a>

cannot be answered by a HyperLinkColumn =))

Rejojohny

>>CssClass appears to not work for me.
what is the problem. Remember the datagrid is finally rendered as a normal HTML table .. so u can define class for table tags or do it this way

<style>
.MyGrid
{
      border:0;
      width:100%;
}
.GridHeader TD, .GridFooter TD
{
      padding-right: 2px;
      padding-left: 2px;
      font-weight: bold;
      padding-bottom: 2px;
      color: gray;
      padding-top: 2px;
      background-color: lavender;
      text-align :center;
}

</style>

<asp:datagrid id="Jokes" runat="server" AutoGenerateColumns="False" ShowHeader="False" CssClass = "Grid">
<HeaderStyle CssClass="GridHeader"></HeaderStyle>
<FooterStyle CssClass="GridFooter"></FooterStyle>

maddhacker24

ASKER

a.BlueLink:link{ color: #66CCFF; font-family: Arial; font-size: 12px; font-weight: bold; text-decoration: none; }
a.BlueLink:visited{ color: #66CCFF; font-family: Arial; font-size: 12px; font-weight: bold; text-decoration: none; }
a.BlueLink:hover{ color: #FFFFFF; font-family: Arial; font-size: 12px; font-weight: bold; text-decoration: none; }

These are the styles that I apply to my Links. I need to be able to apply those styles to each link that is written out.

example output would look like: <a href="Joke.aspx?Type=1&ID=10" class="BlueLink">Joke Desc.</a>

Adding the BlueLink style to the datagrid does not work (presumably because it renders as a table tag.)

maddhacker24

ASKER

b1xml2 ,

Your version compiles and runs, but with no records getting rendered.

maddhacker24

ASKER

b1xml2 ,

I like your idea of using a repeater because it would give me more control over the output. Can you show me how you would add paging into your example? I know that the repeater does not have paging unless you use the custom paging class. Any help would be greatly appreciated.

Regards,

ASKER CERTIFIED SOLUTION

b1xml2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rejojohny

U can use create a template column and add a hyperlink control, then u can add the cssclass to the hyperlink control ..

something like this .. note that both ur parameters are added to the querystring

<asp:TemplateColumn HeaderText="Property name">
<HeaderStyle Width="17%"></HeaderStyle>
<TemplateColumn>
<ItemTemplate>
<asp:HyperLink Runat="server" NavigateUrl='<%# _
"Joke.aspx?Type=" & Container.DataItem("JokeTypeId") & _
"&Id=" & Container.DataItem("JokeId") %>' CssClass="Blue"/>
</ItemTemplate>
</TemplateColumn>

</asp:TemplateColumn>

Rejojohny

By the way, if u already have the hyperlinkcolumn in the datagrid, then all u have to do is open it the property builder of the datagrid by right clicking it in the design view and selecting the menu "Property builder"
select columns .. select the existing hyperlinkcolumn .. at the bottom u will see a link "Convert this to into a template column" .. click on this and it will create a templatecolumn with a hyperlink control .. just change the NavigateURL property to add the necessary addtional parameters and then change the CssClass to "Blue"

SOLUTION

Rejojohny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rejojohny

Please do have a look at the comments and the links provided ...