Select Count(*) problem

[MP]

vb6,ado,mdb,win 2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(cons t) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properti es("Persist Security Info") = False
moConn.Connecti onString = msConnString
moConn.CursorLo cation = adUseClient
moConn.Mode = adModeReadWrite ' or using default...same result
moConn.Open
can't seem to get my sql right
here's a few versions of the many ways i've tried with the resulting error
codes following each

' sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType.Value = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
" WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
" fldCondName.Val ue = '" & sCond & "' AND " & _
" fldCondVar.Valu e = '" & sCondVar & "' AND " & _
" fldCondLBR.Valu e = '2L' ORDER BY fldTypeName"
'ErrState: -2147217887 <You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate function.>
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '3' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".fldTypeName.V alue = '" & sType & "'
AND " & _
' TABLE_NAME & ".fldCondName.V alue = '" & sCond & "' AND " & _
' TABLE_NAME & ".fldCondVar.Va lue = '" & sCondVar & "' AND " & _
' TABLE_NAME & ".fldCondLBR.Va lue = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = ''2L''"
'ErrState: -2147217900 (Syntax error (missing operator) in query expression
'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = ''2L'''.)

eliminate the where clause maybe that's the problem?
'Try execute sql <Select Count(*) As 'Total' From tblTypeCond>
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

yikes...what's the problem???

i'm running out of ideas
any help?
Thanks
Mark

[teddysnips]

MP wrote:

vb6,ado,mdb,win 2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(cons t) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properti es("Persist Security Info") = False
moConn.Connecti onString = msConnString
moConn.CursorLo cation = adUseClient
moConn.Mode = adModeReadWrite ' or using default...same result
moConn.Open
can't seem to get my sql right
here's a few versions of the many ways i've tried with the resulting error
codes following each

' sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType.Value = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
" WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
" fldCondName.Val ue = '" & sCond & "' AND " & _
" fldCondVar.Valu e = '" & sCondVar & "' AND " & _
" fldCondLBR.Valu e = '2L' ORDER BY fldTypeName"
'ErrState: -2147217887 <You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate function.>
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '3' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".fldTypeName.V alue = '" & sType & "'
AND " & _
' TABLE_NAME & ".fldCondName.V alue = '" & sCond & "' AND " & _
' TABLE_NAME & ".fldCondVar.Va lue = '" & sCondVar & "' AND " & _
' TABLE_NAME & ".fldCondLBR.Va lue = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = ''2L''"
'ErrState: -2147217900 (Syntax error (missing operator) in query expression
'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = ''2L'''.)

eliminate the where clause maybe that's the problem?
'Try execute sql <Select Count(*) As 'Total' From tblTypeCond>
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

yikes...what's the problem???

i'm running out of ideas
any help?
Thanks
Mark

You don't need to dereference the column names to determine or set the
value. Taking your first example:

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _

' " WHERE fldType = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"

etc. Obviously I can't vouch for the actual efficacy of this, since
you don't provide table defs.

Edward

[MP]

<te********@hot mail.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

>
You don't need to dereference the column names to determine or set the
value. Taking your first example:

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _

' " WHERE fldType = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"

etc. Obviously I can't vouch for the actual efficacy of this, since
you don't provide table defs.

Edward

Thanks for looking at it.
I knew I didn't *need* to explicitly use .Value but I always heard it's best
not to rely on default properties.
I'm not sure its' *wrong* to explicitly specify the .Value in a Select
statement either...but maybe it is.
In any case it still doesn't work, the error running the above is:
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

There was a typo in the first example but i fixed it before running this
again ... fldType is fldTypeName
the actual string run was:
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"

Earlier in the prog I run a string to get a distinct group
sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondvar, fldcondlbr"
& _
" FROM " & TABLE_NAME & _
" ORDER BY fldTypeName, fldCondName, fldCondvar, fldcondlbr"
FormReport "Try execute sql <" & sSql & ">"
Set oRs = moDb.Execute(sS ql)
that returns me a set with several records so the field names are right
(Table_Name is a string constant for the table name.)

now i'm trying to use the same open connection to get another recordset with
the count
maybe you cant' reuse a connection?????

i'm looping through the above recordset to try to get a count for each group
within it
While Not oRs.EOF
sType = oRs.Fields.Item ("fldTypeName") .Value
sCond = oRs.Fields.Item ("fldCondName") .Value
sCondVar = oRs.Fields.Item ("fldCondVar"). Value
FormReport "Check " & sType & sCond & sCondVar
so now i build the sql string using those variables
sSql = "Select Count(*) From " & TABLE_NAME & _
" WHERE fldTypeName = '" & sType & "' AND " & _
" fldCondName = '" & sCond & "' AND " & _
" fldCondVar = '" & sCondVar & "' AND " & _
" fldCondLBR = '2L'"

'try to get another recordset via same connection...ma yb that the problem?
Set oRsCount = moDb.Execute(sS ql)
and thats when i get the errors

I can't even drop all the wheres' and get it to work
sSql = "Select Count(*) From " & TABLE_NAME

Try execute sql <Select Count(*) From tblTypeCond>
ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

I know i don't have a field named Count or one named * but I thought that
was a generic function that should run on any table...
do you see what i'm doing wrong?

I spent about 4 hours on google reading tons of posts on Select Count(*) etc
and don't see what I'm doing wrong.

Thanks
mark

[MP]

"MP" <no****@Thanks. comwrote in message
news:JR******** **********@torn ado.rdc-kc.rr.com...

>
<te********@hot mail.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

You don't need to dereference the column names to determine or set the
value. Taking your first example:

I should have tried this earlier...
the problem isn't in the select count statement it's in reusing the
connection...
if i isolate the count to a single call on the connection it works
sSql = "Select Count(*) From " & TABLE_NAME
FormReport "Try execute sql <" & sSql & ">"
Set oRs = moDb.Execute(sS ql)
lCount2 = oRs.Fields(0).V alue
FormReport "Lcount2: <" & lCount2 & ">"

result:
Try execute sql <Select Count(*) From tblTypeCond>
Lcount2: <37>

so I guess I have to establish multiple connections to get multiple
recordsets???
seems I cant do
Set oRs1 = oConn.Execute(s ql1)
while not ors1.eof
set ors2 = oconn.execute(s ql2)
etc
ors1.movenext
wend

i guess i need something like
Set oRs1 = oConn1.Execute( sql1)
Set oConn2 = new connnection to same database
while not ors1.eof
sql2 = based on ors1 values
set ors2 = oconn2.execute( sql2)
etc
ors1.movenext
wend

is that right?
thanks

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:JR******** **********@torn ado.rdc-kc.rr.com:

I knew I didn't *need* to explicitly use .Value but I always heard
it's best not to rely on default properties.

There is no Value property in a SQL statement. The fields in a
recordset have a Value property, but not in SQL.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:JR******** **********@torn ado.rdc-kc.rr.com:

I spent about 4 hours on google reading tons of posts on Select
Count(*) etc and don't see what I'm doing wrong.

I don't think you need to do that. Look at my other post about the
self-join. You might want to do the Count() in the virtual table I
suggested in that post if that number is important and you want only
one record returned by the self-join. But I think you need both the
count and the value assigned to your last field, no?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:Zj******** **********@torn ado.rdc-kc.rr.com:

so I guess I have to establish multiple connections to get
multiple recordsets???

[]

is that right?

I don't do ADO. There are none of these problems in DAO at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"MP" <no****@Thanks. comwrote in
news:JR******** **********@torn ado.rdc-kc.rr.com:

I knew I didn't *need* to explicitly use .Value but I always heard
it's best not to rely on default properties.

There is no Value property in a SQL statement. The fields in a
recordset have a Value property, but not in SQL.

--

AH HA!
:-)
Thanks for that.
But i dont' think that was my *only* problem <g>
somethings wrong in the loop i'm calling this in
(see other posts this thread)

even with no fieldnames in the sql it wasnt' working
(see other posts this thread)

if i call it outside of a loop...on it's own it works....
still looking for the solution

[MP]

David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"MP" <no****@Thanks. comwrote in
news:JR******** **********@torn ado.rdc-kc.rr.com:

I spent about 4 hours on google reading tons of posts on Select
Count(*) etc and don't see what I'm doing wrong.

I don't think you need to do that. Look at my other post about the
self-join. You might want to do the Count() in the virtual table I
suggested in that post if that number is important and you want only
one record returned by the self-join. But I think you need both the
count and the value assigned to your last field, no?

I'm studying that post now and will post back when I get it figured out.
Thanks again

[MP]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"MP" <no****@Thanks. comwrote in
news:Zj******** **********@torn ado.rdc-kc.rr.com:

so I guess I have to establish multiple connections to get
multiple recordsets???

[]

is that right?

I don't do ADO. There are none of these problems in DAO at all.

ok ok, don't rub it in :-)
you're eventually gonnna talk me into having to learn dao now eh?
<vbg>