Solved

handling invalid dates from db using dbeditbox

Posted on 2006-07-13
15
280 Views
Last Modified: 2010-04-04
I have a Dbase IV DB with dates stored as MM/DD/YY many of the dates in that db are NOT valid for example 00/00/00.  I have a form that connects to the DB with BDE and uses DBeditboxes to display/edit the data in the database.  

The problem is when there is no date, the date is being stored as 00/00/00 in my db (I think this is the case it's what I see when I look at the record in dbase.exe although it doesn't come out like that when I do a query on it through BDE).  My DBEditBox when this situation occurs (00/00/00) will display what appears to be a random date (as do my queries), also a strange side effect is when you click to enter the edit box the date somewhat randomly changes for no reason apparent to me.

Any advice on how to handle an unentered/invalid date, what I'm looking for is if the date is stored as 00/00/00 in the db display it as such or display nothing.  When there is an invalid date such as 99/99/99 in my db, my dbEditBox actually displays 0/0/0000 for the date which is what I'm looking to do when there is no date entered yet.  
0
Comment
Question by:ICPooreman
  • 8
  • 7
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17099403
see the onSetText/onGetText-events of the underlying TField of your Dataset, which is bound through a Datasource to your tdbedit

in this events you can do checks and translations of the stored/to store
datas

meikl ;-)
0
 

Author Comment

by:ICPooreman
ID: 17100344
Thanks for that, that's pretty useful I never knew those events existed.  One question about it though now when I enter the DBEditBox to edit, the editbox clears the text inside any idea on how to keep that from happening?

Although that's a great place to check for my error I guess I'm looking more for ideas as to how to check for it.

procedure TForm1.Query1SCANDATEGetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
   if DisplayText then
   begin
     //I want to add an if statement here checking if my date is valid
    // What's confusing to me is that Sender.AsString is returning a semi-random date which is different from 00/00/00 and I'm not sure why
     Text := Sender.AsString; //This produces the same problem as I stated above I get a random date in Text
   end;
end;

0
 

Author Comment

by:ICPooreman
ID: 17101035
I think I found part of my answer here

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_10180987.html

ok so I figured out that the values in my db are actually stored as I thought as 00/00/00.  
I created a new not-active query and a button to test.

The first time I click the button and run the sample code below I get the message "field is null" and the date "12/30/1899" however if I click the button a second time it doesn't say the field is null and I get the date 6/6/0180.  Somewhere after the first time I run the query my field is being populated with a garbage value.  


Procedure SetSql( SQLQ : TQuery; sqltxt : string; count : integer; paras  : array of variant);
var
i : integer;
Begin
  SQLQ.SQL.Clear;
  SQLQ.SQL.Text := sqltxt;
  SQLQ.Prepare;
  for i:=0 to count-1 do
    begin
      SQLQ.ParamByName('S'+inttostr(i)).Value := paras[i];
    end;

   SQLQ.RequestLive := true;
   SQLQ.Active := true;
   SQLQ.Open; {open query + display data}
end;


procedure TForm1.TestButtonClick(Sender: TObject);
begin
               SetSql(Query2, 'SELECT * FROM document.dbf WHERE DAN =:S0'
                    ,1,[DAN.Text]);
                   //note DAN.Text field is the same every time I hit the button
                    if(Query2.FieldByName('SCANDATE').isNull) then
                            ShowMessage('field is null');

                      ShowMessage(DateTimeToStr(Query2.FieldByName('SCANDATE').AsDateTime) + ' ScanDate');
end;


0
 

Author Comment

by:ICPooreman
ID: 17101169
I think I found where my problem is occuring.
If I set the RequestLive to False at the beginning of my Query like below then I get the right value of 12/30/99 every time.  However if I do that then I can no longer edit my DBeditBoxes.    

Procedure SetSql( SQLQ : TQuery; sqltxt : string; count : integer; paras  : array of variant);
var
i : integer;
Begin
  SQLQ.RequestLive := false;
  SQLQ.SQL.Clear;
  SQLQ.SQL.Text := sqltxt;
  SQLQ.Prepare;
  for i:=0 to count-1 do
    begin
      SQLQ.ParamByName('S'+inttostr(i)).Value := paras[i];
    end;

   SQLQ.RequestLive := true;
   SQLQ.Active := true;
   SQLQ.Open; {open query + display data}
end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17101655
sorry, my time is very limited at last time,
i wish i could you give more detailed advice

what datatype is your field in your dbase-table?

meikl ;-)
0
 

Author Comment

by:ICPooreman
ID: 17101954
The type is Date in dbase

or ftDate according to Delphi
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17102153
well, in this case you can also influence the visualization by setting the shortdateformat in your application at app-start,
like
shortdateformat := 'MM/DD/YY';


so the onSetText/onGetText could handle only NULL-values

like

procedure TForm1.Table1Common_NameGetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
  if sender.isNull then text := ''
  else text := DateToStr(sender.AsDateTime);
end;

procedure TForm1.Table1Common_NameSetText(Sender: TField;
  const Text: String);
begin
  if text = '' then sender.clear;
end;

about your courious datevalues i have no idea what's the cause

meikl ;-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ICPooreman
ID: 17102557
changing the date format just gives me a random date in a different format.

I've tried what you have above in the getText and SetText procedures, it isn't working for me because by the time they're called (if RequestLive is true) then the strange random dates have already been set for the fields so the value is never null.  

Do you think it makes any difference that I'm using a straight Query and not a table to access the database?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17106471
courious,

if you will,
you can send me your dbase-file or an extraction or sample.

you will find my eMail-adress in my profile -> just click on my name

meikl ;-)
0
 

Author Comment

by:ICPooreman
ID: 17107990
Ok I emailed you a dbase file with most of the records taken out but it should still give an idea of what my db looks like.  Thanks for attempting to help with this.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17119784
sorry for delay, ICPoorman,

after trying to open the db you sent, i got the error "Value out of bounds" for all dates,
which are not NULL (i tried delphi, SQL-Explorer and Database-Desktop -> all BDE Connection)).

so it seems these dates are corrupted.

Maybe you could tell about the history of the table and how you are connecting to it?

meikl ; -)
0
 

Author Comment

by:ICPooreman
ID: 17121601
Well thanks a lot for looking at it.  As far as the history of the table I'm not really sure someone else put it together and I haven't been able to contact them for the past week or so.  However I will see them again in the near future although I'm not really sure what to ask him.  Since I've been playing with it I've done nothing to it except delete a bunch of the records before sending it to you.  

I've been accessing the table with BDE and a straight query in Delphi.  Everything appeared to be working but the dates.  You're right though I get the same error when I try to access the DB through other means.  I got this before too but just assumed I wasn't doing it right (I'm new to Delphi, Dbase, etc...).

So would you say my problem is more than likely the db?  Also what should I find out about the history of the table?
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
ID: 17121721
>So would you say my problem is more than likely the db?  
about the courios dates -> is a problem of the dbase-file, you did nothing wrong

>Also what should I find out about the history of the table?
with which tool was it created (clipper)?
with what for a logic are the dates are inserted?

well, as far as i see, you have to do a correction on the date-fields,
so that they are as usual conform. i will try to get the logic from the values,
when got, then a small correction-app could applied on the dbase-file.

meikl ;-)
0
 

Author Comment

by:ICPooreman
ID: 17130317
Thanks for your help I rebuilt the table and have things running alright now.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17130558
glad you got it work :-))

and so easy, guessed some more doing to keep the data allright

meikl ;-)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now