Can't get the total row count

Question

I have a table which has 3 columns and each column has 5 rows.Now I wanna get those total numbers of rows in c# to create that number of labels dynamically as well as get the rows value for labels name.Similarly, creates same numbers of the textbox as well.Then in the runtime, i wanted to submit the value to the database by this textbox.

Note: here, if I increase the rows of the table,then the label and textbox will be increased automatically/dynamically as well as submitting value through textbox will perfectly work.

But , all I have done is only getting count value 1 , I just tried a lot but not getting the total count value which is actually 5 .

here, is my code...

private void Form1_Load(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        //string cmText = "select ProductId,ProductName,UnitPrice from tblProductInventory";
        string cmText = "Select Count(ProductId) from tblProductInventory";
        SqlCommand cmd = new SqlCommand(cmText, con);
        con.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            int count = rdr.FieldCount;
            while (rdr.Read())
            {
                //System.Windows.Forms.Label MyLabel;
                {
                    int y = 50;
                    Label myLabel = new Label();
                    for (int i = 0; i < count; i++)
                    {
                        myLabel = new Label();
                        myLabel.Location = new Point(88, y);
                        myLabel.Name = "txtVWReadings" + i.ToString();
                        myLabel.Size = new Size(173, 20);
                        myLabel.TabIndex = i;
                        myLabel.Visible = true;
                        myLabel.Text = rdr[i].ToString();
                        y += 25;
                        this.Controls.Add(myLabel);
                    }
                }
            }
        }
    }
}

And I got this output.

Answer 1

The issue seems that you are using query as count but you want the values of the field. So you can probably change it to

string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
    //string cmText = "select ProductId,ProductName,UnitPrice from tblProductInventory";
    string cmText = "Select Count(ProductId) from tblProductInventory";
    SqlCommand cmd = new SqlCommand(cmText, con);
    con.Open();
    Int32 count = (Int32) cmd.ExecuteScalar();
    int i = 1;
    cmText = "select ProductId,ProductName,UnitPrice from tblProductInventory";
    SqlCommand cmd1 = new SqlCommand(cmText, con);
    using (SqlDataReader rdr = cmd1.ExecuteReader())
    {
        int y = 50;
        Label myLabel = new Label();
        TextBox MyTxt = New TextBox();

        while (rdr.Read())
        {
            myLabel = new Label();
            myLabel.Location = new Point(88, y);
            myLabel.Name = "txtVWReadings" + i.ToString();
            myLabel.Size = new Size(173, 20);
            myLabel.TabIndex = i;
            myLabel.Visible = true;
            myLabel.Text = rdr[1].ToString(); //Since you want ProductName here
            y += 25;
            this.Controls.Add(myLabel);

            //Same Way Just include the TextBox
            //After all Position of TextBox 
            MyTxt.Text = rdr[2].ToString(); // I believe you need UnitPrice of the ProductName
            i++;
        }
    }
}

Answer 2

Count(columname) : Will count only the NOT NULL values in that column.

Count(*) : Will count the number of records in that table.

So I guess you have some NULL values in ProductId column. Change it to

Select Count(*) from tblProductInventory