DateTime problem in MSQRY with params

hauiclaudi · Post by **hauiclaudi** » 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

Dimon · Post by **Dimon** » Thu 21 May 2009 13:15

The point is that if parameter type is set to variant, SQL Server returns parameter value as OleString.
To solve this problem you should specify parameters data type before calling the Prepare method.

Code: Select all

MSQuery.ParamByName('ParamName').DataType := ftDateTime; 
MSQuery.Prepare;

hauiclaudi · Post by **hauiclaudi** » Mon 25 May 2009 07:08

The same problem:

EvarianOverflowError with message: Overflow while converting variant of type (OleStr) into type (Double)

procedure TForm1.Button2Click(Sender: TObject);
var
werta, wertb: TDateTime ;
begin
werta := (cxDateEdit1.InternalEditValue) ;
wertb := (cxDateEdit2.InternalEditValue) ;
werta := werta - 1 ;
wertb := wertb + 1 ;
MSQryMerkmaleSPEVAR.Close ;
MSQryMerkmaleSPEVAR.ParamByName('s1').DataType:= ftDateTime ;
MSQryMerkmaleSPEVAR.ParamByName('s2').DataType:= ftDateTime ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := werta ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := wertb ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end;

Dimon · Post by **Dimon** » Mon 25 May 2009 07:23

hauiclaudi wrote:procedure TForm1.Button2Click(Sender: TObject);
var
werta, wertb: TDateTime ;
begin
werta := (cxDateEdit1.InternalEditValue) ;
wertb := (cxDateEdit2.InternalEditValue) ;
werta := werta - 1 ;
wertb := wertb + 1 ;
MSQryMerkmaleSPEVAR.Close ;
MSQryMerkmaleSPEVAR.ParamByName('s1').DataType:= ftDateTime ;
MSQryMerkmaleSPEVAR.ParamByName('s2').DataType:= ftDateTime ;
if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared;
MSQryMerkmaleSPEVAR.ParamByName('s1').value := werta ;
MSQryMerkmaleSPEVAR.ParamByName('s2').value := wertb ;
MSQryMerkmaleSPEVAR.Execute ;
MSQryMerkmaleSPEVAR.open ;
end;

You should use the following code:

Code: Select all

if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepare;

instead of

Code: Select all

if not MSQryMerkmaleSPEVAR.Prepared then MSQryMerkmaleSPEVAR.Prepared;

hauiclaudi · Post by **hauiclaudi** » Mon 25 May 2009 09:57

oh that's it!
Thanks, great job

Devart Forums