SQL error message on RSet lookup. 1

[qwert231]

Get this message:
Column 'CustomerProducts.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
/includeSets.asp, line 144

From this line of code:
prgSet.open "SELECT * FROM CustomerProducts WHERE CustomerID = '" & custid & "' AND ProgramID = '" & searchID & "' GROUP BY ID", strC, adOpenStatic

Now I'm sure some of these field names are triggering nasty reserved word errors and problems. Any ideas how I can get by this? Thanks.

 

[yandso]

You should change your group by to order by.

Roj

 

[qwert231]

Okay, that works, but that doesn't do anything. I want to have a recordset tht contains 1 record for each ProgramID. In the sample data below, the third column, you see that there is more than one 1 and 10. I would like the sample data to result in a recordset with only 3 records.

Sample data:
CustomerID ProgramID ID Sequence ProductID
0008 1 1 7 8-2535-10-MP-2
0008 1 1 8 8-2535-10-MP-2
0008 1 1 9 2535T-10-MP-2
0008 1 10 1 810-10-MP-2
0008 1 10 2 57-10-MP-2
0008 1 10 3 8-2535-10-MP-2
0008 1 10 4 2535T-10-MP-2
0008 1 11 1 57-10-MP-2

 

[yandso]

First things first. Are you using all the data? If you are not going to show any part of the data you should leave it out. Then you can use distinct.

Select distinct customerID,programid
from customerproducts.....

 

[qwert231]

Hmm... INteresting. As you can see, my query selects by CustomerID and ProgramID, but I don't need to list them. Let me try that. Thanks. BRB.

 

[qwert231]

Okay, but now my list of ID's comes out like this...
1
10
11
12
...
2
3
4
5
6

Hmmm.... what can I do for that? (Sometimes however, it will be text. ie. A, Abw, B, Bbw...)

 

[qwert231]

Also, how can I filter out some records.

 

[yandso]

Ok so now you have only one of each the next thing to work on is your where statement. Which field is your primiary key. Also could you post your query as it appears in sql.

 

[qwert231]

I haven't created one in SQL yet... just in ASP page.
Here is what I have:
SELECT Distinct ID FROM CustomerProducts WHERE ([CustomerID] = '" & custid & "' AND [ProgramID] = '" & searchID & "') Order BY ID

There are 4 Keys. CustomerID, ProgramID, ID, and Sequence. This is a database created and used by our production software. So I can't change it. (Isn't life grand.)

 

[yandso]

The only thing that I can see is that you have [] around your field names. Other than that you should only be getting returns that match.

Sorry I could not be more helpfull

 

[qwert231]

How about searching a field for anything BUT a certain criteria. It thought it would be like a NOT statement, but can't find it.

 

[yandso]

You can do that.

and id <> 'value'

 

[qwert231]

Kewl. That's right. What do brackets ([]) do? I needed 'em for one query once, cause the field names used reserved words.

 

[yandso]

Well Ill be jiggered, did not know that about [].

Good luck

 

[qwert231]

Actually, I was asking for clarification. I wanted to know what you knew about brackets...

 

[yandso]

Oh sorry as you can see not much. However a quick question for the DBA and here is the answer. The [] comes from Microsoft and is not normally used, and as you guessed they are used when you want to use a reserved name for a field or when there is a space in a field name. Other than that they should really have no effect.

Roj