|
| |
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
 |
SELECT COUNT - 11/18/2005 11:51:27
I am trying to Display the amount of total records from two tables in a MySQL database (separately) using ASP (just the total numebr of records). Can someone please point me in the right direcction for a tutorial on using SELECT COUNT or help with some quick code for accomplishing this.
Thanks in Advance
John
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: SELECT COUNT - 11/18/2005 13:00:16
The basic syntax for COUNT is as follows:
"SELECT CountOf(myFieldIWantToCount) AS TheNameThatIWantToReturn WHERE myFieldIWantToCriteria = (theValueIWantToUseAsACheckForCriteria)"
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
|
RE: SELECT COUNT - 11/18/2005 13:20:29
I'm doing something wrong here Roger
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT COUNTOF (id) AS TotalCount FROM WCust"
Set oRS = oConn.Execute(qry)
if not oRS.EOF then
while not oRS.EOF
wend
oRS.close
end if
Set oRs = nothing
Set oConn = nothing
| |
|
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
|
RE: SELECT COUNT - 11/18/2005 13:41:54
Getting Closer Roger. No errors but still connot get it to display the total amount of rows.
connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT COUNT(id) AS TotalCount FROM WCust"
Set oRS = oConn.Execute(qry)
reponse.write oRs.Fields ("TotalCount")
if not oRS.EOF then
while not oRS.EOF
wend
oRS.close
end if
Set oRs = nothing
Set oConn = nothing
%>
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: SELECT COUNT - 11/18/2005 13:44:48
quote:
Set oRS = oConn.Execute(qry)
reponse.write oRs.Fields ("TotalCount")
if not oRS.EOF then
while not oRS.EOF
wend
oRS.close
end if
Set oRs = nothing
Set oConn = nothing
Can you try this instead?
Set oRS = oConn.Execute(qry)
if not oRS.EOF then
reponse.write oRs.("TotalCount")
end if
oRS.close
Set oRs = nothing
Set oConn = nothing
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
|
RE: SELECT COUNT - 11/18/2005 13:53:24
Getting real close..I got this error now
Expected identifier
/snapshot.asp, line 32
reponse.write oRs.("TotalCount")
------------------^
| |
|
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
|
RE: SELECT COUNT - 11/18/2005 14:05:09
Spooky with that I get
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/snapshot.asp, line 32
Here is current code
connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT COUNT(id) AS TotalCount FROM WCust"
Set oRS = oConn.Execute(qry)
if not oRS.EOF then
reponse.write oRs("TotalCount")
end if
oRS.close
Set oRs = nothing
Set oConn = nothing
%>
I didn't DIM TotalCount, but should that matter?
| |
|
|
|
bigtime
Posts: 130
Joined: 10/15/2004
Status: offline
|
RE: SELECT COUNT - 11/18/2005 14:09:49
I think I need to learn how to spell.
Thanks Spooky and Roger. You guys are the greatest.
| |
|
|
|
yb2
Posts: 653
Joined: 1/30/2006
Status: offline
|
RE: SELECT COUNT - 6/17/2006 20:02:30
SELECT COUNT(*) AS TotalCount FROM WCust
is used for counting the total number of rows in a table, though you may get the right result using the column specific way too if the column does not allow nulls.
quote:
However, is it possible to combine the SELECT COUNT and SELECT * on the same page/same query? I mean get a grid with all posts and then the number of posts ?
connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * From WCust"
Set oRS = oConn.Execute(qry)
if not oRS.EOF then
'get the count from the recordset
countfields = oRS.Fields.Count
Response.Write countfields
'do your stuff with the returned records here
'
end if
oRS.close
Set oRs = nothing
Set oConn = nothing
%>
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
Try this one instead. 
k¥ 
