I'm trying to display a menu of music categories on a user control that's embedded in a default.aspx page. I am not getting any build errors when I run the default.aspx page but none of the results from my sql query show.

I ran the sql query on its own and it displays correctly so it's something about the way in which the data list is retrieving the data from the .mdf to display it.

Here is the code for my .cs file:

using System;
using System.Web;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Provides methods for Sql Server database queries
/// Requires database connection string to be located in web.config
/// </summary>
public class MusicStoreSample2008

{
//global scope
private SqlConnection myConnection = new SqlConnection();

//constructor
public MusicStoreSample2008(string ConnectingStringNameInWebConfig)
{
    string connectString = WebConfigurationManager.ConnectionStrings[ConnectingStringNameInWebConfig].ConnectionString;
    myConnection.ConnectionString = connectString;
}

/// <summary>
/// SELECT queries: Copy and modify this method for queries that return
/// multiple rows and/or fields of data. 
/// </summary>
/// <returns>DataTable</returns>
public DataTable fillMenu()
{
    string sql = "select s.styleID, s.StyleName from tblStyles s, tblStyleASIN sa where s.StyleID = sa.StyleID group by s.StyleID, s.StyleName order by s.StyleName";
    DataTable dt = new DataTable();
    //using statement destroys object when operation is complete or throws exception.
    using (SqlCommand cmd = new SqlCommand(sql, myConnection))
    {
        //cmd.Parameters.AddWithValue("@StyleName", StyleName);
        myConnection.Open();
        dt.Load(cmd.ExecuteReader());
        myConnection.Close();
    }
    return dt;
}

public DataTable selectCustID(int custID)
{
    string sql = "select custID, nameF, nameL from tblCustomers where custID = @custID";
    DataTable dt = new DataTable();

    using (SqlCommand cmd = new SqlCommand(sql, myConnection))
    {
        cmd.Parameters.AddWithValue("@custID", custID);
        myConnection.Open();
        dt.Load(cmd.ExecuteReader());
        myConnection.Close();
    }
    return dt;
}

and my code for the leftmenu.acsx file:

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="leftMenu.ascx.cs" Inherits="leftMenu" %>

<script runat="server">

void page_load()
{
    if (!Page.IsPostBack)
    {
        PopulateMenu();
    }
}

void PopulateMenu()
{
    MusicStoreSample2008 dbMusic = new MusicStoreSample2008("music");
    dlStyles.DataSource = dbMusic.fillMenu();
    dlStyles.DataBind();
}

// Search onTextChanged
public void search(object sender, EventArgs e)
{
    Response.Redirect("SearchBrowse.aspx?query=" + tbSearch.Text);
}

// Search onClick
public void style(object sender, EventArgs e)
{
    Response.Redirect("SearchBrowse.aspx?query=" + tbSearch.Text);
}

</script>

<div class="col-md-3 noPaddingRight">
        <div class="leftMenu equalHeightColumn">
            <div class="menubox">
                <b>Search</b><br />
                <asp:TextBox ID="tbSearch" runat="server" OnTextChanged="search" Width="113px"/><br />
                <asp:Button ID="btSearch" runat="server" OnClick="style" Text="search" />
                <br />
                <br />
                <br />

                <b>Browse</b><br />

                <asp:DataList ID="dlStyles" runat="server">
                    <ItemTemplate>
                        <asp:HyperLink ID="hlMenu" CssClass="menulink" runat="server" 
                        NavigateUrl='<%# Eval("StyleID", "SearchBrowse.aspx?style={0}") %>'
                        Text = '<%# Eval("StyleName") %>' />
                    </ItemTemplate>
                </asp:DataList>

                <br />
            </div>
        </div>
    </div>

and finally the code for my web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
<connectionStrings>

<!-- Use Sql Server Express connection string on server, comment it when not in use -->
<!--<add name="customers" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\A05Customers.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>-->
<!--<add name="music" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MusicStoreSample2008.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>-->

<!-- Use use LocalDB connection string in Visual Studio -->
<add name="customers" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\A05Customers.mdf;Integrated Security=True" providerName="System.Data.SqlClient"/>
<add name="music" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\MusicStoreSample2008.mdf;Integrated Security=True" providerName="System.Data.SqlClient"/>

</connectionStrings>
</configuration>

THANK YOU FOR YOUR HELP!

Related posts

Recent Viewed