C# Create buttons from database with missing row

Here some things you really need to be looking at, i take it from your code your still new to programming since your way off best bractices.

1. int _bId = Int32.Parse(cmd.ExecuteScalar().ToString()); This is dangerous
2. In your Page Layout, you have an entire Method implementation, you need to move the code to its own respected method and call it from page layout.
3. Why do you need to use the database auto generated id? you can auto generate your own id after you retrieve all the data from one single request instead of 3 as @Alex K. mentioned

4. int NumOfButtons = 40; int i = 1; cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";

You don't know how many buttons you have in your DB, you cannot hardcode the number. instead do something like this "SELECT id, bName, col, row FROM Buttons;"

if (_bId != null)
            {
           //   Here.. You need to check if _bId (For ex: 3) exists in database(by making a query), and then proceed with creating button which is rest of your code.


            }

Note: You do not need to query again and again in for loop, you can retrieve all data at once by using below.

SELECT bName, col, row FROM Buttons where id='"+bId+"'

If this query retruns a row, // you proceed with rest of your code, create Button else bId++;

Use ExecuteReader() with a query which selects all buttons. I assume MS Sql Sever supporting TOP()

  cmd.CommandText = "SELECT TOP(" + NumOfButtons + ") id, bname,col,row FROM Buttons ORDER BY id";
  SqlDataReader reader = cmd.ExecuteReader();
  while (reader.Read())
  {
        // process the button
  }

If we're skipping the part "your approach is not really efficient" but just trying to make this code working, then you should modify it to look like this:

SQLFunctions Lgn = new SQLFunctions();
Lgn.ConnectionToday();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SQLFunctions.conn;

int NumOfButtons = 40;
int i = 1;

//            int counter = 0;

while ( i <= NumOfButtons)
{
    cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";

    int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());

    Button btn = new Button();
    {
        btn.Tag = _bId;
        btn.Dock = DockStyle.Fill;
        btn.Margin = new Padding(10, 10, 10, 10);

        cmd.CommandText = "SELECT bName FROM Buttons where id='" + btn.Tag + "'";
        btn.Text = cmd.ExecuteScalar().ToString();
        string btn_name = cmd.ExecuteScalar().ToString();
        btn.Name = btn_name.ToString();

        /*                    btn.Click += delegate
         {
         pass_txt.Clear();
         username_txt.Text = btn_name;
         username_lbl.Text = btn_name;
         username_lbl.Visible = true;
         pass_txt.ReadOnly = false;
         };*/

    }
    cmd.CommandText = "SELECT col FROM Buttons where id='" + btn.Tag + "'";
    int btn_col = Int32.Parse(cmd.ExecuteScalar().ToString());
    //                MessageBox.Show(btn_col.ToString());
    cmd.CommandText = "SELECT row FROM Buttons where id='" + btn.Tag + "'";
    int btn_row = Int32.Parse(cmd.ExecuteScalar().ToString());
    //                MessageBox.Show(btn_row.ToString());
    tableLayoutPanel4.Controls.Add(btn, btn_col, btn_row);

    i++;

}
SQLFunctions.conn.Close();

But if we're talking about code I would really use to solve your case, I would change column type of Id to int and with 1 simple SQL command took first NumOfButtons from table ordered by Id. Here is simple SQL request which would work:

SELECT id, col, row ORDER BY id FETCH NEXT (@NumOfButtons) ROWS ONLY

And I would use this structure to run it:

using (var reader = cmd.ExecuteReader($"SELECT id, col, row ORDER BY id FETCH NEXT ({NumOfButtons}) ROWS ONLY"))
{
    while (reader.Read())
    {
        var id = reader.GetInt32(0);
        var col = reader.GetString(1);
        var row = reader.GetString(2);

        // ToDo: Your stuff here
    }
}

Do you know what the sequence will be i.e. is is contiguous that is to say it will start at a number and is expected to increment by 1 each time? If that is the case then I can give you a bit of pseudo code that should solve your problem. I don't have visual studio to hand to check all the code.

Then write a linq statement that finds the lowest ID in the sequence. Then write a linq statement that finds the highest ID in the sequence.

Then use a

int last = lowest;
for (int i = lowest; i <= highest; i++)
{
int curr = (from int btn in collection/datatable where btn.ID == i select btn.ID).first;

if (curr == (last + 1))
{
curr = i;
}
else
{
++last
curr = last;
}

  //make the button based on curr or not as needed

++last;
}
}