CODEWITHSUNDEEP
c#asp.nett-sqlado.net
wickenex
wickenex

Reputation: 53

How to retrieve multiple values from SQL to label

I want to retrieve multiple values from my SQL database to several labels on a single aspx page. What is happening now, is that I am successfully retrieving the values from the first row but then when it comes to the second row and afterwards, it keeps repeating the values from the first row.

This is the code that I am using to select the values from the database. Can anyone help me solve this to retrieve other rows aswell?

Code behind page:

public partial class filmes : System.Web.UI.Page
{
    string strConnString = ConfigurationManager.ConnectionStrings["ENTDB"].ConnectionString;
    string str;
    SqlCommand com;
    protected void Page_Load(object sender, EventArgs e)
    {

        SqlConnection con = new SqlConnection(strConnString);
        con.Open();
        str = "select * from FilmesSeries order by IDFilmesSeries desc";
        com = new SqlCommand(str, con);
        SqlDataReader reader = com.ExecuteReader();

        int ID=1;
        while (reader.Read())
        {
           ID++;
           Label tituloControl= (Label) Page.FindControl("Titulo"+ID);
           if(tituloControl!=null) 
           {
              tituloControl.ID=" Titulo"+ID;           
              tituloControl.Text= reader["Titulo"].ToString();
           } 

           Label GeneroControl= (Label) Page.FindControl("Genero"+ID);
           if(GeneroControl!=null) 
           {
              GeneroControl.ID=" Genero"+ID;           
              GeneroControl.Text= reader["NomeGenero"].ToString();
           }
        }

        reader.Close();
        con.Close();

    }
}

filmes.aspx page:

 <div class="row">
        <div class="col-md-5">
            <asp:Label id="Titulo1" runat="server" />
            <asp:Label id="Genero1" runat="server" />
        </div>
    </div>

    <hr>

    <div class="row">
        <div class="col-md-5">
            <asp:Label id="Titulo2" runat="server" />
            <asp:Label id="Genero2" runat="server" />
        </div>
    </div>

Upvotes: 2

Views: 2743

Answers (2)

GH Karim
GH Karim

Reputation: 323

If you want to show all the values for each row and assign them in labels. You could create those labels dynamically for each row and assign them to the page:

int ID=1;
while (reader.Read())
{
   ID++;
   Label TituloLabel= new Label();
   TituloLabel.ID=" Titulo"+ID;
   TituloLabel.Text= reader["Titulo"].ToString();

   Label GeneroLabel= new Label();
   GeneroLabel.ID=" Genero"+ID;
   GeneroLabel.Text= reader["NomeGenero"].ToString();

   Page.Controls.Add(TituloLabel);
   Page.Controls.Add(GeneroLabel);
}

[Edit] To bind the controls to a certain control on the page. You can get that control by id. As an example, assuming you have a div for example:

<div id="placeHolder" runat="server"></div>

The following code binds the created labels to it simply by finding that div:

Page.FindControl("placeHolder").Controls.Add(TituloLabel);
Page.FindControl("placeHolder").Controls.Add(GeneroLabel);

[Update] This code will help to generate labels for each row however if you want to map out previously created ones, then Andrei's approach should work!

[Edit] [2] To map out to hard-coded labels:

int ID = 0;
while (reader.Read())
{
   ID++;
   Label tituloControl= (Label) Page.FindControl("Titulo"+ID);
   if(tituloControl!=null) 
   {         
      tituloControl.Text= reader["Titulo"].ToString();
   } 

   Label GeneroControl= (Label) Page.FindControl("Genero"+ID);
   if(GeneroControl!=null) 
   {       
      GeneroControl.Text= reader["NomeGenero"].ToString();
   }
}

enter image description here

Upvotes: 0

Andrei
Andrei

Reputation: 56688

First you need to get the right index of controls to assign to:

int i = 1;
while (reader.Read())
{
    ...
    i++;
}

Then you need to retrive these controls from the page:

int i = 1;
while (reader.Read())
{
    Label titleLabel = (Label)this.FindControl("Titulo" + i);
    Label genreLabel = (Label)this.FindControl("NomeGenero" + i);
    ...
    i++;
}

And finally assign

int i = 1;
while (reader.Read())
{
    Label titleLabel = (Label)this.FindControl("Titulo" + i);
    Label genreLabel = (Label)this.FindControl("NomeGenero" + i);
    titleLabel.Text = reader["Titulo"].ToString();
    genreLabel.Text = reader["NomeGenero"].ToString();
    i++;
}

Be aware though that if you have say 10 items in database and only 9 pairs of labels on the page (that is Titulo9 is the last one, there is no Titulo10), code above will fail, so make sure to do some error handling.

There are ways to optimize over that:

  1. Put all your labels into an array (make sure to maintain right order), and then you can do titleLabels[i]
  2. Have a repeater or a gridview, which represents a list of your entities, and defines template with necessary labels. Then you could bind data to them from your database, which would result in a much prettier and easier to maintain solution, and less error prone. However that is really a separate subject, not too fit to this question, look up tutorials online for that.

Upvotes: 1

Related Questions