locked
ASP.NET beginner's question - populating list boxes and displaying results RRS feed

  • Question

  • User1622225508 posted

    Hello,

    I have a MS Access Database called "site.mdb".  It has a Site_info table that lists a number of sites across the US, and has different fields containing information about each site.  I would like to create a webpage that pulls all of the information from the different fields for a specific site and displays them dynamically.  Maybe an SQL statement like this:

    SELECT *
    FROM Site_Info
    WHERE Site_Name = ??????

    Of course the ?????? means I do not know how to allow the user to select the site (an initial page with all site names would be great, and a hyperlink for each.  I'm thinking the each hyperlink can attach it's SITE_ID to the link's URL with the "&" syntax.  Then, the SITE_ID can be used on the results page in this SQL statement?).

     Any help is greatly appreciated!!!  I got it to work perfectly with CFML, but i'm trying to do it with ASP.NET!!!  Thanks!

    Thursday, April 10, 2008 5:45 PM

Answers

  • User-821857111 posted

    The SQL you have there is almost spot on, except that you only need one question mark.  That acts as a parameter marker.  More about that here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26.

    To start, you might like to just drag a Repeater onto a page and set its Data Source as an SqlDataSource control. Configure the DataSource to get the site names and add a HyperLink control to the Repeater and set that up.  Here' s a sample that does the same thing with the Categories table from Northwind:

     

    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
      <ItemTemplate>
         <asp:HyperLink 
              ID="Hyperlink1" 
              runat="server" 
              Text='<%# Eval("CategoryName") %>' 
              NavigateUrl='<%# Eval("CategoryID", "NextPage.aspx?ID={0}") %>' />
                    <br />
      </ItemTemplate>
    </asp:Repeater>
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
         ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>" 
         SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
    </asp:SqlDataSource>

    On the second page, you would use the SQL that you have posted to retrieve the details for the specific site.  Add a SqlDataSource control to that page, then configure it to accept a QueryString parameter.  Then bind it to a DetailsView.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:00 AM
  • User-821857111 posted

    what does the {0} mean?  Does this automatically pull the record's (site's) ID to carry over as a variable to "NextPage.aspx"?
     

    It's a place holder used in string formatting.  The TemplateControl.Eval() method takes optionally two arguments: a string representing the field to display (mandatory), and a format string. So the value of the field "Site_ID" is the first, and the second string is the format that it is to be displayed in, with {0} used as an indication of the position for the field's value within the format string.

    Your code looks fine, except for the connection string.  When you drag a SqlDataSource control onto the page, a tag opens inviting you to configure it. Follow the steps in the wizard to do so, and choose to store the connection string in the web.config file when you are asked if you want to.  Then have a look at the markup in the code view of the aspx file.

    More stuff that will help you get the fundamentals is available at the Learn link at the top of the page.  Loads of free tutorials, and videos.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:28 PM

All replies

  • User-821857111 posted

    The SQL you have there is almost spot on, except that you only need one question mark.  That acts as a parameter marker.  More about that here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26.

    To start, you might like to just drag a Repeater onto a page and set its Data Source as an SqlDataSource control. Configure the DataSource to get the site names and add a HyperLink control to the Repeater and set that up.  Here' s a sample that does the same thing with the Categories table from Northwind:

     

    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
      <ItemTemplate>
         <asp:HyperLink 
              ID="Hyperlink1" 
              runat="server" 
              Text='<%# Eval("CategoryName") %>' 
              NavigateUrl='<%# Eval("CategoryID", "NextPage.aspx?ID={0}") %>' />
                    <br />
      </ItemTemplate>
    </asp:Repeater>
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
         ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>" 
         SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
    </asp:SqlDataSource>

    On the second page, you would use the SQL that you have posted to retrieve the details for the specific site.  Add a SqlDataSource control to that page, then configure it to accept a QueryString parameter.  Then bind it to a DetailsView.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:00 AM
  • User1622225508 posted

    Thanks a bunch for the post!!!  I only know CFML programming now (and not the best), so I apologize if I have stupid questions!

    So, the code you posted would be for the start page, correct?  It would list all site names from the database, and each name would have a hyperlink to "NextPage.aspx".  I noticed the following:

    NavigateUrl='<%# Eval("CategoryID", "NextPage.aspx?ID={0}")

    what does the {0} mean?  Does this automatically pull the record's (site's) ID to carry over as a variable to "NextPage.aspx"?

    So, let's say this is my database structure:

    DB name: site.mdb

    Table name: Site_Info

    Fields in Site_Info: Site_ID, Site_Name 

    Then, I would have this for the start page, let's call it "Start.aspx":

     <asp:Repeater ID="Repeater1" runat="server" DataSourceID="site">
      <
    ItemTemplate>
         <
    asp:HyperLink
              ID="Hyperlink1"
              runat="server"
              Text='<%# Eval("SiteName") %>'
              NavigateUrl='<%# Eval("Site_ID", "NextPage.aspx?ID={0}") %>' />
                    <
    br />
      </
    ItemTemplate>
    </
    asp:Repeater>

    <
    asp:SqlDataSource ID="site" runat="server"
         ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
         ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>"
         SelectCommand="SELECT [Site_ID], [Site_Name] FROM [Site_Info]">
    </
    asp:SqlDataSource
    >

    Am I correct?  What is the "NorthwindConnectionString"?  Does this need to be changed at all for my purposes?  This would create a list of the sites with hyperlinks attached to each that takes you to NextPage.aspx (and doesn't do anything yet when taken to it).

    Friday, April 11, 2008 11:48 AM
  • User-821857111 posted

    what does the {0} mean?  Does this automatically pull the record's (site's) ID to carry over as a variable to "NextPage.aspx"?
     

    It's a place holder used in string formatting.  The TemplateControl.Eval() method takes optionally two arguments: a string representing the field to display (mandatory), and a format string. So the value of the field "Site_ID" is the first, and the second string is the format that it is to be displayed in, with {0} used as an indication of the position for the field's value within the format string.

    Your code looks fine, except for the connection string.  When you drag a SqlDataSource control onto the page, a tag opens inviting you to configure it. Follow the steps in the wizard to do so, and choose to store the connection string in the web.config file when you are asked if you want to.  Then have a look at the markup in the code view of the aspx file.

    More stuff that will help you get the fundamentals is available at the Learn link at the top of the page.  Loads of free tutorials, and videos.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 11, 2008 3:28 PM