syntax error in simple count

C

Chieferman

I am trying to do a simple count of records in my database that match a
certain value. I have tried numerous code variations:

SELECT COUNT(*) FROM Results WHERE (Session = 'S01A')
SELECT COUNT(*) FROM Results WHERE Session = 'S01A'
SELECT COUNT(*) FROM Results WHERE (Session = ::S01A::)
SELECT COUNT(*) FROM Results WHERE ('Session = ::Session::')

None find any values. I can count all records for a numeric field but
not a text field.

Any help???
 
D

David Berry

Are you using ASP code to do this? If so, what's the code you're using.
You should be able to get a value back. I just created a quick table in
Access and when I run the query it returns the correct number of records.

SELECT Count(*) AS CountofSession
FROM Results
WHERE Session ='S01A'
 
C

Chieferman

Here is the code from frontpage:

fp_sQry="SELECT COUNT(*) FROM Results WHERE (Session = 'S01A')"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=1 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Operation_Readiness"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Expr1000"
fp_sMenuValue="Expr1000"
fp_sColTypes="&Expr1000=3&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
 
D

David Berry

Ok. Let's try a test to see if the problem is in the Query itself or with
the FP DRW. Create a new page called Test.asp, switch to Code View and try
the following ASP code.

<%
'Create the Connection object
Dim DSN_Name, Conn
DSN_Name = "DSN_NAME;" ' replace this with your DSN Name
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name
%>

<HTML>
<HEAD></HEAD>
<BODY>

<%

Dim strMyVariable, objRS, strSQL

'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")

strMyVariable = "S01A"

strSQL = "SELECT COUNT(*) As CountofSession FROM Results "
strSQL = strSQL & "WHERE (Session= '" & strMyVariable & "') "
objRS.Open strSQL, DSN_Name

%>

<b>Results are:</b> <%=objRS("CountofSession")%>

</BODY>
</HTML>

Then preview that in your browser and see if you get the desired results.
If you do then there's an issue with the DRW.
 
C

Chieferman

Tried it this is the result I got:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

/iaam/cat/test_db3.asp, line 6

Here is full code:
<%
'Create the Connection object
Dim DSN_Name, Conn
DSN_Name = "Operation_readiness;" ' replace this with your DSN Name
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name
%>

<HTML>
<HEAD></HEAD>
<BODY>

<%

Dim strMyVariable, objRS, strSQL

'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")

strMyVariable = "S01A"

strSQL = "SELECT COUNT(*) As CountofSession FROM Results "
strSQL = strSQL & "WHERE (Session= '" & strMyVariable & "') "
objRS.Open strSQL, DSN_Name

%>

<b>Results are:</b> <%=objRS("CountofSession")%>

</BODY>
</HTML>
 
C

Chieferman

Just to make sure I am giving enough information: I am using FP 2002,
MS Access db.

I use this query string and return valid result:
SELECT COUNT(*) As CountofSession FROM Results)

I use this string and get valid result: (ID is interger)
SELECT COUNT(*) As CountofSession FROM Results WHERE ID = 1

but when I try a WHERE from text field it find no results.
 
M

Mark Fitzpatrick

is the text field you're trying to apply the condition to an exact match for
the data you are entering? For example, Session = 'S01A' will need to be an
exact match. S01A cannot be contained as part of the data in the field, it
is looking for a complete match. You could try SESSION LIKE '%S01A%' to try
to match within the text. The % are SQL wildcards.
 
D

David Berry

Sorry, I was typing too fast. Change

DSN_Name = "Operation_readiness;" ' replace this with your DSN Name

to

DSN_Name = "DSN=Operation_readiness;"
 
K

Kathleen Anderson [MVP - FrontPage]

Chieferman said:
That did the trick. The field name "session" was the issue.
thanks!!!!!
Click to expand...

You're welcome. I actually got caught by the same word about a year ago in a
training evaluation web site - the database contained fields named course
and session.


--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
 
Top