hi i have done following code to get data from table to String grid combo box and pass it to colums, but once i select data in first row its ok if i go to second row and select the items from combobox it does not show only shows first item which i select only, please check the attach images and code what i did please

hi i have done following code to get data from table to String grid combo box and pass it to colums, but once i select data in first row its ok if i go to second row and select the items from combobox it does not show only shows first item which i select only, please check the attach images and code what i did please

procedure TfQuotation.StringGrid1GetEditorProp(Sender: TObject; ACol,
ARow: Integer; AEditLink: TEditLink);
begin
with stringgrid1 do
case acol of 0:
begin
ClearComboString;
Combobox.Items.Add('HARDWARE');
Combobox.Items.Add('SERVICE');
end;
end;

end;
//================================================================
procedure TfQuotation.StringGrid1ComboDropDown(Sender: TObject; ARow,
ACol: Integer);
begin
with stringgrid1 do
case acol of 2:
begin
ClearComboString;

if (cells[0, arow] = 'HARDWARE') then
begin
QItem.Close;
QItem.Open;
QItem.First;
repeat
Combobox.Items.Add(QItem.FieldByName('Description').AsString);
QItem.Next;
until QItem.Eof;

end;
//======================================================================

if (cells[0, arow] = 'SERVICE') then
begin
QService.Close;
QService.Open;
QService.First;
repeat
Combobox.Items.Add(QService.FieldByName('ServiceName').AsString);
QService.Next;
until QService.Eof;
end;
//======================================================================
end;
end;
end;
//==========================================================================

procedure TfQuotation.StringGrid1SelectCell(Sender: TObject; ACol,
ARow: Integer; var CanSelect: Boolean);
begin
with stringgrid1 do
case acol of 2:
begin
ClearComboString;

if (cells[0, arow] = 'HARDWARE') then
begin
QItem.Close;
QItem.Open;
QItem.First;
repeat
Combobox.Items.Add(QItem.FieldByName('Description').AsString);
QItem.Next;
until QItem.Eof;

end;
//======================================================================

if (cells[0, arow] = 'SERVICE') then
begin
QService.Close;
QService.Open;
QService.First;
repeat
Combobox.Items.Add(QService.FieldByName('ServiceName').AsString);
QService.Next;
until QService.Eof;
end;
//======================================================================
end;
end;
end;

//========================================================================

procedure TfQuotation.StringGrid1SetEditText(Sender: TObject; ACol,
ARow: Integer; const Value: string);
var
Item: string;
begin
with stringgrid1 do
case acol of
2:
if StringGrid1.Cells[0, arow] = 'HARDWARE' then
begin
Item := '';
Item := StringGrid1.Cells[2, ARow];

QItem.Close;
QItem.SQL.Clear;
QItem.SQL.Add('Select * from Item');
QItem.SQL.Add(Format('Where Description = ''%s''', [Item]));
QItem.Open;

StringGrid1.Cells[1, ARow] := QItem.FieldByName('PartNo').AsString;
StringGrid1.Cells[4, ARow] := QItem.FieldByName('Avrcost').AsString;
StringGrid1.Cells[5, ARow] := QItem.FieldByName('SellingPrice').AsString;
end;

end;


with stringgrid1 do
case acol of
2:
if StringGrid1.Cells[0, arow] = 'SERVICE' then
begin
Item:= '';
Item:= StringGrid1.Cells[2, ARow];
QService.Close;
QService.SQL.Clear;
QService.SQL.Add('Select * From Service');
QService.SQL.Add(Format('Where ServiceName = ''%s''',[Item]));
QService.Open;
StringGrid1.Cells[1, ARow] := QService.FieldByName('ServiceID').AsString;
StringGrid1.Cells[4, ARow] := QService.FieldByName('Servicecost').AsString;
StringGrid1.Cells[5, ARow] := QService.FieldByName('SellingPrice').AsString;
end;
end;


end;

Comments

  1. Yes the QService.SQL.Text := 'Select * From Service'; (and for QItem too) needs to be added in between the Close and Open in the events I mentioned. The others don't need to be adjusted. But I agree with you that rethinking this and optionally using a separate routine which retrieves the records would be much more clear. There could even be one function created RetrieveComboLinesForTable(Tablename: String); created which fills the combobox without even using QService and QItem but using a dynamically created query. It would mean a lot less code.

    ReplyDelete
  2. A somewhat cleaned-up version (which still can be improved on a lot). I just types this out of hand so it could contain some errors. I also assumed IBX as dataset-components but that can be changed easily. (You see that I prefer the dynamically created SQL-queries over the QItem and QService, which can be removed now).

    procedure RetrieveComboLinesForTable(ComboBox: TComboBox; DB: TIBDatabase;
    TableName, FieldName: String);
    var
    Qr: TIBQuery;
    begin
    ComboBox.Clear;
    Qr := TIBQuery.Create(nil);
    try
    Qr.Database := IBDatabase1;
    Qr.SQL.Text := Format('SELECT %s FROM %s', [FieldName, TableName]);
    Qr.Open;
    while not Qr.Eof do
    begin
    ComboBox.Items.Add(Qr.FieldByName(FieldName));
    Qr.Next;
    end;
    finally
    Qr.Free;
    end;
    end;

    procedure TfQuotation.StringGrid1GetEditorProp(Sender: TObject;
    ACol, ARow: Integer; AEditLink: TEditLink);
    var
    Sq: TStringGrid;
    begin
    Sg := TStringGrid(Sender);
    case Sg.ACol of
    0:
    begin
    ClearComboString;
    ComboBox.Items.Add('HARDWARE');
    ComboBox.Items.Add('SERVICE');
    end;
    end;
    end;

    // ================================================================
    procedure TfQuotation.StringGrid1ComboDropDown(Sender: TObject;
    ARow, ACol: Integer);
    var
    Sq: TStringGrid;
    begin
    Sg := TStringGrid(Sender);
    case Sg.ACol of
    2:
    begin
    if (Sg.cells[0, Sg.ARow] = 'HARDWARE') then
    RetrieveComboLinesForTable(ComboBox, DB, 'Description');
    if (Sg.cells[0, Sg.ARow] = 'SERVICE') then
    RetrieveComboLinesForTable(ComboBox, DB, 'ServiceName');
    end;
    end;
    end;
    // ==========================================================================

    procedure TfQuotation.StringGrid1SelectCell(Sender: TObject;
    ACol, ARow: Integer; var CanSelect: Boolean);
    var
    Sq: TStringGrid;
    begin
    Sg := TStringGrid(Sender);
    case Sg.ACol of
    2:
    begin
    if (Sg.cells[0, Sg.ARow] = 'HARDWARE') then
    RetrieveComboLinesForTable(ComboBox, DB, 'Item', 'Description');
    if (Sg.cells[0, Sg.ARow] = 'SERVICE') then
    RetrieveComboLinesForTable(ComboBox, DB, 'Service', 'ServiceName');
    end;
    end;
    end;

    // ========================================================================

    procedure TfQuotation.StringGrid1SetEditText(Sender: TObject;
    ACol, ARow: Integer; const Value: string);
    var
    Item: string;
    Qr: TIBQuery;
    Sq: TStringGrid;
    begin
    Sg := TStringGrid(Sender);
    if Sg.ACol <> 2 then exit;

    if Sg.cells[0, Sg.ARow] = 'HARDWARE' then
    begin
    Item := Sg.cells[2, Sg.ARow];
    Qr := TIBQuery.Create(nil);
    try
    Qr.Database := DB;
    Qr.SQL.Clear;
    Qr.SQL.Add('Select * from Item');
    Qr.SQL.Add(Format('Where Description = ''%s''', [Item]));
    Qr.Open;
    Sg.cells[1, ARow] := Qr.FieldByName('PartNo').AsString;
    Sg.cells[4, ARow] := Qr.FieldByName('Avrcost').AsString;
    Sg.cells[5, ARow] := Qr.FieldByName('SellingPrice').AsString;
    finally
    Qr.Free;
    end;
    end;

    if Sg.cells[0, Sg.ARow] = 'SERVICE' then
    begin
    Item := Sg.cells[2, Sg.ARow];
    Qr := TIBQuery.Create(nil);
    try
    Qr.Database := DB;
    Qr.SQL.Clear;
    Qr.SQL.Add('Select * From Service');
    Qr.SQL.Add(Format('Where ServiceName = ''%s''', [Item]));
    Qr.Open;
    Sg.cells[1, ARow] := Qr.FieldByName('ServiceID').AsString;

    ReplyDelete
  3. Sg.cells[4, ARow] := Qr.FieldByName('Servicecost').AsString;
    Sg.cells[5, ARow] := Qr.FieldByName('SellingPrice').AsString;
    finally
    Qr.Free;
    end;
    end;

    end;

    ReplyDelete
  4. Jude De Silva Just a tangential request/comment:

    When you post code, post it at the very least on one of the code paste sites and not inline in your post as above. This will retain formatting and make it easier to make corrections/updates etc. There are many such sites, e.g
    https://pastebin.com
    gist.github.com - Create a new Gist · GitHub
    http://paste.ie
    https://www.pastiebin.com

    Of these I suppose I'd suggest the top 2. Also, make sure you register and perform your pastes logged in, so that you retain control of your pastes.

    And then finally, if your projects are bigger or multiple files it's perhaps worth posting it on github or bitbucket, to allow others to quickly and easily get your code in compileable form. (If you're not used to using version control then this is something that you really should start learning.)

    ReplyDelete

Post a Comment