ASP.NET newbie - programming simple SQL queiries

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

 

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):

 

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!

 

i want to learn ASP i dont even have the Knowlegde, can any one help me?

 

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

 

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?

 

For those who want to learn quickly ASP .NET I recommend codervods.com because it has good video tutorials for beginners.