[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-19
20
Medium Priority
?
200 Views
Last Modified: 2010-04-07
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>
0
Comment
Question by:maddhacker24
  • 8
  • 5
  • 4
17 Comments
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13814965
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
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13815763
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>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:maddhacker24
ID: 13815846
I get the following error: Name 'LinksRepeater' is not declared.
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13815884
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?
0
 

Author Comment

by:maddhacker24
ID: 13815938
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>
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13815961
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.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13815982
<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  =))
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13816002
>>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>

0
 

Author Comment

by:maddhacker24
ID: 13816056
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.)
0
 

Author Comment

by:maddhacker24
ID: 13816109
b1xml2 ,

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

Author Comment

by:maddhacker24
ID: 13820331
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,
0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 1000 total points
ID: 13820618
this should do the trick,

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
      'data bind
      LinksRepeater.DataBind()
      reader.Close()
      connection.Close()
End Sub
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13821638
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>
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 13821662
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"
0
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 1000 total points
ID: 13821670
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 14005440
Please do have a look at the comments and the links provided ...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question