Chris__T
Posts: 339
|
| Posted: 03/11/2008, 12:16 PM |
|
Trying to capture the search criteria ( a date) and see if it matches a condition (equal to something already in the table) so I can let the user know they can't add info for that particular date.
The search record is on its own page. You stick the date in the textbox, hit the button and it takes you to the data entry page for that date.
But if you go back to the first page and put in the same date, it goes to the dataentry page and you will be able to add this date again into the database. I don't want duplicate dates
So on the first page, when the date is entered and the button was pressed, I was going to have some code that looks in the table (select statement) and see if the date is in there. If it is, then redirect user to a page telling them to select a different date. Else, let the user go to the data entry page.
I can't seem to pull the date out of the search box and assign it a variable and compare it to some kind of select query (or dlookup?)
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 03/11/2008, 4:24 PM |
|
Nice one.
I am thinking a DLookUp on the OnValidate of the datefield in the Search.
Or its counterpart CCDLookUp() in the code part of the onValidate.
It would then trigger the normal error handling.
However I have never done this before but will give it a try 4U.
This is written as I read yr post, back of my head says something like no datasource in Search Form might be a problem
I'll be back.
Walter
_________________
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
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/12/2008, 6:58 AM |
|
Thanks. I'll try the Dlookups and CCDLookups and see how that turns out.
I tried a Dlookup but it gave me a blank page. By what you posted it seems I put the code in the wrong server event.
|
|
|
|
wkempees
|
| Posted: 03/13/2008, 9:14 AM |
|
Did a little experimenting.
Search field datefield, set as shortdate
onValidate added Customcode
/ -------------------------
// Write your own code here.
// -------------------------
global $DBInternetDB;
$ccs_result = CCDLookUp('date_start', 'events',
'date_start='.$DBInternetDB->ToSQL($Container->s_date_start->GetValue(),ccsDate),
$DBInternetDB);
echo 'result'. $css_result;
if ($ccs_result){
$Container->s_date_start->Errors->addError("Date already booked");
}
///End Custom Code
This was done on a gridbuilder using the events table from internet
exampledatabase.
Pittfal: carefuly check your dateformat settings, you may have to change the
code to:
$ccs_result = CCDLookUp('date_start', 'events', 'date_start LIKE
'.$DBInternetDB->ToSQL($Container->s_date_start->GetValue(),ccsDate). '%',
$DBInternetDB);
To cater for the datetimeformat, stripping of the time.
Oh well you'll manage.
Walter
"Chris__T" <Chris__T@forum.codecharge> schreef in bericht
news:647d7e189977f7@news.codecharge.com...
> Thanks. I'll try the Dlookups and CCDLookups and see how that turns out.
>
> I tried a Dlookup but it gave me a blank page. By what you posted it
> seems I
> put the code in the wrong server event.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/13/2008, 2:12 PM |
|
Is your code in PHP? I guess I'll have to modify for ASP
|
|
|
|
wkempees
|
| Posted: 03/13/2008, 4:16 PM |
|
Yes sorry, but it shouldnot be to hard to do.
Basically steal the error routine from another form.
And apply your settings to a CCDLookUp()
Walter
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/17/2008, 12:03 PM |
|
Well, I modified the code, set it up for how I need to use it, and I get the dreaded white page of death.
dim ccs_result
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte=" &_
DBpoliceDB.ToSQL(Request.QueryString("s_dte"),ccsDate),DBpoliceDB)
if (ccs_result <> "") //not empty, ie. CCDlookup returned a date from table, so date exists in table
Response.Redirect(http://www.gobacktostartpage.com")
end if
Request.QueryString("s_dte") gets the date from the s_dte that is carried over from the start page search box. So the CCDLookup should be looking in Payperdate2 table where Dte field in the table equals the date in s_dte, and store in ccs_result. And CCDLookup stores the first retrieved value from the lookup into the variable ccs_result.
so if ccs_result is not empty, it means the lookup did find the date in the table, so it is instructed to go back to the start page and have the user try a different date. Else, if the ccs_result variable is empty, it didn't find any date in the table matching my search, so continue with adding this date.
I've tested the if/then with a constant on it's own (without CCDLookup) and that works fine. Problem is in the CCDLookup code somewhere. Like Walter said before, the dates might not match up, one being long/short date. But I've tried LIKE as well, and still the same white screen of death :)
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/17/2008, 3:01 PM |
|
Try to echo (response.write) the s_dte, just to be sure you have a value there.
In my PhP slang I would have
s_dte = CCGetFromGet("s_dte",""); // <<<< get it first
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte=" &_
DBpoliceDB.ToSQL(Request.QueryString("s_dte"),ccsDate),DBpoliceDB)
Oeps just reread your post and your are doing that already
Then the problem must lie in the datetime stamp.
walter
_________________
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
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/17/2008, 3:16 PM |
|
White screen of death, I can't help.
The date comparison can be done though.
First what format is your s_dte in?
YY-MM-DD or MM-DD-YY or something else?
What is your database dateformat for Dte?
If you want to go about this yourself:
- s_dte posted only contains the datepart (logical assumption)
would make:
dim ccs_result
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte LIKE" &_
DBpoliceDB.ToSQL(Request.QueryString("s_dte") &" %",ccsDate),DBpoliceDB)
if (ccs_result <> "") //not empty, ie. CCDlookup returned a date from table, so date exists in table
Response.Redirect(http://www.gobacktostartpage.com")
end if
That should give rows matches, but make sure (by response.write) s_dte is "xx/xx/xxxx" only.
And not "xx-xx-xxxx:00:00:00", if you catch my drift.
_________________
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
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 6:26 AM |
|
My database is an Access database. The field Dte is xx/xx/xxxx. Actually, the user inputs as mm/dd/yyyy but it's stored as m/d/yyyy (removes any leading zeroes on single digit month/day). But I would think LIKE would find a match between a MM/DD/YYYY and M/D/YYYY
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 6:50 AM |
|
My database is an Access database. The field Dte is xx/xx/xxxx. Actually, the user inputs as mm/dd/yyyy but it's stored as m/d/yyyy (removes any leading zeroes on single digit month/day). But I would think LIKE would find a match between a MM/DD/YYYY and M/D/YYYY
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/18/2008, 8:30 AM |
|
not sure, I would rather expect (MySQL) date fields 'yyyy-mm-dd'
find out (google) date comparison in Access, field types.
Back later
_________________
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
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 11:44 AM |
|
I tested to see what it's pulling from Request.QueryString("s_dte") and ran an if/then with it. It is definitely pulling "03/15/2008" (mm/dd/yyyy) but I guess it's having trouble putting that in the Lookup. Will google like you said and work some more...
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 12:34 PM |
|
I changed my Access database to store the date as mm/dd/yyyy, just like I'm using it in CCS. Still can't get it to work.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 1:11 PM |
|
I had an error in my code. For DBxxxxxxDB, I was using the database name instead of the connection name. I replaced with connection name, but still not working.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/18/2008, 1:27 PM |
|
I've even tried this:
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte=03/07/2008", DBConnection2DB)
just to simplfy and it's not even working. I even put in the "#" for searching dates in Access:
ccs_result= CCDLookup("Dte","Payperdate2","Dte=#03/07/2008#",DBConnection2DB)
I'm perplexed! 
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/18/2008, 7:10 PM |
|
afaik:
MsAccess stores dates in US format mm/dd/yyyy, whatever you use as a locale.
I would take the experiment a bit further and see the result for:
ccs_result= CCDLookup("Dte","Payperdate2","Dte>=#03/07/2008#",DBConnection2DB)
(notice >=)
see what that results to.
pressed submit a bit fast.
What I meantto say is check your Connection settings, see that it in fact uses the dateformat MsAccess uses.
Same for your s_dte field.
Tip: Check out sample Time Sheet report, it might contain the dreaded code.
http://examples.codecharge.com/CCSExamplePack2/TimeReport/TimeReport.php
I will give it a build, tomorrow, it's mind boggling, always fun
Walter
_________________
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
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/20/2008, 6:06 AM |
|
I've checked my connection settings, and they are using MS Access formats for date
I tried the >= in the ccdlookup. No change.
I'll look through the timesheet report in the example pack, see what I can come up with....
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/20/2008, 6:37 AM |
|
I just tried the Dlookup action in Design view under Events tab. Plugged in all the values needed. Still doesn't work. I don't get any syntax errors (even the stuff we tried above). I don't get anything....just a blank white screen.
It has to be something in the CCDlookup. Because I ran an if/then test with a constant date to compare with a user entered date, and that works.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 03/20/2008, 6:47 AM |
|
  
Hallelujah! I got it to work!
After doing a search for other CCDLookup posts on here, I came across one and noticed that in their code, when they were putting in their connections, they only had one "DB" in the front, not DBs surrounding the entire connection. So this:
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte=" &_
DBConnection2DB.ToSQL(Request.QueryString("s_dte"),ccsDate),DBConnection2DB)
becomes this:
ccs_result = CCDLookup("Dte", "Payperdate2", "Dte=" &_
DBConnection2.ToSQL(Request.QueryString("s_dte"),ccsDate),DBConnection2)
and it works! Thanks for working with me on this Walter!
|
|
|
|
wkempees
|
| Posted: 03/21/2008, 3:03 PM |
|
Jeez,
Quote :
I had an error in my code. For DBxxxxxxDB, I was using the database name
instead of the connection name. I replaced with connection name, but still
not
working.
I thought you had it under control.
Next time, add a DlookUp action to an event, open source view and 'steal'
all the parms from there.
Greetz,
Walter
|
|
|
|
|
