All data is not displayed from SQL server to the websit.e-CodePudding

I am trying to display just the building names from the SQL server database on a webpage. But the webpage is only showing the first name repeatedly, with the correct count of buildings.

C# : -

On Page Load.

// Fetching the total count of buildings from the database and storing it in a variable.

            SqlConnection conBldng = new SqlConnection("Data Source=DESKTOP-PMQQD94\\SQLEXPRESS;Initial Catalog=loginAMS;Integrated Security=True");

            conBldng.Open();

            SqlCommand cmdBldngs = new SqlCommand("Select buildingName from roomData", conBldng);

            //var countBdlngs = cmdBldngs;

            //conBldng.Close();

            // Now to show the building names.

            SqlConnection conn = new SqlConnection("Data Source=DESKTOP-PMQQD94\\SQLEXPRESS;Initial Catalog=loginAMS;Integrated Security=True");
            
            SqlCommand cmd = new SqlCommand("select buildingName from buildingData", conn);

            conn.Open();

            string data = cmd.ExecuteScalar().ToString();

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {

                var p = new HtmlGenericControl("a") { InnerText = data };

                inputBuilding.Controls.Add(p);

                p.Attributes.Add("class", "card");

                p.Attributes.Add("href", "viewRooms.aspx");

            }

            conn.Close();

ASP.NET: -

<div  id="inputBuilding" runat="server">



</div>

Generated output: -

Generated Output

I want to retrieve all the names of just the buildings from the database.

CodePudding user response：

It looks like you're getting the row from the DataReader, but you're then ignoring it when you render. And you don't seem to need that ExecuteScalar step.

Using dr.GetString() should help you get your value.

while (dr.Read())
{
    var p = new HtmlGenericControl("a") { InnerText = dr.GetString(0) };
    inputBuilding.Controls.Add(p);
    p.Attributes.Add("class", "card");
    p.Attributes.Add("href", "viewRooms.aspx");
}

There are some good simple examples of using the DataReader here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader