|
| |
SQL Server/FP 2003 Query - Updated(part of the problem found)
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
 |
SQL Server/FP 2003 Query - Updated(part of the problem ... - 2/16/2004 18:08:36
What is the proper way to run a query against SQL Server that will pull between dates. I have the following query:
SELECT Sales_Managers.slsmgr, Format((Sum([RC]*6.5))+(Sum([NRC])),'$#,##0.00') AS NSR, Sum(Orders.Package) AS SumOfPackage, Sum(Orders.PCS) AS SumOfPCS, Sum(Orders.DSL) AS SumOfDSL, Sum(Orders.Narrowband) AS SumOfNarrowband, Sum(Orders.CPE) AS CPEquip, Sum(Orders.LD) AS SumOfLD FROM Orders INNER JOIN (Employees INNER JOIN Sales_Managers ON Employees.slsmgr = Sales_Managers.slsmgr) ON Orders.SalesCode = Employees.slscd WHERE (((Orders.AppDate) Between '::StartDate::' And '::EndDate::')) GROUP BY Sales_Managers.slsmgr HAVING (((Sales_Managers.slsmgr)='::slsmgr::'));
This query fails unless I pull the WHERE (((Orders.AppDate) Between '::StartDate::' And '::EndDate::')) clause out of the query. Please and thanks much all
Mike
< Message edited by mikeb -- 2/16/2004 19:06:36 >
|
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/16/2004 19:57:02
No errors, works correctly
| |
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 13:42:02
Different problem. I set it to debug and solved the defaults issue, was a different page as well. Problem now is I think in the way dates are being passed to the sql server. I can hard code the dates, and it works, but if I pass them by using a search form or posting to the page from another form, I get the error.
| |
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 14:11:06
Date formated 02/16/04 the parameter formated '::StartDate::'
Used to have them in the format #::StartDate::# but when we moved from Access to SQL Server, it would no longer accept it that way.
As a note. It works perfectly when I run a single date, but crashes and burns when I try to run a " between '::StartDate::' and '::EndDate::' "
| |
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 14:26:37
Yup,
Is it possible that Between is not valid for SQL Server?
Unable to find operator in query string. Query string currently is SELECT Sales_Managers.slsmgr, Employees.slscd, Employees.lname, Employees.fname, (Sum([RC]*6.5))+(Sum([NRC])) AS NSR, Orders.AppDate, Sum(Orders.Package) AS SumOfPackage, Sum(Orders.PCS) AS SumOfPCS, Sum(Orders.DSL) AS SumOfDSL, Sum(Orders.Narrowband) AS SumOfNarrowband, Sum(Orders.CPE) AS CPEquip, Sum(Orders.LD) AS SumOfLD FROM Orders INNER JOIN (Employees INNER JOIN Sales_Managers ON Employees.slsmgr = Sales_Managers.slsmgr) ON Orders.SalesCode = Employees.slscd WHERE (((Orders.AppDate) Between '::StartDate::' And '::EndDate::')) GROUP BY Sales_Managers.slsmgr, Employees.slscd, Employees.lname, Employees.fname, Orders.AppDate HAVING (((Sales_Managers.slsmgr)='::slsmgr::'));
BTW, really appreciate your time on this
< Message edited by mikeb -- 2/17/2004 14:28:26 >
| |
|
|
|
jdanderson02
Posts: 27
Joined: 2/17/2004
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 14:43:30
All,
I had run in to this same problem when trying to implement someone's tutorial on using column headings as hyperlinks to sort database results. As an MSDN subscriber I contacted Microsoft about this and to make a long story short , their initial response was that there was a bug with FP 2003. Apparently the problem is with the way FP2003 generates the fpdbrgn1.inc file, according to Microsoft. Unfortunately I was subsequently told that MS has no plans to correct this problem as they blame the problem on the "click-back" step in the Database Results Wizard process used to create hyperlinks that sort as it is a misuse of FP (even though it works fine in FP2002). However, I have found that anytime I try to pass parameters (i.e. ::value::) in a query created with FP2003 that I have the same problem, it doesn't work.
The solution I received from MS was to uninstall FP2003 and use FP2002. If anyone would like to see the email thread please let me know. Or if anyone has been able to get this to work in FP2003 I'd like to find out how as I would prefer to use FP2003 but cannot until the problem is fixed or if there is a way around it. Thanks.
| |
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 14:54:43
If you use FP2002, will it hose what has been done with fp2003? maybe a better solution is to hand code in asp, as much as I'd rather not for time constraints and learning curve... still learning asp/asp.net
| |
|
|
|
jdanderson02
Posts: 27
Joined: 2/17/2004
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/17/2004 16:54:55
Mike - We had to start from scratch using FP2002. Basically we had to delete the fpdblib.inc file and any fpdbrgn*.inc files (both located in the _fpclass folder) and have FP2002 generate it's own version of these files in order to get any passed parameters to work.
| |
|
|
|
jdanderson02
Posts: 27
Joined: 2/17/2004
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/18/2004 16:28:00
So, Spooky. Pardon my ignorance on this subject, but are you saying that I can use FP2003 and workaround the bug by simply replacing the includes generated by FP2003 with the includes contained in the zip file you pointed to? Grazie.
| |
|
|
|
mikeb
Posts: 53
Joined: 10/15/2003
Status: offline
|
RE: SQL Server/FP 2003 Query - Updated(part of the prob... - 2/23/2004 13:34:08
Ok, I'm using both versions of the include right now, depending on whether or not there is a date in the query, as I can't get the dates to pass correctly with the old version of the includes due to a character string conversion error that MSSQL server throws. The question I have is that everything is working correctly, but I'd like to be able to just completely remove the yellow error message boxes from being displayed on the page when I have no default values or when SQL server pukes on the Default tag that fp2003 includes cause. Is it possible to do this?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
