datagrid doesn't display data 4

[AP81]

I am trying to get a datagrid to display data based on an ADOQuery to a MySQL database. The query runs and resturns the correct results, however when trying to display them in a datagrid, the datagrid displays the correct number of rows to be returned, but with no data in them.

The database contains three fields: RecordNumber (num), CustomerCode (cust_code) and TemplateNo (temp_no).

Here is my code:

---snip-------

unit viewmod;

interface

uses

... ADODB, DB;

type
...

private
{ Private declarations }
public
{ Public declarations }
end;

var
frmViewMod: TfrmViewMod;
Global_DBConnection_String : String;
//ADOQuery to query database
lADOQuery : TADOQuery;

implementation

{$R *.dfm}

...


procedure TfrmViewMod.SearchByCustomer;
var
j : Integer;
strSearchString : String;
begin
//set global connection string
Global_DBConnection_String :=
'DRIVER={MySQL ODBC 3.51 Driver};' +
'SERVER=localhost;' +
'DATABASE=test;' +
'UID=root;' +
'PASSWORD=';

//validate whether data is in the field
if Trim(txtCustCode.Text) <> '' then
begin
//Assign customer code to search string variable to enable
//a search to be performed
strSearchString := '%' + UpperCase(txtCustCode.Text) + '%';
lADOQuery := TADOQuery.Create(nil);
with lADOQuery do
begin
//set connection string to use MySQL connection
ConnectionString := Global_DBConnection_String;
// Insert Record into Oracle DB using SQL
SQL.Text :=
'SELECT * FROM Lookup ' +
'WHERE cust_code LIKE '+
QuotedStr(strSearchString);
Open; //Open connection and query

//display results--> Problem starts
dsResults.DataSet := lADOquery;
dgdisplay.DataSource := dsResults;
dgdisplay.Refresh;
//-->problem ends --> Empty datagrid
//display column names
dgdisplay.Columns[0].FieldName := 'Record No.';
dgdisplay.Columns[1].FieldName := 'Customer Code';
dgdisplay.Columns[2].FieldName := 'Template No.';


end;
end;
end;

procedure TfrmViewMod.FormClose(Sender: TObject; var Action: TCloseAction);
begin
//close query
lADOQuery.Close;
end;

------snip---------

Now, I can loop through the dataset and go through all the records like this:

with lADOQuery.Recordset do
begin
if (RecordCount > 0) then //check whether records exist
begin
MoveFirst; //move to first record
//loop through dataset and retrieve data
for j := 1 to RecordCount do
begin
//Add search results to listbox
cboNumber.Items.Add(Fields['num'].Value);
//and so on...
MoveNext; //move to next record
end;
end;
end;

Can I do this and add the data to a datgrid? Or better yet can I set the datasource of the datagrid (like I have attempted earlier) and just display the data?

Thanks all.




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[hilfy]

Have you tried using the components on the form instead of creating a dynamic TADOQuery? Is there a special reason why it has to be done this way?

Try this:
-Put a TADOQuery on your form.
-Set the SQL and connection string for the query.
-Point your TDataSource to that query and the TdbGrid to the TDataSource.
-Set the query's &quot;Active&quot; property to True.

You should be able to see the data in the grid at design time.

-Set the query's &quot;Active&quot; property to False.

From here, all you have to do in your code is this:

 //validate whether data is in the field
  if Trim(txtCustCode.Text) <> '' then
  begin
    //Assign customer code to search string variable to enable
    //a search to be performed
    strSearchString := '%' + UpperCase(txtCustCode.Text) + '%';
    lADOquery.SQL.Add('SELECT * FROM Lookup ' +
        'WHERE cust_code LIKE '+
        QuotedStr(strSearchString));
    lADOquery.Open;  //Open connection and query

  end;

If the SQL will always be selecting from the same table, you can also set up persistent TFields for the query where you can define the DisplayLabel parameter for the column headings. You could also set up persistent columns in the grid where you can define what the column labels are.

-D

 

[AP81]

Yes that would work, however setting up the connection string for a visual control involves having to make a custom DNS for the SQL ODBC driver, which makes it a little inconvenient.




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[hilfy]

However, you could still dynamically create the connection string in your application. That way whatever you set it to at design time is a &quot;default&quot; and you change it when the app runs.

-D

 

[AP81]

hilfy,

Thanks for your quick response. I Tried what you said, but it still has the same problem. The dbgrid reacts as if it has loaded data (even returns the correct number of rows), but the cells are empty.

This is what I have done:

my form has:

1. A TADOQuery (lADOQuery)
2. A TDataSource (dsResults) with it's dataset set to lADOQuery
3. A Tdbgrid (dgDisplay) with it's datasource set to dsResults

Here is my code:

--------------------------------------
procedure TfrmViewMod.SearchByCustomer;
var
strSearchString : String;
begin
//set global connection string
Global_DBConnection_String :=
//'Provider=MSDASQL;' +
'DRIVER={MySQL ODBC 3.51 Driver};' +
'SERVER=localhost;' +
'DATABASE=test;' +
'UID=root;' +
'PASSWORD=';

//validate whether data is in the field
if Trim(txtCustCode.Text) <> '' then
begin
//Assign customer code to search string variable to enable
//a search to be performed
strSearchString := '%' + UpperCase(txtCustCode.Text) + '%';
//Set connection string for ADOQuery
lADOQuery.ConnectionString := Global_DBConnection_String;
// Insert Record into MySQL DB using SQL
lADOQuery.SQL.Add('SELECT * FROM Lookup ' +
'WHERE cust_code LIKE '+
QuotedStr(strSearchString));

//ladoquery.Active := True;
lADOquery.Open; /Open query

dgdisplay.Columns[0].FieldName := 'Record No.';
dgdisplay.Columns[1].FieldName := 'Customer Code';
dgdisplay.Columns[2].FieldName := 'Template No.';

end;
end;

--------------------------------------

Thanks.





------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[VintageWine]

I think your problems might be here:

>dgdisplay.Columns[0].FieldName := 'Record No.';
>dgdisplay.Columns[1].FieldName := 'Customer Code';
>dgdisplay.Columns[2].FieldName := 'Template No.';

(Note the last paragraph on hilfy's response)

Try commenting out these three rows.

What you are doing is setting the fieldname to names that, I suspect, do not exist in your dataset.

I think what you are trying to do is set the caption.

If your query is like &quot;SELECT *&quot; then the above code should look something like:

with dgdisplay.Columns do
for i:=0 to count-1 do
with Itemsdo
if(FieldName='RecordNo')then
Title.Caption:='Record No.'else
if(FieldName='CustomerCode')then
Title.Caption:=Customer Code'else
...............
...............

Instead of setting the grid captions, you could set the user friendly captions in the fields, like:

with lADOQuery.Fields do
for i:=0 to count-1 do
with Fieldsdo
if(FieldName='RecordNo')then
DisplayLabel:='Record No.'else
if(FieldName='CustomerCode')then
DisplayLabel:=Customer Code'else
...............
...............

Have fun
Simon

 

[AP81]

Thanks VintageWine & hilfy. Got it working now! A star for you help.




------------------------------------
There's no place like 127.0.0.1
------------------------------------