CodeChargenewbie
Posts: 114
|
| Posted: 01/11/2008, 7:14 AM |
|
FYI, I'm using PHP.
I have a date field on one of my pages. It is of type date and in the format of mm/dd/yyyy in codecharge. This value gets stored in an sql database using ccsDate. So, it's a date through-and-through. Now, as far as retrieving it from the sql database, that is a problem. The error message I get is
"A non well formed numeric value encountered..."
FYI, I am using custom code to retreive the data, as well. The problem is it sees it as a string. Also, the format that it retrives is Month day Year Time (i.e. Jan 10 2008 12:00 AM), as opposed to 01/10/2008, which is what I specified earlier, but that is an sql issue, I believe. Anyway, I tried using a convertdate function I built with CCFormatDate and CCParseDate, which does convert it back to 1/10/2008. However, the error above occurs.
Then I attempted date("m/d/Y", strtotime($database->f("Date")))...No Luck.
Finally, date("m/d/Y", strtotime(dateconversion($database->f("Date))))...Again, no luck. (note: dateconversion is my function using ccparsedate and ccformatdate).
The ONLY thing that I can do is make the text field on my page of type Text and all three methods will work fine. The problem is I'd rather codecharge take care of ensuring the field is a date, rather than doing it myself, since that's part of its design. I want to avoid things like sql injection and things of that nature. I can conduct error checking myself, but if codecharge can handle it, why bother? The question is is there a way to take a date from a database and display it in a text box without formatting the textbox to a string type?
|
 |
 |
datadoit
|
| Posted: 01/11/2008, 7:42 AM |
|
If CCFormatDate() and CCParseDate() return the format you expect in your
textbox control, is the error a form Error (a CCS error)? or a page
error (PHP error)? If it's a PHP error, you can suppress those.
What's the database?
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/11/2008, 8:10 AM |
|
It appears to be a php error. The database is MS SQL 2000. Again, I'm doing all of this in custom code. The error occurs when I perform:
$form->control->SetValue(date value goes here);
The CCformatdate function is just one method to accomplish the goal of converting the date. The problem is unrelated to the method I choose, as far as I can tell. Converting the date and displaying it using pure php is fine. But when I sett it to the textbox, that's when I get the error. Again, I think codecharge sees the value as a string, no matter what I do, which is why I had to change the data type of the textbox to string instead of date.
|
|
|
|
datadoit
|
| Posted: 01/11/2008, 9:38 AM |
|
10-4. Let's go through some obvious things first...
1. Make certain that your connection settings specify the format that
your database is storing dates in. Make sure you match what SQL Server
expects. I know in MySQL the default is YYYY-MM-DD HH:ii:ss.
2. Look at your value under Project Settings -> Locales & Encodings ->
Date Display Format. That's how CCS will display date fields if you
don't specify otherwise. I assume you have this set to GeneralDate,
based on your original post.
3. In your textbox field, set your Control Source to your DATE field,
Data type of Date, Format (how you want it displayed - looks like
mm/dd/yyyy), and VERY IMPORTANT, the DBFormat should match what the
field definition is. If the field definition is DATE, then the DBFormat
should be yyyy-mm-dd. If it's DATETIME or TIMESTAMP, then it should
match #1 above. So many people seem to miss this.
Given those three rules, and given that your database field definition
is indeed a DATE and not TEXT, you should have no problems displaying,
reading or writing date information to that field.
You may have already gone through all this... just want to make sure.
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/14/2008, 5:55 AM |
|
Quote datadoit:
10-4. Let's go through some obvious things first...
1. Make certain that your connection settings specify the format that
your database is storing dates in. Make sure you match what SQL Server
expects. I know in MySQL the default is YYYY-MM-DD HH:ii:ss.
2. Look at your value under Project Settings -> Locales & Encodings ->
Date Display Format. That's how CCS will display date fields if you
don't specify otherwise. I assume you have this set to GeneralDate,
based on your original post.
3. In your textbox field, set your Control Source to your DATE field,
Data type of Date, Format (how you want it displayed - looks like
mm/dd/yyyy), and VERY IMPORTANT, the DBFormat should match what the
field definition is. If the field definition is DATE, then the DBFormat
should be yyyy-mm-dd. If it's DATETIME or TIMESTAMP, then it should
match #1 above. So many people seem to miss this.
Given those three rules, and given that your database field definition
is indeed a DATE and not TEXT, you should have no problems displaying,
reading or writing date information to that field.
You may have already gone through all this... just want to make sure.
Appreciate the write-up and your prompt response!
First, I want to clarify two things: 1) I am setting the textbox on the page with a data type of date and format of mm/dd/yyyy. And this is very important, 2) the date field is not only set corrrectly, but does not produce any errors at all when I'm buidling the query using codecharge's datasource; that is, it's gui query builder. HOWEVER, I am building ALL of it myself using custom PHP code, which is where the error is occuring. Now then:
1. The connection settings are, indeed, correct, especially since the date field is stored and retrived correctly sans using custom code.
2. Actually, the date format is NOT GeneralDate here but mm/dd/yyyy. So, the default format, if unspecified, is mm/dd/yyyy.
3. I'm a little confused on this one. Do you mean to say if the database field itself, as opposed to the textbox field in codecharge, has a data type of DATE, it's format in the dbFormat attribute should be yyyy-mm-dd? The database field datatype is datetime, however, and it's format that's displayed in the database is yyyy-mm-dd hh:mm:ss.[ms][ms][ms]. For example, 2000-12-17 10 11:57:30.300 may be such a value. So, am I supposed to store it as yyyy-mm-dd hh:mm:ss.[ms][ms][ms] in the dbformat attribute then?
Thank you very much for your help.
|
|
|
|
datadoit
|
| Posted: 01/14/2008, 6:14 AM |
|
CodeChargenewbie wrote:
> 3. I'm a little confused on this one. Do you mean to say if the database
> field itself, as opposed to the textbox field in codecharge, has a data type of
> DATE, it's format in the dbFormat attribute should be yyyy-mm-dd? The database
> field datatype is datetime, however, and it's format that's displayed in the
> database is yyyy-mm-dd hh:mm:ss.[ms][ms][ms]. For example, 2000-12-17 10
> 11:57:30.300 may be such a value. So, am I supposed to store it as yyyy-mm-dd
> hh:mm:ss.[ms][ms][ms] in the dbformat attribute then?
>
> ---------------------------------------
If you don't store it in the format the Db expect it, or perhaps you
omit the timestamp part, the value will get stored as if it were
midnight (2008-01-14 00:00:00).
Important: As you're formatting your date in your code, be sure to
represent minutes using 'ii' and not 'mm' (if you're using PHP).
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/15/2008, 6:48 AM |
|
Quote datadoit:
CodeChargenewbie wrote:
> 3. I'm a little confused on this one. Do you mean to say if the database
> field itself, as opposed to the textbox field in codecharge, has a data type of
> DATE, it's format in the dbFormat attribute should be yyyy-mm-dd? The database
> field datatype is datetime, however, and it's format that's displayed in the
> database is yyyy-mm-dd hh:mm:ss.[ms][ms][ms]. For example, 2000-12-17 10
> 11:57:30.300 may be such a value. So, am I supposed to store it as yyyy-mm-dd
> hh:mm:ss.[ms][ms][ms] in the dbformat attribute then?
>
> ---------------------------------------
If you don't store it in the format the Db expect it, or perhaps you
omit the timestamp part, the value will get stored as if it were
midnight (2008-01-14 00:00:00).
Important: As you're formatting your date in your code, be sure to
represent minutes using 'ii' and not 'mm' (if you're using PHP).
Thanks. Well, I correctly set the dbformat column. The thing is the field on the page is in the form of mm/dd/yyy, right, so the database will store the timestamp part as midnight, like you said. Regardless of what I do, though, codecharge is uncooperative. It seems to only be pleased if I let it handle retrieving the date, as opposed to handling it all in custom code. I wonder if the problem is codecharge interprets any php with text as strings. No matter what I do, I can't get it to display correctly, unless I set the field on the page to type text.
The thing is, in my database, how it displays the date in a table and how it actually stores it are two different things. See, the date is stored as the number of seconds( or milliseconds, I forget which) from around 1970. So, a date of 2000-12-17 10
> 11:57:30.300 is actually a very large integer. I don't think it matters in this condundrum, but it may be worth pointing out. At this point, I think i'm content with giving up and maybe sending an e-mail to to the codecharge guys, that this has to be fixed in a future release.
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/15/2008, 7:15 AM |
|
I even tried using this tip:
Quote :
Notice: A non well formed numeric value encountered in {filename} on line {17}
You may well being trying to use a string to display the date or time - such as...
$test = date("l d F Y","1974-08-02");
To do this you need to first convert the string into a timestamp using the strtotime function as per this amended example...
$test = date("l d F Y",strtotime("2007-04-02"));
http://www.error-messages.co.uk/
I copied this exact code into my php custom code and set $test to the field and I'll get an error of:
Quote :Warning: date() expects parameter 2 to be long, string given...
It seems no matter what I do, codecharge is actually interpreting it as a string. If anybody can actually get codecharge to display a date in a date textbox on the page using custom code, i'd love to hear about it.
|
|
|
|
datadoit
|
| Posted: 01/15/2008, 7:46 AM |
|
CodeChargenewbie wrote:
> It seems no matter what I do, codecharge is actually interpreting it as a
> string. If anybody can actually get codecharge to display a date in a date
> textbox on the page using custom code, i'd love to hear about it.
> ---------------------------------------
You've referred to 'custom code' several times, but you've yet to post
your custom code for validation to the forum. Perhaps an oversight? :)
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/15/2008, 9:33 AM |
|
Unfortunately, I cannot the post the EXACT code due to my employer's policy, but I can post the code I'm referring to by changing the names, sorry about that.
// Done in a before show event
$dbase = new clsDBtestdatabase();
$dbase->query("Select * From TableLeader where ID = 1");
if($dbase->next_record())
{ // Begin if
$test = date("l d F Y",strtotime("2007-04-02"));
$Form->DateControl->SetValue($test);
} // End if
unset($dbase);
|
|
|
|
datadoit
|
| Posted: 01/15/2008, 12:44 PM |
|
CodeChargenewbie wrote:
> Unfortunately, I cannot the post THE exact code due to my employer's policy, but
> I can post the code I'm referring to by changing the names, sorry about that.
>
> // Done in a before show event
>
> $dbase = new clsDBtestdatabase();
>
> $dbase->query("Select * From TableLeader where ID = 1");
>
> if($dbase->next_record())
> { // Begin if
>
> $test = date("l d F Y",strtotime("2007-04-02"));
>
> $Form->DateControl->SetValue($test);
>
> } // End if
>
> unset($dbase);
> ---------------------------------------
What you have is trying to write to a date field "Monday 02 April 2007".
That's not a date, it's text. Changing your control to a Text field
will show this value. Having it as a Date field will throw all kinds of
errors.
You just need to parse the date so that CCS knows how to format it:
$Form->DateControl->SetValue(CCParseDate("2007-04-02",array("yyyy","-","mm","-","dd")));
Then, as long as your control is defined as a Date, with whatever
display format you want, and the DbFormat specified correctly, all will
be well.
|
|
|
|
DonB
|
| Posted: 01/15/2008, 8:15 PM |
|
MS SQL is goofy when it comes to dates. I think you should specify the
column with a datatype conversion, perhaps even wrap it all up into a view
so CCS doesn't even know about it. The goal is to have it spit out the date
as yyyy-mm-dd hh:mm:ss, which CCS will digest nicely:
select CONVERT(datetime, some-column-containing-datetime, 120) from
some-table
should output the column as yyyy-mm-dd hh:mm:ss
--
DonB
http://ccswiki.gotodon.net
"CodeChargenewbie" <CodeChargenewbie@forum.codecharge> wrote in message
news:2478cee5bd177f@news.codecharge.com...
> Unfortunately, I cannot the post THE exact code due to my employer's
> policy, but
> I can post the code I'm referring to by changing the names, sorry about
> that.
>
> // Done in a before show event
>
> $dbase = new clsDBtestdatabase();
>
> $dbase->query("Select * From TableLeader where ID = 1");
>
> if($dbase->next_record())
> { // Begin if
>
> $test = date("l d F Y",strtotime("2007-04-02"));
>
> $Form->DateControl->SetValue($test);
>
> } // End if
>
> unset($dbase);
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/16/2008, 6:47 AM |
|
Thanks for the suggestions. I had tried that particular convert suggestion previously and, unfortunately, its results in php were less than satisfactory, producing the issue in question (i.e. Jan 10 2008 12:00 AM). I even tried converting it to mm/dd/yyy in sql, which php read correctly. However, setting it to the datefield produced the issue in question. The tip I posted earlier would have worked, if I didn't have to worry about an IDE like codecharge fudging it up.
The convertdate function I made didn't work:
CCFormatDate(CCParseDate($Date,array("mmm"," ","d"," ","yyyy"," ","h",":","nn"," ","AM/PM")),array("mm","/","dd","/","yyyy"))
// The date arrives from sql as Jan 10 blah blah blah. Then i plug it in convertdate and spit it ////out to $test. So, at this point, it's 01/10/2008.
Then I tried the other suggestion above:
$Form->DateControl->SetValue(CCParseDate($test, array("mm","/","dd","/","yyyy")));
and it works! Interestingly enough, I didn't have to set the dbformat to make it work. It had no effect on it one way or the other. Why is that?
Thank you all again.
|
|
|
|
datadoit
|
| Posted: 01/16/2008, 7:31 AM |
|
CodeChargenewbie wrote:
> Then I tried the other suggestion above:
> $Form->DateControl->SetValue(CCParseDate($test,
> array("mm","/","dd","/","yyyy")));
>
> and it works! Interestingly enough, I didn't have to set the dbformat to make
> it work. It had no effect on it one way or the other. Why is that?
>
> Thank you all again.
> ---------------------------------------
If you format the date in your code, then CCS will attempt to format it
again based on your display property settings for the control. So it's
formatting an already formatted date. or so I believe. :)
Try not to be so quick in declaring that CCS is the one 'fudging it up'.
There are several layers involved here, which include the database
engine itself and how it manages dates, and the DSN/ODBC layer and how
it manages dates. The mere fact that the CCParseDate() function exists,
to me, means that CCS is providing a mechanism to fix the way dates are
retrieved from various database engines.
or so I believe. :)
|
|
|
|
CodeChargenewbie
Posts: 114
|
| Posted: 01/16/2008, 2:34 PM |
|
Quote datadoit:
CodeChargenewbie wrote:
> Then I tried the other suggestion above:
> $Form->DateControl->SetValue(CCParseDate($test,
> array("mm","/","dd","/","yyyy")));
>
> and it works! Interestingly enough, I didn't have to set the dbformat to make
> it work. It had no effect on it one way or the other. Why is that?
>
> Thank you all again.
> ---------------------------------------
If you format the date in your code, then CCS will attempt to format it
again based on your display property settings for the control. So it's
formatting an already formatted date. or so I believe. :)
Try not to be so quick in declaring that CCS is the one 'fudging it up'.
There are several layers involved here, which include the database
engine itself and how it manages dates, and the DSN/ODBC layer and how
it manages dates. The mere fact that the CCParseDate() function exists,
to me, means that CCS is providing a mechanism to fix the way dates are
retrieved from various database engines.
or so I believe. :)
Quote Codecharge manual wrote:This property specifies the format of the data as it appears in the database. The DBFormat property determines the format that will be used to extract as well as place the control value into the database. Some databases such as MySQL and PostgreSQL are particular about the format used so this property should be set to correspond to the format of the value as contained within the database.
http://docs.codecharge.com/studio3/html/Components/Prop...Format.html?toc
I'm supposing MSSQL is not one of those databases who are stingy about this sort of thing then...
Yes, you're right, it's easy to blame codecharge for everything :) It's a good defense against my obvious ineptitude at coding.
|
|
|
|
|
