Problem with ADOCommand inserting null into datetime field

Giganews Newsgroups
Subject: Problem with ADOCommand inserting null into datetime field
Posted by:  Niall Mulcahy
Date: Fri, 9 Jul 2010

Hi all

I hope I am posting this in the correct area, if not please accept my apologies.

I am new to Delphi and would appreciate any help.

The following is a simplified version of my problem.

I am trying to parse a text file and insert it into MyTable in MS SQL Server 2000

MyTable has 3 fields

    Member      Char 6
    Join_date    DateTime
    Leave_date DateTime  (Allow Nulls)

The Text File has three fields

Field 1  Pos 1 - 6      Member_id (Mandatory)
Field 2  Pos 7 - 17    Joining Date DD-MMM-YYYY (Mandatory)
Field 3  Pos 18 - 28  Leaving Date DD-MMM-YYY( Non Mandatory)

Everything is fine when there is data for all three fields, however I am having problems when there is no data for Leaving Date

Below is how I am trying to handle it

{code}
var
member_id : string;
join_date, leave_date : dateTime;

adoCommand1.commandText :=
'Insert into myTable Values( :p1, :p2, :p3 )

assignFile(TxtFile, 'c:\SRFC\ftpdownload\upload.txt');
reset(TxtFile);
while not eof( TxtFile )
do
begin
readLn(TxTFile, LineToProcess);
if isvalidRecord( LineToProcess ) then

begin
member_id := copy(LineToProcess,1,6);
Join_date := dateTimeStrEval('DD-MMM-YYYY', copy(nmLine,7,11));
if trim(copy(nmLine,7,11)) = '' then leave_date = null else leave_date := dateTimeStrEval('DD-MMM-YYYY', copy(nmLine,7,11));
end;
adoCommand1.Parameters[0].value := member_id;
adoCommand1.Parameters[1].value := join_date;
adoCommand1.Parameters[2].value := leave_date;
adoCommand1.execute;
end;

{code}

Any help greatly appreciated

Replies