VC + + Access about Time field solutions

Introduction

As we all know, VC + + is a very powerful application development tool, while Microsoft Access is a small and medium database management system that can handle a variety of data information. Therefore, when using VC + + links to small and medium-sized databases of ODBC, access is often selected as a database source. In the practical application, VC + + and Access's digital type data (such as plastic, floating-point and double precision) when the link, easy to use, but in VC + + and Access database Date/Time type data exchange, often appear some unexpected problems. For example: in VC + + in a record, VC + + will automatically record the Time field plus date; Cannot use common SQL statements to find the date and Time fields of a record at the same time, and so on. This kind of problem is caused by the limitation of VC + + on the definition of date/time type variable.

Below we have a brief description of the type of VC + + and access date/time, explore the causes, and illustrate the solution.

　　 data interchange Methods for VC + + and access date/time types

The Visual C + + datetime control (date Time Picker) consists of a static control plus a calendar control that allows the user to select a date or to allow the user to select the time to enter. Its three forms are as follows:

(1) Short date display date (format 9/10/1970)

(2) Long date displays the day (format September 10,1970)

(3) Time displayed (format 8:00:00)

Microsoft Access provides 7 formats for date/Time data: Common dates such as 9/19/97 14:32:10 PM, full dates such as Wednesday,february 13,1997, medium dates such as 19-jun-94, short dates such as 6/19/ 97. Complete time such as 05:34:10pm, medium time such as 05:34, short time as 17:34. This shows that Microsoft Access's date/Time fields can be grouped into two broad categories except for the common date: the Date field and the Time field.

When we compile an ODBC database, we can match the VC + + attribute to the short date (long date) date control with the Date field in Access, the date-time control in VC + + property to time, and times field one by one in access. It is convenient to realize the data reading and other operations.

But then the problem came up. We often need to add a record to the database in VC + +, when adding a new record (such as: Date 9/19/99, Time 18:30:00), open Access database discovery, in the newly added record, the Time field is displayed as: 70-1-1 18:30:00, which means VC + + Automatically adds a date to the Time field for each record added-January 1, 70. This is the future of our program design brings hidden dangers.

The reason is that VC + + will all of the CTime type variables into seconds to calculate, it will be January 1, 70 0:00:00 defined as 0 time, and then all the dates are all converted to the number of seconds from January 1, 70 0:00:00. Therefore, it is not difficult to understand that VC will automatically add the Time field year date. But then if we in VC + + SQL statements to the time (such as: 18:30:00) to find, then we should find out the current time (1999-9-9 18:30:00), or check 70-1-1 18:30:00? , and how to find if we want to find a record that meets the 30 minutes before and after September 19, 99, 15 days before and after the 18:30:00. Here we discuss how to solve this problem.

an amendment to the time problem

Now, we take an example of a real database of communication between ships to illustrate how to overcome this problem in the application. In this program, we compare the difference between the number type variable and date/Time type variable in VC + + and Access data exchange, and put forward the corresponding solution to the problem of time type variable.

This program needs to implement two features simultaneously:

(1) The time range to look for: the T-minute before and after a given time in a day, the total before and after N-day;

(2) The location range of the lookup: a point to all points within the radius of the given a-center R. And all points in the radius range with the given B as center R as Point B, and two-point association (both sides of the communication).

This example involves a block diagram as shown in Figure 1:


Figure 1 The time to find dialog

The corresponding variables for each edit control are: M_date,m_time,m_ longitude_a,m_longitude_b,m_ Latitude_a,m_latitude_b,m_dd,m_mm,m_rad. The CPP source program involved in

is as follows:

m_date1=m_date; M_date2=m_date; m_date1-=m_dd*86400;//Every day is 86,400 seconds m_date2+=m_dd*86400; M_time1 = m_time; M_time2 = m_time; M_time1-=m_mm*60//60 seconds per minute m_time2+=m_mm*60 //lookup with SQL statements, attention to processing of time fields Sqlstr. Format ((Year Day) >#%d/%d/%d# and (month) <#%d/%d/%d#) and\ ((time) > #1/1/70%d:%d:%d# and (cent) (#1/1/70%d:%d:%d#) and\ ( A-point longitude) <%f and (A-point longitude) >%f and (a point of latitude) <%f and\ (point latitude) >%f and (b-point longitude) <%f and (b-point longitude) >%f and\ (b-point latitude) <%f (b-point latitude) >%f) " br> m_date1.getmonth (), M_date1.getday (), M_date1.getyear (), M_date2.getmonth (), M_date2.getday (), m_ Date2.getyear (), M_time1.gethour (), M_time1.getminute (), M_time1.getsecond (), M_time2.gethour (), m_ Time2.getminute (), M_time2.getsecond (), M_longitude_a+m_rad,m_longitude_a-m_rad,m_latitude_a+m_rad, M_ Latitude_a-m_rad,m_longitude_b+m_rad,m_longitude_b-m_rad, M_latitude_b+m_rad,m_latitude_b-m_rad); M_smartset.m_strfilter=sqlstr m_smartset.m_strsort= "score DESC";//descending order

In the SQL statement of the program, we add the January 1, 70 (i.e. 70/1/1) to the lookup of each time field, and the standard SQL statement for the lookup of numeric type variables is OK. Why add 70/1/1 to it. Because as mentioned earlier, in the main interface, when we add a record, VC automatically adds the date for the record time field is 70/1/1. In this way, the Time field in the SQL statement artificially inserted date not only overcomes the time problem of VC, but also the search of other fields have no effect, this solution is proved to be completely correct and feasible by the programming experiment.

Finally, with the CListBox class AddString () and other member functions, you can find the record in the list box one by one display, look up the record as shown in Figure 2, here we only display a few simulated records in the list box. The sort in Figure 2 is sorted in descending order by the size of the score.


Figure 2 Finding record results

　　 Concluding remarks

This paper provides a simple and practical method, which fixes the problem that VC + + has with the Access database date/Time type variable data exchange. The use of VC + + and other database links can use similar ideas.