Hello everyone,
I need some assistance in finding an error in my Delphi code. (I am new to the Delphi programming). I am using a Delphi application to build an excel spreadsheet.
When a part_number is entered for the first time(assuming it exists in db) application always runs , the second time the spreadsheet comesback with no data. Like my code is skipped ( while QRY.eof = False do -line is skipped). PLease help, below is my code.
/////////////////////////////////////////////////////////////////
procedure TForm1.BuildButtonClick(Sender: TObject);
var
SheetName: String;
x: Integer;
y: Integer;
Sheet: Variant;
ColumnRange: Variant;
fldcnt: Integer;
begin
///////////////////////////
QryC.Close();
QryC.SQL.Clear();
QryC.SQL.Add('select item_no from ITEM_MASTER where item_no =trim(:v_item)');
QryC.Parameters.ParamByName('v_item').Value := PartNumberEdit.Text;
QryC.Open();
if QryC.RecordCount <> 1 then
begin
ShowMessage('Couldn''t find part number ' + PartNumberEdit.Text + '.');
PartNumberEdit.text :='';
PartNumberEdit.SetFocus();
end
else if QryC.RecordCount = 1 then
begin
// Execute the stored procedure to prep the query.
Proc.ProcedureName := 'CostAnalyzerViewBuild_KANBAN';
Proc.Parameters.CreateParameter('v_partnumber', ftString, pdInput, 24, 0);
Proc.Parameters[0].Value := PartNumberEdit.Text;
Proc.ExecProc();
Proc.Parameters.Clear();
QRY.Close();
QRY.Open();
gauge.Visible := True;
gauge.Min := 0;
gauge.Position := 0;
gauge.Max := QRY.RecordCount;
//if VarIsEmpty(XLApp) then
XLApp:= CreateOleObject('Excel.Application');
//XLApp.Visible := True;
XLApp.Workbooks.Add(xlWBatWorkSheet);
SheetName := 'BOM Summary ' + IntToStr(SheetNumber);
XLApp.Workbooks[1].WorkSheets[1].Name := SheetName;
Sheet := XLApp.Workbooks[1].WorkSheets[SheetName];
ColumnRange := XLApp.Workbooks[1].WorkSheets[SheetName].Columns;
fldcnt := QRY.FieldDefs.Count; // the number of columns in the result set
for y := 0 to fldcnt - 1 do
begin
XLApp.Workbooks[1].Worksheets[1].Range['A1', 'A1'].EntireColumn.NumberFormat := '#,###,###,##0.0#_);[Red](#,###,###,##0.0#_)';
XLApp.Workbooks[1].Worksheets[1].Range['B1', 'B1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
XLApp.Workbooks[1].Worksheets[1].Range['C1', 'C1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
XLApp.Workbooks[1].Worksheets[1].Range['D1', 'D1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
XLApp.Workbooks[1].Worksheets[1].Range['E1', 'E1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
XLApp.Workbooks[1].Worksheets[1].Range['F1', 'F1'].EntireColumn.NumberFormat := '#,##0_);[Red](#,##0_)';
XLApp.Workbooks[1].Worksheets[1].Range['G1', 'G1'].EntireColumn.NumberFormat := '#,###_);[Red](#,###)';
ColumnRange.Columns[y+1].AutoFit;
Sheet.Cells[1, y+1] := QRY.FieldDefs.Items[y].Name;
end;
x := 2;
while QRY.eof = False do
begin
for y := 0 to fldcnt - 1 do
begin
Sheet.Cells[x, y+1] := QRY.Fields.Fields[y].AsString;
end;
x := x + 1;
// NEW SHEET?
if x >= 65530 then
begin
for y := 0 to fldcnt - 1 do
begin
ColumnRange.Columns[y+1].AutoFit;
end;
x := 2;
SheetNumber := SheetNumber + 1;
SheetName := 'BOM Summary ' + IntToStr(SheetNumber);
XLApp.Workbooks[1].Sheets.Add();
XLApp.Workbooks[1].Sheets[1].Name := SheetName;
Sheet := XLApp.Workbooks[1].Sheets[1];
ColumnRange := Sheet.Columns;
for y := 0 to fldcnt - 1 do
begin
ColumnRange.Columns[y+1].NumberFormat := '#,###,###,##0.0#_);[Red](#,###,###,###.00)';
ColumnRange.Columns[y+1].AutoFit;
Sheet.Cells[1, y+1] := QRY.FieldDefs.Items[y].Name;
end;
end;
Application.ProcessMessages();
gauge.Position := gauge.Position + 1;
QRY.Next();
PartNumberEdit.Text :=' ';
PartNumberEdit.SetFocus();
//end;
for y := 0 to fldcnt - 1 do
begin
ColumnRange.Columns[y+1].AutoFit;
end;
end;
Beep();
QRYC.Close();
QRY.Close();
XLApp.Visible := True;
SheetNumber := SheetNumber + 1;
gauge.Position := 0;
gauge.Visible := False;
end;
end;
///////////////////////////////////////////////////////
Thank you in advance,
Sonya