Need sql to count records in a table

I have a table (tblCustomers) with a field "DomainName". I want to be able to create a function that will return an integer with the number of records in tblCustomers where the criteria is a specific DomainName value. In other words, for any specified domain name, I want to be able to have the function return the number of records containing that specific domain name within the field DomainName.
M_EpsteinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
This should do the trick:
SELECT COUNT(*)
FROM tblCustomers
WHERE DomainName = 'DomainNameValue'

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
If need to account for variations then, like this and I forgot to name the response.
Broke up the string so you can see how to concatenate to a parameter like @DomainNameValue where the literal value 'DomainNameValue' is.
SELECT COUNT(*) AS CountOfRecords
FROM tblCustomers
WHERE DomainName LIKE '%' + 'DomainNameValue' + '%'

Open in new window

0
BrandonGalderisiCommented:
" I want to be able to have the function return the number of records containing that specific domain name within the field DomainName."

Since you say function:

This is mwvisa1's code from http:#22827211 and http:#22827223, slightly modified (with the parameter) and wrapped in a function.
--if you want LIKE searches
create function fn_CustomerDomainCount (@Search nvarchar(255))
returns int
as
begin
return (
  SELECT COUNT(*) AS CountOfRecords
  FROM tblCustomers
  WHERE DomainName LIKE '%' + @Search + '%'
  )
end
go
 
 
--if you want LIKE searches
create function fn_CustomerDomainCount (@Search nvarchar(255))
returns int
as
begin
return (
  SELECT COUNT(*) AS CountOfRecords
  FROM tblCustomers
  WHERE DomainName = @Search
  )
end
go

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
Thanks, Brandon.  Wasn't feeling up to all the typing and wasn't sure if the function was in Visual Basic side or DB side.
0
BrandonGalderisiCommented:
No prob.  Just helping out.
0
M_EpsteinAuthor Commented:
I must be missing something. This is for VB6, with an Access 2003 db on the back end, connected by ADO.

I get an error message when I use this code: (no value given for one or more parameters)


Dim ssql As String
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "(SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = msn.com)"

MsgBox Products_con.Execute(ssql)
0
Kevin CrossChief Technology OfficerCommented:
It thinks msn.com is a parameter versus a string literal as you have not put in quotes.

Additional information helps.  Guess I should have asked, but I had a feeling why I didn't post you function code. ;)

Try like this:
Dim ssql As String
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = ""msn.com"""
 
MsgBox Products_con.Execute(ssql)

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
You can throw in a:

MsgBox ssql

And ensure that the output SQL looks like this:

SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = "msn.com"
0
M_EpsteinAuthor Commented:
The string came out perfect. When the Select Count executed, I get a type mismatch error.
0
BrandonGalderisiCommented:
You don't want to do the msgbox on the execute, just the SQL.



Dim ssql As String
dim rs as new adodb.recordset
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = ""msn.com"""
 
MsgBox ssql
rs=Products_con.Execute(ssql)
if not rs.eof and not rs.bof then
msgbox rs("CountOfRecords")
end if

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Good catch, Brandon.

I just copied M_Epstein's code on that paying attention to the quotes on domain name. ;)

Definitely want it how Brandon has it.
0
BrandonGalderisiCommented:
I didn't change anything with the way quotes are handled.  I just assumed that they were using access or something that wanted values in doubles instead of singles.  I change the msgbox to "msgbox ssql" instead of "msgbox products_con.execute (ssql)"

products_con.execute (ssql) returns a recordset object which, I don't believe, can be a msgbox, hence the type mismatch.
0
Kevin CrossChief Technology OfficerCommented:
I know.  That is what was wrong with the code before.  It need the double quotes and I added it.  You caught the next error location. ;)
0
M_EpsteinAuthor Commented:
The above code works, thank you. I now want to encase this in a function, so I will replace msn.com with a string variable. I have tried to do this, but I cannot get the syntax corect,and get error messages.

Dim DM As String
DM = "msn.com"

Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = 'DM'"
MsgBox DM
MsgBox ssql
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
MsgBox rs("CountOfRecords")
End If
0
BrandonGalderisiCommented:
I assume that your connection (products_con) is declared somewhere else in your app and available to this function.  This should be what you need.
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = '" & DM & "'"
 
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
GetRecordCount = rs("CountOfRecords")
End If
rs.close
set rs=nothing
end function

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
You will need to have the DM outside of the quotes SQL statement.
(check the ssql display in message box to make sure the number of quotes are correct, but this should get it)
Dim DM As String
DM = "msn.com"
 
Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = """ & DM & """"
MsgBox DM
MsgBox ssql
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
MsgBox rs("CountOfRecords")
End If

Open in new window

0
M_EpsteinAuthor Commented:
You are both correct. It worked both ways.

Can you explain this syntax to me? I get very confused with the full and half quotes...
0
BrandonGalderisiCommented:
To me, it's a matter of what engine you are running against.  If it's access, you need double quotes.

If it's SQL Server, then single quotes will always work.  Double quotes will work, as long as quoted_identifiers are turned off (but you can override that).

So I always use singles against a SQL connection.


I'll walk through the function below.
'To use it
'This will msgbox the record count
msgbox GetRecordCount ("PA")
 
'This will pull the value into a variable
dim i as integer
i= GetRecordCount ("PA")
 
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
'you need a recordset to capture the output of your SQL
Dim rs As New adodb.Recordset
'Your SQL
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = '" & DM & "'"
 
'populates rs with the result of products_con.execute
'another method would be -- > rs.open, ssql, products_con
Set rs = Products_con.Execute(ssql)
'tests to ensure that a record was retrieved
If Not rs.EOF And Not rs.BOF Then
'gets the value of the "CountOfRecords" column and assigns it to the function
GetRecordCount = rs("CountOfRecords")
End If
'close the recordset
rs.close
'dispose of the recordset
set rs=nothing
end function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
I always forget with MS ACCESS you can use the single quotes (') for string literals too.  That is the string literal identifier in SQL so I am actual more use to that, but in VB strings are usually in double quotes (") and likewise for ACCESS.

When using " in VB, since the strings are already have " there, you have to use "" to escape the fact that you want a double quote to end up in your resulting string.  

In your case, since ' does work in the SQL side of things, probably easy to just use ' so you don't have to keep escaping it. :)

Hopefully that answered it and wasn't more confusing.
0
Kevin CrossChief Technology OfficerCommented:
>>This is for VB6, with an Access 2003 db on the back end, connected by ADO.
I was operating under this statement, so as Brandon says that is why I was using double quotes.  
0
M_EpsteinAuthor Commented:
How would I get a count of all records in the tblCustomers where the field CustomerEmailDomain is not Null (has any entry at all). Thanks.
0
BrandonGalderisiCommented:
Two ways:  Another function that gets a count with no WHERE.  Or interpret a 0 length string passed in as a count all.  I'll show the second.


Calling it like this:

i= GetRecordCount ("")

will return all.
'To use it
'This will msgbox the record count
msgbox GetRecordCount ("PA")
 
'This will pull the value into a variable
dim i as integer
i= GetRecordCount ("PA")
msgbox i
i= GetRecordCount ("")
msgbox i
 
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
'you need a recordset to capture the output of your SQL
Dim rs As New adodb.Recordset
'Your SQL
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers"
if len(dm)>0 then 
  ssql = ssql & " WHERE CustomerEmailDomain = '" & DM & "'"
end if
 
'populates rs with the result of products_con.execute
'another method would be -- > rs.open, ssql, products_con
Set rs = Products_con.Execute(ssql)
'tests to ensure that a record was retrieved
If Not rs.EOF And Not rs.BOF Then
'gets the value of the "CountOfRecords" column and assigns it to the function
GetRecordCount = rs("CountOfRecords")
End If
'close the recordset
rs.close
'dispose of the recordset
set rs=nothing
end function

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Alternatively for what it is worth, you could just change this line of existing Function:

ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain like '" & DM & "' AND CustomerEmailDomain IS NOT NULL;"

Then you can pass * or msn.com.  If value is equal to a domain name the IS NOT NULL won't hurt it finding record as it has to not be NULL to match value passes.  if value is equal to *, then you will now match on all domains as long as they have a value to begin with.  You can also try for blanks.

CustomerEmailDomain IS NOT NULL ==> NZ(CustomerEmailDomain, "") <> ""
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.