0

I want to show the purchased courses for a user at 'Page Load Event' when user performed Login.

The conditions are :

  1. User has a unique email ID saved during SignUp
  2. When user purchased a course it is being saved w.r.t his email ID (could be multiple courses)
  3. Had multiple labels, but I want one course displayed on one label respectively

I have tried this code.

Table newadmission:

email pgenrolled
testmail Course 1
testmail Course 2
testmail Course 3 
protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(strcon);

    try
    {
        if (Session["get"].Equals(""))
        {
            Label1.Text = "NIL Value";
        }
        else if (Session["get"].Equals("userinfolbl"))
        {
            if (cn.State == System.Data.ConnectionState.Closed)
            {
                cn.Open();

                cmd = new SqlCommand("SELECT Distinct pgenrolled FROM newadmission WHERE email= '" + Session["em"].ToString() +"' ",cn);

                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Label1.Text = "You have purchased :  1. " + dr.GetValue(0).ToString(); // course 1 name
                        Label2.Text = "2. " + dr.GetValue(0).ToString(); // course 2 name
                        Label3.Text = "3. " + dr.GetValue(0).ToString(); // course 3 name
                    }

                    dr.Close();
                }

                cn.Close();
            }
        }
    }
    catch (Exception ex) 
    {
        Response.Write("<script>alert('Exception Unhandled : user_courses_pg '+'" + ex.Message.ToString() + "') </script>");
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shubham_Turalkar
  • 1
  • 1
  • 1
    Your data is spread across multiple rows, but your code only reads and processes one row at a time? To get the next row values you need to call `dr.Read()` again, so you'd have to track whether the user has changed or not and if not, append to the existing string (or something). – Dale K Dec 24 '22 at 21:17
  • As an aside, `SqlCommand` is disposable so you should construct it within a [`using`](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement) statement. It's also better to close the `SqlConnection` and data reader via a `using` as this will ensure they are properly disposed of even of an exception is thrown. (Unless that is you are reusing the connection, which you aren't in the code shown. See [Close and Dispose - which to call?](https://stackoverflow.com/q/61092).) – dbc Dec 25 '22 at 06:44
  • And as another aside, I would suggest you use parameterized SQL queries for the reasons discussed in [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/a/7505842). – dbc Dec 25 '22 at 06:50

1 Answers1

1

Would not a grid view be better for multiple items?

Just drop in a grid view, say like this:

<asp:GridView ID="GridView1" runat="server" Width="30%" CssClass="table">

</asp:GridView>
<h4 id="mybiglabel" runat="server"></h4>

And now in code behind, this:

void LoadGrid()
{
    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST5))
    {

        string strSQL =
            "SELECT * FROM newAdmission WHERE Email = @Email";

        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            cmdSQL.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Session["em"];
            DataTable rstData = new DataTable();
            rstData.Load(cmdSQL.ExecuteReader());
            GridView1.DataSource = rstData;
            GridView1.DataBind();

            this.mybiglabel.InnerText = $"Courses purchased = {rstData.Rows.Count}";                    
        }
    }
}

And the result is now this:

enter image description here

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51