COUNT( group by) problem

[jowan]

Hi, everyone,
I have a table below, I would like to count the eID by group to know how many eID group will 115 have. So, it is suppose to have 3 groups:X (1), Y(2) and z(1). In the end, recordcount will show there are 3 groups for eID refer to ID 115.
TblX
ID ¦ eID ¦ COUNT(eID)
115 ¦ X---¦----> 1
115 ¦ Y---¦----> 2
115 ¦ Y---¦---->
116 ¦ Y
115 ¦ Z---¦----> 1

Code snippet:
Set wRS = Server.CreateObject("ADODB.Recordset")
sql1="SELECT eID,COUNT(eID) AS count FROM TblX GROUP BY eID WHERE ID='"&ID&"'"
wRS.Open sql1,wCON,1,1,1

But it shows: Syntax error (missing operator) in query expression 'eID WHERE eID='115''.

I wonder what could be wrong. Any help is very much appreciated.

[Mosha]

sql1="SELECT eID,COUNT(eID) AS count FROM TblX GROUP BY eID WHERE ID='"&ID&"'"
wRS.Open sql1,wCON,1,1,1

from what i see you haven't specified where to select eID from, for example

SELECT eID FROM yourTableName

I'm not 100% on this, i'm new to all this but thats what i noticed

[WebJoe]

WHERE before GROUP:

SELECT eID, COUNT(eID) AS count FROM TblX WHERE ID='"&ID&"' GROUP BY eID

[jowan]

Thanks for help. It works now but I have another problem. I open the recordset with:
wRS.Open sql1,wCON,1,1,1

but the following error appeared:
"ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "

When I used set wRS=wCON.Execute(sql1). No error appeared. But the recordCount gave -1. What I have done wrong?

[jowan]

Thanks for help. It works now but I have another problem. I open the recordset with:
wRS.Open sql1,wCON,1,1,1

but the following error appeared:
"ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "

When I used set wRS=wCON.Execute(sql1). No error appeared. But the recordCount gave -1. What I have done wrong?

[duckhunter]

Response.Write sql1
Response.End 'ie: don't let it error. just get the statement

Then run it in T-SQL. If it runs fine, then remove all the optional parameters in your open statement. It's probably that last "1"

wRS.Open sql1,wCON

The .Open method accepts 5 Parameters. (Source, ActiveConnection, [CursorType], [LockType], [Options])

Your Open statement is passing 1 in the Options param. That's my bet.