[SOLVED] What is wrong with this code to fill a combobox from a select
query?
lazarus / 16-Databases

----------------------------------------------------------------------
georgelappies · 2011-07-20 13:12

Hi all

I want to use this code to populate a combobox on a form:

Code: [Select]
procedure TfrmWalkthru.cmbExistingMFDDropDown(Sender: TObject);
{
 the code here runs the select query to get the info to populate the
 combobox with all existing MFD numbers entered to allow editing.
}
var
  sqlExistingMFD: String;

begin
    // Check if we have an active connection. If so, let's close it.
  if MySQLConnection2.Connected then
     begin
          // The SQLTransaction2 gets activated automatically, but
before we can close
          // the connection we have to set the SQLTransaction2.Active
to false.
          SQLTransaction2.Active := False;
          MySQLConnection2.Close;
     end;

  // Create the sql statement
  sqlExistingMFD := 'SELECT MFD_Number FROM sasol_kpa.walkthu';

  // Set the connection parameters.
  MySQLConnection2.HostName := DB_HOSTNAME;
  MySQLConnection2.UserName := DB_USER;
  MySQLConnection2.Password := DB_PASSWD;
  MySQLConnection2.DatabaseName := DB_NAME;
  // Open the connection.
  MySQLConnection2.Open;

  // Execute the query
  if MySQLConnection2.Connected then
     begin
          SQLQuery2.SQL.Text := sqlExistingMFD;
          SQLQuery2.Open;
          if (SQLQuery2.IsEmpty) then
             begin
                  MessageDlg('Error! Database not available, contact
administrator',mtError, mbOKCancel, 0);
                  exit;
             end;

          // analyse query to get values for combobox
          SQLQuery2.First;
          while not SQLQuery2.EOF do;
          begin
            cmbExistingMFD.Items.Add(SQLQuery2.FieldByName('MFD_Number
').AsString);
            SQLQuery2.Next;
          end;
     end;


end;   

But the system hangs as soon as I click the combobox to drop down. The
code runs on the drop down of the combobox.

There is only 5 records in the table so it should be instantaneous...

----------------------------------------------------------------------
georgelappies · 2011-07-20 17:59

Ok, if I comment out the code like this Code: [Select]          //
analyse query to get values for combobox
          {SQLQuery1.First;
          while not SQLQuery1.EOF do;
          begin   }
            showmessage(SQLQuery1.Fields[0].AsString);
            DBComboBox1.Items.Add(SQLQuery1.Fields[0].AsString);
            {SQLQuery1.Next;
          end; } 

it shows the correct record in the message box and in the combobox, so
my guess is that it bombs out with the while not EOF portion but I
have NO idea as to why???

How do you step thru the query result if not by using EOF??? And or
does the while not EOF work for you?

EDIT:

Ok this workaround works Code: [Select]          SQLQuery1.First;
          SQLQuery1.Last;
          recCount := SQLQuery1.RecordCount;
          SQLQuery1.First;
          for i := 1 to recCount do
          begin
            DBComboBox1.Items.Add(SQLQuery1.Fields[0].AsString);
            SQLQuery1.Next;
          end;      
But why is it even necessary...

----------------------------------------------------------------------
Dick, from the internet · 2011-07-20 18:16

how about:

Code: [Select]procedure TfrmWalkthru.cmbExistingMFDDropDown(Sender:
TObject);
var
  sqlExistingMFD: String;
                        i : integer;          //<<< add this variable
begin
......

  SQLQuery1.Last;    //initializes .RecordCount
  SQLQuery1.First
  for i := 0 to SQLQuery1.RecordCount - 1 do
    begin
      DBComboBox1.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;

.......
end;

geno.

----------------------------------------------------------------------
georgelappies · 2011-07-20 18:19

Thanks geno, that approach works

----------------------------------------------------------------------
Lacak2 · 2011-07-21 05:17

If I see you have ";" at end of "   while not SQLQuery2.EOF do;"
This causes, that your begin ... end block is not executed in while
cycle

----------------------------------------------------------------------
georgelappies · 2011-07-21 06:01

Quote from: Lacak2 on July 21, 2011, 07:17:55 amIf I see you have ";"
at end of "   while not SQLQuery2.EOF do;"
This causes, that your begin ... end block is not executed in while
cycle

Lacak2, thanks so much. Indeed removing the offending ';' solves the
issue!

----------------------------------------------------------------------
JuhaManninen · 2011-07-21 06:14

Quote from: georgelappies on July 21, 2011, 08:01:11 amLacak2, thanks
so much. Indeed removing the offending ';' solves the issue!

Don't you guys use debugger? It reveals such problems easily.

Juha

----------------------------------------------------------------------
georgelappies · 2011-07-21 06:19

Quote from: JuhaManninen on July 21, 2011, 08:14:31 amQuote from:
georgelappies on July 21, 2011, 08:01:11 amLacak2, thanks so much.
Indeed removing the offending ';' solves the issue!

Don't you guys use debugger? It reveals such problems easily.

Juha

I actually used the debugger but am not sure how to use it. Could you
recommend a site explaining the details of how to properly debug with
'gdb' and lazarus?

----------------------------------------------------------------------
krexon · 2011-07-21 07:23

And you forgot to close query: SQLQuery2.Close

----------------------------------------------------------------------
JuhaManninen · 2011-07-22 11:13

Quote from: georgelappies on July 21, 2011, 08:19:54 amI actually used
the debugger but am not sure how to use it. Could you recommend a site
explaining the details of how to properly debug with 'gdb' and
lazarus?

It should be simple.
Set a breakpoint somewhere by clicking the editor's left border area,
start the application and wait for the breakpoint to hit. Then step
forward in code with F7 or F8.

If it doesn't work then you haven't configured a debugger. Do it in
Options... -> Debugger.