syntax error in simple count

  • Thread starter Thread starter Chieferman
  • Start date Start date
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???
 
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'
 
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
%>
 
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.
 
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>
 
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.
 
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.
 
Sorry, I was typing too fast. Change

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

to

DSN_Name = "DSN=Operation_readiness;"
 
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/
 
Back
Top