Display total number records based on user in Label using C# in ASP.Net

Last Reply one month ago By George616

Posted one month ago

Hello Forum, 

I want to display the total number of records in the data table based on column name (user). For example, if a user A created 11 records in total and user B inserted or created 20 records, then when user A logs in the label should display the total number of records for user A, which in this case is 11. if userB logs in, then the label should display 20 records; All these is based on the user.

Here is what I tried but it is not working; instead its showing 1 as its value. In my code, I have two label controls to display these total records from two tables and based on the user who created

    protected void Page_Load(object sender, EventArgs e)
    {
            display1();
            display2();
    }
    private void display1()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table1 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        cmd.ExecuteScalar();

        sda.Fill(ds);
        label1.Text = ds.Tables[0].Rows.Count.ToString();
        con.Close();
    }
    private void display2()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table2 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        cmd.ExecuteScalar();
       
        sda.Fill(ds);
        label2.Text = ds.Tables[0].Rows.Count.ToString();
        con.Close();
    }

 

Posted one month ago
George616 says:
 private void display1()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table1 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        cmd.ExecuteScalar();
 
        sda.Fill(ds);
        label1.Text = ds.Tables[0].Rows.Count.ToString();
        con.Close();
    }
    private void display2()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table2 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        cmd.ExecuteScalar();
        
        sda.Fill(ds);
        label2.Text = ds.Tables[0].Rows.Count.ToString();
        con.Close();
    }

Change above with below code.

    private void display1()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table1 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        label1.Text = Convert.ToString(cmd.ExecuteScalar());
        con.Close();
    }
    private void display2()
    {
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Table2 where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        label2.Text = Convert.ToString(cmd.ExecuteScalar());
        con.Close();
    }

 


Posted one month ago

I think I got the answer; I changed the code to this.

and its working fine

    private void displaycert()
    {
        int count = 0;
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM CertTbl where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        count = Convert.ToInt32(cmd.ExecuteScalar());
        lblTotal.Text = count.ToString();
        con.Close();
    }
    private void displayinvoice()
    {
        int count = 0;
        SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM InvoiceTable where CreatedBy =@CreatedBy", con);
        cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
        count = Convert.ToInt32(cmd.ExecuteScalar());
        labelinv.Text = count.ToString();
        con.Close();
    }