Quite often, I find it necessary to determine a number if items in a table. This happens when I want to make sure something exists (the count is greater than 0). Trying to perform work on a non-existent result will give me an error, so I must first make sure I have something to work with! Or perhaps I know something exists, and I just need the total. For instance, I know I have lots of green socks in my inventory; I need to know many, and perhaps whether or not I should therefore be ordering more green socks.
Here’s a simple way to do this:
Dim intCount, strSQL
strSQL = "SELECT COUNT( * )
FROM socks WHERE color = ‘green’"
set objRS = server.CreateObject("ADODB.RecordSet")
objRs.Open strSQL2, strConnect
intCount = objRS( 0 )
objRS.Close()
Set objRS = Nothing
If intCount < 100 Then
Response.Write( “You’d better order more green socks!” )
Else
Response.Write( “You have lots of green socks” )
End If
Now, we’re in the same position as before. This is not the most difficult thing to manage, but suppose you have to do it for green, red, orange, yellow, and puce socks all individually? Now it becomes repetitious. So, here we go:
'=====================================
function getCount( strTable, strCriteria, altString )
'=====================================
dim strSQL2, objRS
strSQL2 = ""
if trim( altString ) = "" then
if strTable = "" then call errorMessage( "Invalid table!" )
strSQL2 = "SELECT COUNT( * ) FROM " & strTable
if strCriteria <> "" then strSQL2 = strSQL2 & " WHERE " & strCriteria
else
strSQL2 = altString
end if
'debug( strSQL2 )
set objRS = server.CreateObject("ADODB.RecordSet")
objRs.Open strSQL2, Application("MySQL_inventory")
getCount = objRS( 0 )
objRS.Close()
Set objRS = Nothing
strSQL2 = null
end function
Now watch how this simplifies our life:
If getCount( “socks”, “color = ‘green’”, “” ) < 100
Then Response.Write( “Almost out of green socks!” )
End If
Here’s another example:
If getCount( “users”, “email = ‘me@here.com’”, “” ) = 0
Then strErrorMessage = “Sorry, that email address
does not exist in our records.”
End If
There, we’ve done it again. Six lines of code reduced to one function call!
But you ask: “What if I am a good RDBA and have normalized my tables to the 3rd normal form? ‘strTable’ is insufficient when I’m performing JOINs and the like!”
This is very true, and that’s where the altString parameter comes in. We could create our SQL statement separately, with all the fancy JOINs we need. Something like this:
strSQL = “SELECT COUNT( s.id ) FROM socks s “ & _
“INNER JOIN colors c ON c.id = s.color_id “ & _
“WHERE c.description = ‘” & strColor & “’”
if getCount( “”, “”, strSQL ) < 100
Then Response.Write( “Almost out of green socks!” )
This little function has simplified my life to the extent that I now only have to spend three minutes of my day coding, and the rest of if writing for ASP Free! A slight exaggeration, but it’s very useful, I kid you not.
Now it’s worth noting that in our first function, commitToDB(), one of the parameters (strDB) allowed us to perform the procedure using the default connection, or specify our own. It would be simple to add this feature to the getCount() function: just add the extra parameter, and the default setting within the function. There are two reasons why it’s not already there:
- I copied the function from an application where I’m only referencing one database. I had no need for the optional parameter, so I took it out to save myself writing the extra ‘, “” ’ every time.
- So that I could highlight the simplicity of modifying these tools to fit your needs. Aren’t I nice?
Now let’s take a look at the third tool.
