DateTime problem in MSQRY with params
Posted: Wed 20 May 2009 08:04
I've a MS-SQL Server Database with a DateTime field and i will select datasets in a range of this DateTime field.
The select command with params is:
SELECT PP_PAB_REF.NLFDPABNR AS Auftragsnr, PP_PAB_REF.NLFDPPLNR AS Pruefplannr, ARTIKEL.SARTIKELNR, ARTIKEL.SARTIKELBEZ AS Artikelbezeichnung,
SPCIDREF.NSPCIDNR AS Merkmal, SPE_VAR.DTTSERF, SPE_VAR.DMW AS Messwert, PP_PAB.NLFDMMNR AS Merkmalsnummer,
MM_KOPF.SMMBEZ AS Merkmalsbezeichnung
FROM SPE_VAR INNER JOIN
PP_PAB_REF INNER JOIN
SPCIDREF ON PP_PAB_REF.NLFDPABNR = SPCIDREF.NLFDPABNR ON SPE_VAR.NSPCIDNR = SPCIDREF.NSPCIDNR INNER JOIN
MM_KOPF INNER JOIN
PP_PAB ON MM_KOPF.NLFDMMNR = PP_PAB.NLFDMMNR ON SPCIDREF.NLFDPABNR = PP_PAB.NLFDPABNR INNER JOIN
ARTIKEL ON SPCIDREF.NLFDARTIKELNR = ARTIKEL.NLFDARTIKELNR
GROUP BY PP_PAB_REF.NLFDPABNR, PP_PAB_REF.NLFDPPLNR, ARTIKEL.SARTIKELBEZ, SPCIDREF.NLFDARTIKELNR, SPCIDREF.NSPCIDNR,
SPE_VAR.DTTSERF, SPE_VAR.DMW, PP_PAB.NLFDMMNR, MM_KOPF.SMMBEZ, ARTIKEL.SARTIKELNR
HAVING (PP_PAB_REF.NLFDPPLNR = 3987001 OR
PP_PAB_REF.NLFDPPLNR = 3986001) AND (SPCIDREF.NLFDARTIKELNR is not NULL) AND (SPE_VAR.DTTSERF > :s1) AND (SPE_VAR.DTTSERF < :s2)
ORDER BY SPE_VAR.DTTSERF
the parameter are s1 and s2
With the MSQuery Editor component (Sdac) in Delphi and the paramtername s1 and s2 (DateTime) and a value like 23.04.2008 23:59:59 it works very fine and there is no problem!
Know in the program I will click a button in delphi and send it over params like that:
procedure TForm1.Button2Click(Sender: TObject);
begin
MSQryMerkmaleSPEVAR.Close ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared ;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := '27.04.2009 23:59:00' ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := '28.04.2009 00:00:00' ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end ;
Delphi returns an error:
EVariantTYpeCastError: Could not convert variant of type (String) into type double.
Why double????
The same problem with:
procedure TForm1.Button2Click(Sender: TObject);
var
werta, wertb: TDateTime ;
begin
werta := StrToDateTime(cxDateEdit1.Text) ;
wertb := StrToDateTime(cxDateEdit2.text) ;
werta := werta - 1 ;
wertb := wertb + 1 ;
MSQryMerkmaleSPEVAR.Close ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared ;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := werta ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := wertb ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end;
What can I do?
Delphi7 with sdac 4.70.0.46
The select command with params is:
SELECT PP_PAB_REF.NLFDPABNR AS Auftragsnr, PP_PAB_REF.NLFDPPLNR AS Pruefplannr, ARTIKEL.SARTIKELNR, ARTIKEL.SARTIKELBEZ AS Artikelbezeichnung,
SPCIDREF.NSPCIDNR AS Merkmal, SPE_VAR.DTTSERF, SPE_VAR.DMW AS Messwert, PP_PAB.NLFDMMNR AS Merkmalsnummer,
MM_KOPF.SMMBEZ AS Merkmalsbezeichnung
FROM SPE_VAR INNER JOIN
PP_PAB_REF INNER JOIN
SPCIDREF ON PP_PAB_REF.NLFDPABNR = SPCIDREF.NLFDPABNR ON SPE_VAR.NSPCIDNR = SPCIDREF.NSPCIDNR INNER JOIN
MM_KOPF INNER JOIN
PP_PAB ON MM_KOPF.NLFDMMNR = PP_PAB.NLFDMMNR ON SPCIDREF.NLFDPABNR = PP_PAB.NLFDPABNR INNER JOIN
ARTIKEL ON SPCIDREF.NLFDARTIKELNR = ARTIKEL.NLFDARTIKELNR
GROUP BY PP_PAB_REF.NLFDPABNR, PP_PAB_REF.NLFDPPLNR, ARTIKEL.SARTIKELBEZ, SPCIDREF.NLFDARTIKELNR, SPCIDREF.NSPCIDNR,
SPE_VAR.DTTSERF, SPE_VAR.DMW, PP_PAB.NLFDMMNR, MM_KOPF.SMMBEZ, ARTIKEL.SARTIKELNR
HAVING (PP_PAB_REF.NLFDPPLNR = 3987001 OR
PP_PAB_REF.NLFDPPLNR = 3986001) AND (SPCIDREF.NLFDARTIKELNR is not NULL) AND (SPE_VAR.DTTSERF > :s1) AND (SPE_VAR.DTTSERF < :s2)
ORDER BY SPE_VAR.DTTSERF
the parameter are s1 and s2
With the MSQuery Editor component (Sdac) in Delphi and the paramtername s1 and s2 (DateTime) and a value like 23.04.2008 23:59:59 it works very fine and there is no problem!
Know in the program I will click a button in delphi and send it over params like that:
procedure TForm1.Button2Click(Sender: TObject);
begin
MSQryMerkmaleSPEVAR.Close ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared ;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := '27.04.2009 23:59:00' ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := '28.04.2009 00:00:00' ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end ;
Delphi returns an error:
EVariantTYpeCastError: Could not convert variant of type (String) into type double.
Why double????
The same problem with:
procedure TForm1.Button2Click(Sender: TObject);
var
werta, wertb: TDateTime ;
begin
werta := StrToDateTime(cxDateEdit1.Text) ;
wertb := StrToDateTime(cxDateEdit2.text) ;
werta := werta - 1 ;
wertb := wertb + 1 ;
MSQryMerkmaleSPEVAR.Close ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared ;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := werta ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := wertb ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end;
What can I do?
Delphi7 with sdac 4.70.0.46