Saludos gente del foro, estoy con un problema y no le encuentro la esquina a la pelota, es un programa en el cual intento registrar unos campos haciendola importar directamente de un libro de excel, pero al importarla me coge el error de "key violation", he revisado las tablas, el còdigo y aùn no hallo el origen del problema, aquí les doy las dos partes del código:
Código de Importar:
Código Delphi
[-]
procedure TImportar.Button1Click(Sender: TObject);
var
xls : Variant;
iFila,Total,Puntero: Integer;
Porc: Real;
fLoop: Boolean;
Fecha: TDate;
Loteria: String;
NomZod: String;
Triple: String;
begin
if OpenDialog1.Execute then
begin
Button1.Enabled := False;
xls := CreateOleObject('Excel.Application');
xls.WorkBooks.Open( OpenDialog1.FileName );
xls.Visible := false;
fLoop := true;
iFila := 2;
Puntero := 0;
while fLoop do
begin
if VarToStr( xls.WorkSheets[1].Cells[iFila,1].Value ) = '' then
fLoop := false
else
begin
inc(iFila);
Label1.Caption := 'Procesando ...';
Label1.Update;
end;
end;
Total:=iFila;
ProgressBar1.Max:=iFila;
try
fLoop := true;
iFila := 2; while fLoop do
begin
if VarToStr( xls.WorkSheets[1].Cells[iFila,1].Value ) = '' then
fLoop := false
else
begin
Fecha:=StrtoDate(VarToStr( xls.WorkSheets[1].Cells[iFila,1].Value));
Loteria:=VarToStr( xls.WorkSheets[1].Cells[iFila,2].Value);
if (VarToStr( xls.WorkSheets[1].Cells[iFila,7].Value)) = '' then
NomZod := 'No Hay'
else
NomZod:=VarToStr( xls.WorkSheets[1].Cells[iFila,7].Value);
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Text:='SELECT * FROM Loterias WHERE (Fecha = :Fec) AND (Loteria = :Lot)';
Query1.ParamByName('Lot').asString := Loteria;
Query1.ParamByName('Fec').asDate := Fecha;
Query1.RequestLive := true;
Query1.Open;
if Query1.IsEmpty then
Begin
Query2.Close;
Query2.SQL.Clear;
Query2.SQL.Text:='SELECT NomLot FROM NombreLoterias WHERE (NomLot = :Lot)';
Query2.ParamByName('Lot').asString := Loteria;
Query2.RequestLive := true;
Query2.Open;
if Query2.IsEmpty then
Begin
Query2.Insert;
Query2.FieldByName('NomLot').AsString:=Loteria;
Query2.Post;
end;
Query3.Close;
Query3.SQL.Clear;
Query3.SQL.Text:='SELECT * FROM Zodiacales WHERE (NomZod = :Zod)';
Query3.ParamByName('Zod').asString := NomZod;
Query3.RequestLive := true;
Query3.Open;
if Query3.IsEmpty then
Begin
Query3.Insert;
Query3.FieldByName('NomZod').AsString := NomZod;
Query3.Post;
end;
Query1.Insert;
Query1.FieldByName('Id').AsInteger := Table1.RecordCount+1;
Query1.FieldByName('Fecha').AsDateTime := StrtoDate(VarToStr( xls.WorkSheets[1].Cells[iFila,1].Value));
Query1.FieldByName('Loteria').AsString := VarToStr( xls.WorkSheets[1].Cells[iFila,2].Value);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,3].Value);
Query1.FieldByName('ADia').AsString := completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,4].Value);
Query1.FieldByName('BDia').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,5].Value);
Query1.FieldByName('CDia').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,6].Value);
Query1.FieldByName('ZDia').AsString := VarToStr( xls.WorkSheets[1].Cells[iFila,7].Value);
Query1.FieldByName('ATarde').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,8].Value);
Query1.FieldByName('BTarde').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,9].Value);
Query1.FieldByName('CTarde').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,10].Value);
Query1.FieldByName('ZTarde').AsString := VarToStr( xls.WorkSheets[1].Cells[iFila,11].Value);
Query1.FieldByName('ANoche').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,12].Value);
Query1.FieldByName('BNoche').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,13].Value);
Query1.FieldByName('CNoche').AsString :=completarceros(triple);
triple:=VarToStr( xls.WorkSheets[1].Cells[iFila,14].Value);
Query1.FieldByName('ZNoche').AsString := VarToStr( xls.WorkSheets[1].Cells[iFila,15].Value);
Query1.Post;
end;
end;
inc(iFila);
inc(Puntero);
Porc:=IFila*100/Total;
Label1.Caption := FloattoStr(Trunc(Porc))+'%';
ProgressBar1.StepIt;
Label1.Update;
end
finally
xls.Quit
end;
Label1.Caption := 'Completado';
Label1.Update;
end
else
begin
Button1.Enabled := True;
Principal.Importar1.Enabled:=True;
Close;
end;
Query1.Destroy;
Query2.Destroy;
Query3.Destroy;
end;
Código de la creación de la tabla:
Código Delphi
[-]
If not Table2.Exists then
begin
with TTable.Create(self) do
try
Databasename:=ExtractFilePath(Application.ExeName);
TableType:=ttParadox;
TableName:='Loterias.db';
with FieldDefs do
begin clear;
with AddFieldDef do
begin
Name := 'Id';
DataType := ftInteger;
Required := true;
end;
with AddFieldDef do
begin
Name := 'Fecha';
DataType := ftDate;
Required := true;
end;
with AddFieldDef do
begin
Name := 'Loteria';
DataType := ftString;
Size := 25;
Required := true;
end;
with AddFieldDef do
begin
Name := 'ADia';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'BDia';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'CDia';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'ZDia';
DataType := ftString;
Size := 15;
Required := false;
end;
with AddFieldDef do
begin
Name := 'ATarde';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'BTarde';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'CTarde';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'ZTarde';
DataType := ftString;
Size := 15;
Required := false;
end;
with AddFieldDef do
begin
Name := 'ANoche';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'BNoche';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'CNoche';
DataType := ftString;
Size := 5;
Required := false;
end;
with AddFieldDef do
begin
Name := 'ZNoche';
DataType := ftString;
Size := 15;
Required := false;
end;
end;
with IndexDefs do
begin clear;
with AddIndexDef do
begin
Name := 'PorId';
Fields := 'Id';
Options := [ixPrimary];
end;
with AddIndexDef do
begin
Name := 'PorFechas';
Fields := 'Fecha';
Options := [ixDescending];
end;
with AddIndexDef do
begin
Name := 'PorLoterías';
Fields := 'Loteria';
Options := [ixCaseInsensitive];
end;
end;
CreateTable;
finally
free;
end;
end;
En sí no le veo el origen del error, he tomado el RecordCount de la tabla para asignarle el autonúmerico al índice principal, cualquier sugerencia será bienvenida. Gracias.