The following code is largely based on an example sent to me by BigChimp as a response to my thread "Create (MSSQL) Database in code". Once again I'm pulling out my hair over something that looks simple - opening a database and reading a single field's content into each row of a grid.
I tried initially using a TDBGrid but, when that didn't work, switched to a TStringGrid which also failed. Theoretically the TDBGrid will just show the content automatically when correctly connected but, for whatever reason, mine didn't.
With the TStringGrid the problem doesn't seem to be with the grid itself, but the way the data is read. At the point of opening my query (Q), Q.Eof immediately returns true, so my grid is empty and my counter remains at zero. However, Q.RecordCount correctly shows 6 - the number of tables in my database, whose names I want to display.
As far as I can tell, I've set all the important parameters and controls exactly as in BigChimp's example, which is working for me. Can anyone see if I've missed something really obvious?
{ Q (TSQLQuery) Database already set to Conn
Q.Datasource set to DS
Tr (TSQLTransaction) Database set to Conn
DS.Dataset set to Q }
Conn.Connected:= True;
MsgText.Text:= 'Connected to master database';
// Check for existence of the Bookings database.
Conn.Connected:= False;
Try
// Attempt to connect to Bookings
Conn.DatabaseName:= DN;
// Enable autocommit so that Create will work
Conn.Params.Add('AutoCommit=True');
Conn.Connected:= True;
// Error 911 indicates database doesn't exist
// If no error (lines below would be skipped), show tables in Bookings Database
// Close connection and query before reopening to ensure we can read data sequentially
MsgText.Text:= 'Connected to ' + DN + ' database';
Conn.Connected:= False;
Q.Close;
Conn.Open;
Instruc:= 'Select Name from Sys.Tables order by Name';
Q.SQL.Text:= Instruc;
Tr.StartTransaction;
Q.Open;
Counter:= 0;
// Recordcount shows 6 records here but Eof is true as soon as query is opened & grid is blank
While Not Q.Eof do
begin
Inc(Counter);
DBG.Cells[0, Counter]:= Q.FieldByName('Name').AsString;
Q.Next;
end;
ShowMessage(IntToStr(Q.RecordCount));
Q.Close;
Tr.Commit;
ShowMessage('Rows returned: ' + IntToStr(Counter));
Thanks.