1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ASP.NET newbie - programming simple SQL queiries

Discussion in 'C#' started by lespaul00, Apr 10, 2008.

  1. #1
    Hello,

    I created a simple dynamic website with Coldfusion 8 code. I use a MS Access database. It has a very simple structure. A main page with many different site names, and a filter to filter it by state/region.

    Then, I have a page that displays site information based on the selection. Now, I need to code it in ASP.NET, but I don't know much about it. I assume the SQL will stay the same, but as far as initiating queries, i'm not sure how ASP.NET works. Here is my code for the two pages:

    <!----INITIALIZE VARIABLES------>
    
    <cfparam name="form.regions" default="">
    <cfparam name="form.regionID" default="">
    <cfparam name="form.states" default="">
    <cfparam name="form.plant_group" default="">
    <cfparam name="form.searchfield" default="">
    
    
    
    <form method="post" action="page.cfm">
    
    
    
    <!----QUERIES TO POPULATE DROP DOWN LISTS------>
    
    <!----REGION LIST INFO QUERY------>
    <cfquery name="regions" datasource="site">
    SELECT RegionID, RegionName
    FROM tblRegion
    ORDER BY RegionName
    </cfquery>
    <!----STATE LIST INFO QUERY------>
    <cfquery name="states" datasource="site">
    SELECT Abbreviation, STATE_ID
    FROM StateNames
    ORDER BY Abbreviation
    </cfquery>
    
    <!----PLANT GROUP LIST INFO QUERY------>
    <cfquery name="plant_group" datasource="site">
    SELECT Plant_Group_ID, Plant_Group_Name
    FROM Plant_Groups
    ORDER BY Plant_Group_Name
    </cfquery>
    
    <!----QUERY FOR RESULTS------>
    
    <cfquery name="siteresults" datasource="site">
    SELECT i.Site_Name, i.RegionID, i.STATE_ID, p.Plant_Group_ID, p.Plant_Group_Name, r.RegionName, s.Abbreviation
    FROM   ((Site_Info AS i INNER JOIN tblRegion r
    	ON i.regionID = r.RegionID)
    INNER JOIN Plant_groups AS p
       ON i.Plant_Group_ID = p.Plant_Group_ID)
    INNER JOIN StateNames AS s
       ON i.STATE_ID = s.STATE_ID
    	WHERE  1 = 1
    	<cfif val(form.RegionID) GT 0>
    		AND	 i.RegionID = <cfqueryparam value="#form.RegionID#" cfsqltype="cf_sql_integer">
    	</cfif>
    	<cfif val(form.states) GT 0>
    		AND	i.STATE_ID = <cfqueryparam value="#form.states#" cfsqltype="cf_sql_integer">
    	</cfif>
    	<cfif val(form.plant_group) GT 0>
    		AND	i.Plant_Group_ID = <cfqueryparam value="#form.plant_group#" cfsqltype="cf_sql_integer">
    	</cfif>
    	ORDER BY i.RegionID ASC, i.SITE_NAME ASC
    </cfquery>
    
    
    <!----FILTERS FORM------>
    
     <select name="RegionID">
       <option value="" <cfif trim(form.RegionID) eq "">selected
    				</cfif>> All </option>
    							
       <cfoutput query="regions">
    	 <option value="#RegionID#" 
              <cfif form.RegionID eq regions.RegionID>selected</cfif>>#RegionName#</option>
       </cfoutput>
          </select>
    
    
    
     <select name="states">
       <option value="" <cfif trim(form.states) eq "">selected
    				</cfif>> All </option>
    							
       <cfoutput query="states">
    	 <option value="#STATE_ID#" 
              <cfif form.states eq states.STATE_ID>selected</cfif>>#Abbreviation#</option>
       </cfoutput>
          </select>
    
     <select name="plant_group">
       <option value="" <cfif trim(form.plant_group) eq "">selected
    				</cfif>> All </option>
    							
       <cfoutput query="plant_group">
    	 <option value="#Plant_Group_ID#" 
              <cfif form.plant_group eq plant_group.Plant_Group_ID>selected</cfif>>#Plant_Group_Name#</option>
       </cfoutput>
          </select>
    
       <input name="submit" type="submit" value="Filter" />
    
    
    </form>
    
    
    <!----DISPLAY RESULTS------>
    
    
    <table width="100%" border="0" cellspacing="0" cellpadding="0">
    
    <cfoutput query="siteresults" group="RegionID">
       <tr>
       	 <th colspan="3" align="left">#RegionName#
       	   </th>
       </tr>
       <tr>
       <cfset typeRow = 1>
       <cfoutput>
          	  <td width="33%"><a href="fd_site_info.cfm?site_name=#SITE_NAME#">#SITE_NAME#</td>
       	  <cfif typeRow mod 3 eq 0>
    	  	</tr><tr>
    	  </cfif>	
    	  <cfset typeRow = typeRow + 1 />
       </cfoutput>
       </tr>
    </cfoutput>
    </table>
    Code (markup):
    Then, here is the code for when a site is selected. It takes you to fd_site_info.cfm now.

    fd_site_info.cfm:

    
    
    <!----- SELECTS SITE THAT USER HAS CLICKED ON MAIN PAGE - PULL DATABASE INFORMATION FOR SPECIFIED SITE---->
    
    <cfset x = #URL.site_name#>
    <cfoutput>
    <cfquery name="siteinfo" datasource="site">
    SELECT *
    FROM
    Site_Info
    WHERE Site_Name = '#x#'
    </cfquery>
    
    <!----- SIMPLY DISPLAY SITE NAME---->
     <cfoutput> <td><h3>#siteinfo.site_name#</h3></td></cfoutput>
    
    Code (markup):
    Any help to get me on the path the change this from CFML to ASP.NET is greatly appreciated. Thanks!

    - Nick
     
    lespaul00, Apr 10, 2008 IP
  2. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,681
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Not a CF person at all but taken a best guess of what is going on to put into .Net.... have to say it can probably all be done with tags rather than code but I am a code type of person

    Code:
    
        Protected Sub FilterBT_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles FilterBT.Click
            Dim Query As String = "SELECT i.Site_Name, i.RegionID, i.STATE_ID, p.Plant_Group_ID, p.Plant_Group_Name, r.RegionName, s.Abbreviation FROM ((Site_Info AS i INNER JOIN tblRegion r ON i.regionID = r.RegionID) INNER JOIN Plant_groups AS p ON i.Plant_Group_ID = p.Plant_Group_ID) INNER JOIN StateNames AS s ON i.STATE_ID = s.STATE_ID WHERE i.RegionID = @RegionID	AND	i.STATE_ID = @StateID AND	i.Plant_Group_ID = @PlantID ORDER BY i.RegionID ASC, i.SITE_NAME ASC"
            Dim SqlConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConfigName"))
            Dim sqlcomm As New SqlCommand(Query, sqlconn)
            
            '' set parameters
            Dim param As New SqlParameter("RegionID", SqlDbType.Int)
            param.Value = RegionDDL.SelectedValue
            sqlcomm.Parameters.Add(param)
    
            param = New SqlParameter("StateID", SqlDbType.Int)
            param.Value = StatesDDL.SelectedValue
            sqlcomm.Parameters.Add(param)
    
            param = New SqlParameter("PlantID", SqlDbType.Int)
            param.Value = PlantGroupDDL.SelectedValue
            sqlcomm.Parameters.Add(param)
    
            Dim DT As New DataTable
            Dim DA As New SqlDataAdapter(sqlcomm)
    
            SqlConn.Open()
            DA.Fill(DT)
            SqlConn.Close()
    
            ResultsRP.DataSource = DT
            ResultsRP.DataBind()
    
        End Sub
    
    Code (.Net):
    Page:
    
        <form id="form1" runat="server">
            <asp:DropDownList ID="RegionDDL" runat="server" AppendDataBoundItems="true" 
                DataSourceID="RegionSDS" DataValueField="RegionID" DataTextField="RegionName">
                <asp:ListItem Selected="True" Value="All" Text="All" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="RegionSDS" runat="server" ConnectionString="<%$ ConnectionStrings:ConfigName %>"
                SelectCommand="SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName" />
            
            <asp:DropDownList ID="StatesDDL" runat="server" DataSourceID="StatesSDS" 
                DataValueField="STATE_ID" DataTextField="Abbreviation" />
            <asp:SqlDataSource ID="StatesSDS" runat="server" ConnectionString="<%$ ConnectionStrings:ConfigName %>"
                SelectCommand="SELECT Abbreviation, STATE_ID FROM StateNames ORDER BY Abbreviation" />
                
            <asp:DropDownList ID="PlantGroupDDL" runat="server" AppendDataBoundItems="true" 
                DataSourceID="PlantGroupSDS" DataValueField="Plant_Group_ID" 
                DataTextField="Plant_Group_Name">
                <asp:ListItem Selected="True" Value="All" Text="All" />
            </asp:DropDownList>
            <asp:SqlDataSource ID="PlantGroupSDS" runat="server" ConnectionString="<%$ ConnectionStrings:ConfigName %>"
                SelectCommand="SELECT Plant_Group_ID, Plant_Group_Name FROM Plant_Groups ORDER BY Plant_Group_Name" />
                
            <asp:Button ID="FilterBT" runat="server" Text="Filter" />
            
            <asp:Repeater ID="ResultsRP" runat="server">
                <HeaderTemplate>
                    <table width="100%" border="0" cellspacing="0" cellpadding="0">
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <td colspan="3">
                            <asp:literal id="RegionLT" runat="server" text='<%# Eval("RegionName") %>' />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:HyperLink ID="SiteHL" runat="server" Text='<%# Eval("Site_Name") %>'
                                 NavigateUrl='<%# Eval("SiteName") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <FooterTemplate>
                    </table>
                </FooterTemplate>
            </asp:Repeater>
        </form>
    
    Code (.Net):
     
    AstarothSolutions, Apr 11, 2008 IP
  3. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks a bunch for the help! I'm going to try this on my local machine when I get home.

    I assume I will just need to install the asp.net software to test it locally? I use Dreamweaver 8.

    Also, is this code compatible with C# and ASP.NET? That is the criteria I have to work with.

    Again, thank you very much!
     
    lespaul00, Apr 11, 2008 IP
  4. georgen2u

    georgen2u Active Member

    Messages:
    40
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    61
    #4
    i want to learn ASP i dont even have the Knowlegde, can any one help me?
     
    georgen2u, Apr 12, 2008 IP
  5. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,681
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #5
    It is in vb.net rather than c# but there are plenty of free online tools to convert one from the other.

    Download a copy of Visual Web Developer Express (which is much better than DW for .Net anyway) but it has a built in test and debugging server for running code
     
    AstarothSolutions, Apr 14, 2008 IP
  6. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thanks for the response.

    Download a copy of Visual Web Developer Express (which is much better than DW for .Net anyway) but it has a built in test and debugging server for running code 
    Code (markup):
    I'm downloading it now. So, does this support C# code?

    So, I tried taking the code you provided and convert it to C# at the following site: http://labs.developerfusion.co.uk/convert/vb-to-csharp.aspx

    Then, I got an instant error:

    Any ideas?
     
    lespaul00, Jun 17, 2008 IP
  7. vodcoder

    vodcoder Peon

    Messages:
    13
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    For those who want to learn quickly ASP .NET I recommend codervods.com because it has good video tutorials for beginners.
     
    vodcoder, Jul 2, 2008 IP