john_mocambique
Posts: 3
|
| Posted: 07/15/2008, 1:18 PM |
|
I am using CC4 (ASP .NET), and MySql4.1.
I have created a field in MySQL that is a DATETIME type.
Then I used a grid-record builder to create a ASP .NET page with a search option that includes the DATETIME field.
When the builder is complete everything works except searching the DATETIME field. I changed the WHERE critieria to be LIKE instead of '=' but this still doesn't result in a the ability to search.
For example:
The DATETIME field has a value of 2008-07-15 14:11:56. I then try to search for '2008-07-15'. What I get is a error message that states 'Error in Grid repairs: Invalid parameter'.
Is CC changing the search value into something that ASP or MySQL doesn't recognize?
Or is there something else I'm missing?
|
 |
 |
john_mocambique
Posts: 3
|
| Posted: 07/15/2008, 1:19 PM |
|
Further details:
In the past I have had a similar issue with PHP and to workaround the issue I changed the DATETIME fields to VARCHAR and then the search works fine. 
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 07/15/2008, 6:35 PM |
|
One idea is to change upto MySQL 5, which has some very nice features like views.
I often use views for my search/grid sources as it makes it easier to create descriptive fields and joins.
Also with MySQL 5 you can create your own functions so you can do some specuial thigns like restrict the length of a description to 20 chars. if it is more that 20 chars then change the last 3 characters to '...'
Also the date formatting of MySQL is very powerful so you can easily make a sortable date field and a descriptive date field.
Hope this helps
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 07/16/2008, 8:33 AM |
|
Hi
I do not know if this will be helpful to you but try taking a look at this post. It sort of describes how CCS deals with dates in PHP and I might assume it does somthing similar for ASP.
http://forums.yessoftware.com/posts.php?post_id=97052
Let me know if that helps your situation.
Have fun
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
john_mocambique
Posts: 3
|
| Posted: 07/17/2008, 7:45 AM |
|
Thanks for the advice John and Dave.
Your information confirms what I suspected - Codecharge doesn't allow for searching of date values.
For example:
I enter in 2008-07-17 in order to show all DATETIMEs of 2008-07-17* but because the date time is stored in a numerical instead of text value the search parameter is incorrect.
To fix the problem for now I simply removed the date search boxes from my project pages and will instead rely on using the column headers in the grids for sorting ascending/descending.
|
|
|
|
datadoit
|
| Posted: 07/17/2008, 9:58 AM |
|
> Your information confirms what I suspected - Codecharge doesn't allow for
> searching of date values.
>
---------------------------
But it does... and without a single bit of coding. Please follow these
steps:
1. You specify that your date field in MySQL is of type DATETIME. That
means that MySQL by default is storing your date in the format of
yyyy-mm-dd HH:nn:ss. Verify this by going to your Project Settings
(Alt-F7), then going to Connections, then choosing your connection and
Modify..., go to the Server tab and make certain that the Date Format is
correct. This is provided that you haven't changed the default in MySQL
itself! Look at the MySQL System Variable datetime_format. It should
be %Y-%m-%d %H:%i:%s.
2. In your search form, in your date field, make sure you have the
following Properties -> Data values:
Data Type: Date
Format: However you want the date to 'display' on the screen in the form
field.
DBFormat: yyyy-mm-dd HH:nn:ss
3. In your results grid or record form Data Source WHERE Table
Parameter, you should have:
Field Name: Your date field chosen
Condition: greater than or equal to
Parameter: The name of your search form date field
Field Type: Date
Parameter Type: URL
Field Format: yyyy-mm-dd
Parameter Format: The 'display' format you chose above in #2 Format
I think your problem is in the 'Condition' part. You want all records
with a value greater than or equal to the date specified. This means
that 2008-07-17 00:00:01 is greater than 2008-07-17, or 2008-07-17
00:00:00. If you don't specify hours minutes and seconds in your search
value, then it will default to 00:00:00.
Secondly, since you're only searching by date and not by the hours,
minutes and seconds, then the Field Format needs to be proper - omit the
HH:nn:ss portion of the chosen format.
Enjoy.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 07/18/2008, 2:25 AM |
|
DD is right.
Thanks for descriptive post.
Walter
Postnote:
If searching on DATETIME fields does not work for you then changing them to VARCHAR is not a wise move, changing to DATE would be the obvious chose, based on 'do I need time'.
Searching on DATE fields, manipulating DATE fields is catered for in MySQL (4 as wel as 5, 5 has more and better).
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
