Displaying total data records based on user

Question

User-1994446809 posted

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();
    }

Answer 1

User1535942433 posted

Hi georgeakpan233,

Accroding to your description and codes,I'm guessing that you need to display the records total's num.

I have created a demo.If "createby" is a textbox ,you could use textchanged event.When you edit the textbox,it will count rows in the database.

More details,you could refer to below codes:

<div>
            <asp:Panel ID="Panel1" runat="server">
                <asp:TextBox ID="createby" runat="server" OnTextChanged="createby_TextChanged" AutoPostBack="true"></asp:TextBox>
                Recore Total:<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
            </asp:Panel> 
        </div>

Code-Behind:

protected void createby_TextChanged(object sender, EventArgs e)
        {
            string str;
            int count = 0;
            str = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Test where num =@CreatedBy", con);
            cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
            count = Convert.ToInt32(cmd.ExecuteScalar());
            Label1.Text = count.ToString();
            con.Close();
        }
  protected void Page_Load(object sender, EventArgs e)
        {
          
        }

Best regards,

Yijing Sun

Answer 2

User-1994446809 posted

Hi yij sun,

"createby" is a label that is used to identify a user.

Answer 3

User1535942433 posted

Hi georgeakpan233,

As far as I think,you could do just like this:

<asp:Panel ID="Panel1" runat="server">
                <asp:Label runat="server"  ID="creatby" Text="10"></asp:Label>
                Recore Total:<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
            </asp:Panel> 

Code-behind:

 protected void Page_Load(object sender, EventArgs e)
        {
            display1();
        }
        private void display1()
        {
            string str;
            int count = 0;
            str = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Test where num =@CreatedBy", con);
            cmd.Parameters.AddWithValue("@CreatedBy", creatby.Text.Trim());
            count = Convert.ToInt32(cmd.ExecuteScalar());
            Label1.Text = count.ToString();
            con.Close();
        }

Best regards,

Yijing Sun