473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

complex query fails programatically

BeemerBiker
I put together a (what I consider) complex query using the Access wizard. It works fine in access but fails when I code it up. I can actually make it work in code by "simplifying" it, but then it does not do exactly what I want it to do.
The following works fine if I click on ! and run it.



Ok - after verifying the above works, I put the part before the "WHERE" into
ads4any.SelectCommand and put the part after the "WHERE" into ads4any.FilterExpression
Here is the error



By trial and error I found that if I remove the first two select items, I can get a more meaningful error message:



Qualifying EmplID with "PlannedCourses.EmplID" did not help
Replacing EmplID with "EmployeeID" worked only if I added EmployeeInfo.EmployeeID to my select statement. However, I do not want the EmployeeID in the final report, only the name. The following image shows that I stepped past the .Select command successfully and indeed, the gridview matched the selected items when I hit the continue key in the VS2008 debug.



Observation: I could not use "EmployeeInfo.EmployeeID" in the FilterExpression, but the "EmployeeID" just by itself works fine.

Question: Since the basic query "query1" in first img works fine, can remove the where clause from "query1" and do something like

ads4any.SelectCommand = "SELECT * FROM Query1()"
ads4any.FilterExpression = "Category = 'Technical' AND EmplID IN (13288,11506,9751,13404,12953,13372,13321,13429)" ..etc.. since the query never changes, but the filter values do? Maybe the filter will still fail as it does not know what EmplID is and I am not selecting it in the original Query1

best regards
Mar 14 '09 #1
2 1738
By trial and error I found that if I remove the first two select items, I can get a more meaningful error message
"Section" seems to be a reserved word, should have been [Section]. That got the better error message without haveing to remove any select items, but the filter expression still did not work unless I included the "EmployeeID" as one of the select items which I didnt want to do.

The following finally worked
Expand|Select|Wrap|Line Numbers
  1. ads4any.SelectCommand = sqlcmd + " WHERE " + ads4any.FilterExpression.ToString() ;
  2. ads4any.FilterExpression = "";
  3.  
The library code handeling the filter mechanism must behave differently than the code that evaluates the entire expression includeing the "where"

In the process of trying to figure this out I ran across a statement that the ms sql server cannot handle " IN (1,2,3, etc) " when the values inside the list are a parameter I was about to switch to a query and a parameter when I read that. I assume that access has the same problem as ms sql server.
Mar 15 '09 #2
NeoPa
32,556 Expert Mod 16PB
I'm a bit confused here BB.

Where did MS SQL Server come into the picture? I can see that you're accessing the data from outside of MS Access, but I thought the database itself was processed via MS Access.

It is generally true that SQL run from within Access will use the standard Jet engine, whereas I'm not sure this is always true when ADO or other non-DAO drivers or links are used from other environments.

I don't recall seeing a need for a field to be SELECTED before it can be filtered (WHERE clause) before, although I think I have for a SORT BY clause in Access before. This seems to be the case with your [EmplID] filtering. Quite weird. May be to do with the type of link used. Not sure.
Apr 4 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Peter Olsen | last post by:
I want to define a class "point" as a subclass of complex. When I create an instance sample = point(<arglist>) I want "sample" to "be" a complex number, but with its real and imaginary...
39
by: | last post by:
I am trying to run the following agregate function in a parameterized query on Access2000: Min(.*sqr(./.)/) The query saved OK, but an attempt to run it results in the message: The expression...
4
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to...
2
by: phillip.s.powell | last post by:
SELECT s.id, s.student_first_name, s.student_last_name, IF(s.student_ethnicity_interest_other IS NOT NULL AND s.student_ethnicity_interest_other != '', CONCAT(s.student_ethnicity_interest_other,...
12
by: vj | last post by:
Hi! I have a piece of code (shown below) involving complex numbers. The code is not running and giving error ("Invalid floating point operation" and "SQRT:Domain error"). I would be very...
2
by: Arvid Requate | last post by:
Hello, I'd like to understand why the following code does not compile. It looks like a strangeness in connection with overload resolution for the <complex> header: The conversion operator...
3
by: Slower Than You | last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and am having some problems getting my head around it. Can anyone help? TableName: CustTransactions TransactionKey AutoNumber ...
13
by: Slower Than You | last post by:
Well, I think it's complex anyway -- you might not :) TableDef: CREATE TABLE CustTransactions ( TransactionKey int IDENTITY(1,1) NOT NULL, CustomerID int, AmountSpent float, CustSelected bit...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.