Solved

handling invalid dates from db using dbeditbox

Posted on 2006-07-13
15
284 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

765 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