Refresh an array of buttons

[prrm333]

I have the following code on form load that works correctly to create an array of buttons to filter a database by last name for A-Z and #'s and disable any buttons that have no results return for that letter:

private void getAlpha()
{
int locX = 12,
locY = 76,
i = 0;
string sqlText = "";
char[] delimiterChars = { ',' };
string alphabet = "A,B,C,D,E,F,G,H,I,J,K,L,M,#,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,All";
string[] words = alphabet.Split(delimiterChars);
foreach (string s in words)
{
button = new Button();
button.Name = s + "_button";
string buttonName = s;
button.Size = new Size(30, 30);
button.Location = new Point(locX, locY);
button.Text = s.ToString();
button.Font = new Font(button.Font, FontStyle.Bold);
this.Controls.Add(button);
button.Refresh();
i = i + 1;
button.Click += new EventHandler(button_click);
locY += 30;
if (i == 14)
{
locY = 76;
locX = 42;
}
m_dtContacts.Clear();
if (s == "#")
{
sqlText = "SELECT * FROM Contacts WHERE [LastName] LIKE '[0-9]%'";
}
else if (s == "All")
{
sqlText = "Select * From Contacts order by [LastName],[FirstName]";
}
else
{
sqlText = "Select * From Contacts where [LastName] like '" + buttonName + "%" + "'";
}
m_daDataAdapter =
new OleDbDataAdapter(sqlText, m_cnADONetConnection);
OleDbCommandBuilder m_cbCommandBuilder =
new OleDbCommandBuilder(m_daDataAdapter);
m_daDataAdapter.Fill(m_dtContacts);
if (m_dtContacts.Rows.Count == 0)
{
button.Enabled = false;
}
else
{
button.Enabled = true;
}
}
}

On form load it works as expected but calling it from an update, add, delete function does nothing. It should disable or enable a button based on adding or delete the only record with that letter. I have tried modifying it for these from the form load code but so far nothing.

Any ideas or suggestions would be appreciated.

 

[jmeckley]

you have a select n+1 issue with this code. while it may not be broken, performance is poor because you are issuing 28 individual queries. We can address that later.

as for the immediate problem. nothing jumps out that is absolutely wrong. Are you sure the database is updated and this method is called after the commit?

I would solve the problem differently though. rather than dynamically generating the same buttons at runtime, define the buttons at design time. then you can have code like this to set whether the button is active or not.

privte Control[] buttons = new [] {
   LastNameStartingWithA,
   LastNameStartingWithB,
   LastNameStartingWithC,
   LastNameStartingWithD,
   ...
   LastNameStartingWithZ,
};

private void SetButtonStatus()
{
  Array.ForEach(buttons, b => b.Enabled = false);

  var results = new DataTable();
  const string sql = "select distinct left(lastname, 1) as letter from Contacts";
  using(var command = connection.CreateCommand())
  {
     command.CommandText = sql;
     results.Load(command.ExecuteReader());
  }

  foreach(DataRow row in results.Rows)
  {
     var letter = row["letter"].ToString();

     int i;
     if(int.TryParse(letter, out i))
     {
       LastNameStartingWithNumber.Enabled = true;
       continue;
     }

     buttons
         .First(b => b.CommandArgument == letter)
         .Enabled = true;
  }
}

i'm sure this needs some tweaking as I'm writing it from memory, but something like that should work.

note that I'm using CommandArgument to define A-Z, # & All. Not the id of the control. the ID is strictly a technical requirement that shouldn't have business meaning. that's what the command argument property is for.
we have also reduced the number of queries from 28 to 1.



Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[prrm333]

I'm not sure about changing the code yet as I spent a little time with your suggestions but didn't get too far. However to the best of my testing I think the problem with my code and updating is in the following section of my code:

if (m_dtContacts.Rows.Count == 0)
{button.Enabled = false;}
else
{button.Enabled = true;}

When I put a break in this section the button.Enabled is equal to:

{Text = "A"} or which ever letter it is evaluating.

I haven't found a way to make it equal to the button name which would be in this case A_button.