SQL blank date

[JoeNg]

I have a SQL that inserts values into a Paradox table.
The SQL executes without any problems if the date value is not empty. However, the SQL would not execute properly if the date value is empty.

How can I get around this error?

Thanks

For example:

sDataDate : String;
sAcctNo : String;
sOrderNo : String;

sQStr := 'Insert Into "C:\MyTable.db" '+
'(DataDate, AcctNO, OrderNo)'+
'Values
'("'+sDataDate+'","'+sAcctNo+'","'+sOrderNo+'") ';

 

[hilfy]

Have you tried using a parameterized query? Your SQL would look like this:

Insert Into "C:\MyTable.db"
(DataDate, AcctNO, OrderNo)
Values
DATADATE,:ACCTNO,:ORDERNO)

If you can use static SQL, i.e., use the same SQL, just changing the parameter values, then you'll set the parameter information in the Object Inspector (the query should automatically pick up the params - anything preceded by ':'). Double-click on Params for the query and set the DataType (it DOES NOT have to all be strings!!!) and the ParamType (ptInput) for each of your parameters. Then, when you're ready to run the query you'll do something like the following:

MyQuery.ParamByName('DATADATE').AsDateTime := dtDate;
MyQuery.ParamByName('ACCTNO').AsString := sAcctNo;
MyQuery.ParamByName('ORDERNO').AsInteger := nOrder;
MyQuery.ExecSQL;

One of the advantages to this when you're doing multiple inserts is that the query only has to be "prepared" once.

If you must set the SQL at runtime, you'll set it using the same syntax that I've shown you above and then you'll set the DataType and ParamType of the parameters in code like this:

MyQuery.ParamByName('DATADATE').DataType := ftDateTime;
MyQuery.ParamByName('DATADATE').ParamType := ptInput;

You can then set the value and execute the query.

-D

 

[JoeNg]

Thanks for your reply.

The issue is when dtDate is empty, the DataDate will end up with 12/30/1899.

How can I assign a blank value to DataDate when dtDate is empty?

Thanks

 

[towerbase]

JoeNg, A date of zero will convert to a string of '12/30/1899'. A date of zero is not the same as an "empty date".

You could code your INSERT statement something like this

if NoDateAvailable then
 sQStr := 'Insert Into "C:\MyTable.db" '+
     '(AcctNO, OrderNo)'+
     'Values ("'+sAcctNo+'","'+sOrderNo+'") '
else
 sQStr := 'Insert Into "C:\MyTable.db" '+
     '(DataDate, AcctNO, OrderNo)'+
     'Values ("'+sDataDate+'","'+sAcctNo+'","'+sOderNo+'") ';

I recommend that you use paramatized queries as suggested by hilfy. The code should then be both faster to run and easier to maintain.



Andrew
Hampshire, UK

 

[indrahig]

Hi,
Hope this will help..
I usually use this code on SQL Server, I never try it on Paradox.
Your code will be like this:


aDt: TDateTime; //this is the date value before goes into
//sDataDate string variable
sDataDate : String;
sAcctNo : String;
sOrderNo : String;

if aDt = 0 then
sDataDate:= 'Null'
else
sDataDate:= QuotedStr(FormatDateTime('yyyymmdd', aDt));

sQStr := 'Insert Into "C:\MyTable.db" '+
'(DataDate, AcctNO, OrderNo)'+
'Values
'('+sDataDate+',"'+sAcctNo+'","'+sOrderNo+'") ';

thanks

 

[towerbase]

indrahig,

Paradox requires that the date is in 'mm/dd/yyyy' format so your example won't work.

Also if the date supplied was actually 30 December 1899 your code would replace it with a NULL.



Andrew
Hampshire, UK

 

[indrahig]

towerbase,

sorry, I know that example may not work when using Paradox.. just to give idea..
Hilfy and towerbase suggestion is good.. And I preferred Hilfy suggestion (no heart feeling)..

let me correct my code, although it may not be the best suggestion, but just give alternatif..

sDataDate : String;
sAcctNo : String;
sOrderNo : String;

if sDataDate = '' then //assuming that if date value is
//empty it will return empty string
sDataDate:= 'Null'
else
sDataDate:= QuotedStr(FormatDateTime('dd/mm/yyyy', aDt));

sQStr := 'Insert Into "C:\MyTable.db" '+
'(DataDate, AcctNO, OrderNo)'+
'Values
'('+sDataDate+',"'+sAcctNo+'","'+sOrderNo+'") ';

 

[JoeNg]

Thank you everyone for your suggestions. I am going give this another try.