sql query using COUNT

**mikeistyke** · Jan 24, 2005 08:58

I'm trying to use a SELECT query with COUNT.

I'm using the string retrieved from the text box to match the records held in the Db.
The query below returns this error: Data type mismatch...the line in question is the sql itself.

SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes HAVING (((Count(Zipcodes.Zipcode))='" & strZipcode & "'))

Thank you guys for all your help....

**rokc** · Jan 24, 2005 09:10

hi,

try first converting the strZipcode value you get from text box to Integer..

like this,

Code:

strZipcode = CInt(strZipcode)

**mikeistyke** · Jan 24, 2005 09:23

After converting that...
strZipcode = CInt(strZipcode)

Do I request it like this?
CInt(strZipcode) = Request.Form("Zipcode")

Does that make sense?

**Thing** · Jan 24, 2005 10:10

No you convert while setting the variable:

Code:

strZipcode = CInt(request.form("Zipcode"))

**ole dawg** · Jan 24, 2005 10:36

What exactly is your query supposed to achieve?

It seems it's hard for the query to work because you're comparing a numeric with a string.

HAVING (((Count(Zipcodes.Zipcode))= '" & strZipcode & "'

.

You'd need to remove the single quotes (or convert the Count value to a string) in order to solve this problem.

Hope this helps.

**mikeistyke** · Jan 24, 2005 11:52

Here's what i'm trying to achieve with this:

First, capture the value of a text box entered by the user, called ZipBox

Next, convert that string to a numeric like they showed me:

strZipcode = CInt(request.form("Zipcode"))

then run the statement against the Db:

SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes HAVING (((Count(Zipcodes.Zipcode))='" & strZipcode & "'))

I want to compare the user value, say, 90028, to a value stored in the Db. If 90028 is in the Db, how many of them are there?

Does that make sense?

**ole dawg** · Jan 24, 2005 12:21

It's simpler than that.

SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes where zipcode = '" & strZipcode & "';

Use the single quotes if your Zipcode field is text and not numeric. You actually should probably make it a text field if you expect zipcodes with leading zeroes.

**mikeistyke** · Jan 24, 2005 12:25

I apologize. I might have been trying to do too much at once with too little knowledge of the overall scheme.
I was trying to use an INSERT INTO statement in the same script as the SELECT statement and it's full of wrong things.
Let me first do the INSERT INTO statement. Then i can do the second part of checking the contents with the select statement.
I'll do that and come back....
Thanks guys...

**mikeistyke** · Jan 24, 2005 16:58

I better display what i'm getting at this point.
When I open the page, the amount of all the zipcode records are displayed i nthe html form. Obviously, i've done somehting wrong.
Because i was having a tough time using an insert statement with a select statement within the same script, i decided to break them up and see what i would get.
Here are the two db calls i'm using:
'--THIS CONNECTION SELECTS THE RS FROM THE DB
Response.Buffer = True
Response.Expires = 0

Dim objConn
Dim objRS
Dim strConn
Dim strQ

dbPath = "c:\accounts\valleyba\wwwroot\HomeBaseDB.mdb"
db=server.mappath("HomeBaseDB.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = objConn
strQ = "SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes where zipcode = '" & strZipcode & "' "
objRS.Open strQ
%>
<% While Not objRS.EOF %>
<html>
<body>

<table border="1" cellspacing="1" width="10%">
<tr>
<td width="75%" align="center"><b><font size="2"># of Records that match</font></b></td>
</tr>
<tr>
<td width="25%"><%= objRS("Zipcode") %> </td>
</tr>
</table>

<%objRS.MoveNext
Wend
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
%>

<%
'--THIS CONNECTION INSERTS INTO THE DB
Response.Buffer = True
Response.Expires = 0

'--check if submit has been selected
If Request.Form("Submit") <> "" Then

Dim strZipBox
Dim strZipcode
Dim DataConnection
Dim SQL
Dim strUserInput
Dim strError1
Dim strError2

strZipcode = Request.Form("ZipBox")

strError1 = "* Max. 5 numbers"
strError2 = "* Min. 5 numbers"

If Len(strZipcode) > 5 Then
Response.Write (strError1)
ElseIf Len (strZipcode) <= 4 Then
Response.Write (strError2)
Else

strUserInput = Replace(strUserInput, " ' " , " ' ' ")
dbPath = "c:\accounts\valleyba\wwwroot\HomeBaseDB.mdb"
db=server.mappath("HomeBaseDB.mdb")
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

sSQL = " INSERT INTO Zipcodes (Zipcode) VALUES ( '" & Zipcode & " ')"
DataConnection.Execute sSQL
DataConnection.Close
Set DataConnection = Nothing

Response.Write "Record has been updated. Thank you"
End if
End IF

Thanks for having another look.

**Thing** · Jan 24, 2005 19:24

You do not need to loop through your records, since you will only be returning 1 record. Therefore check for a record, and if it exists assign it to a variable. If not return a 0 like so:

Code:

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = objConn
strQ = "SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes where zipcode = '" & strZipcode & "' "
objRS.Open strQ
If objRS.EOF = false then
'we found a match
zipcode_count = objRS("Zipcode")
Else
zipcode_count = 0
End if
objRS.close 
objConn.close
Set objRS = Nothing
Set objConn = Nothing
%>
<html>
<body> 
 
<table border="1" cellspacing="1" width="10%">
<tr>
<td width="75%" align="center"><b><font size="2"># of Records that match</font></b></td>
</tr>
<tr>
<td width="25%"><%=zipcode_count%>&nbsp;</td>
</tr>
</table>

One point to remember is ALWAYS check what the user has entered into the textbox before inserting into your database. This will prevent SQL Injection attacks.

Code:

strZipcode = REPLACE(Request.Form("ZipBox"), "'", "''")

You can read more about SQL Injection attacks here:
http://www.sitepoint.com/article/sql...n-attacks-safe

I would also recommend, depending on what type of zipcodes you plan on accepting, check to make sure it's an integer, and limit the size of the textbox.

Your INSERT statment will only execute when the user has submitted your form. Therefore if they do not submit the form, it will not execute and the INSERT statement will not run.

What exact problem are you having with the INSERT statement?

**mikeistyke** · Jan 24, 2005 20:19

Thanks, Thing, for looking at that.
Here's what's going on now.
When the page loads, the value of all the records is displayed in the html form.
I'm doing something wrong.
I want to enter the zip in the box, have it update the Db, and then write back to the user the total of zipcodes that match theirs, like if they enter 90028, have the SQL COUNT all records that match 90028 ,and display that as a number in the html form.
Maybe what I want to do isn't so practical? I thought it would be kind of cool if people knew how many people on the site shared their zipcode....
Also, thanks for all your help. I'll have a look a that tutorial on sql injection as soon as i finish the stack in front of me! hehhehe

**Thing** · Jan 24, 2005 22:31

Your script is fine, just move your INSERT above the count:

Code:

If Request.Form("Submit") <> "" Then

Dim strZipBox
Dim strZipcode
Dim DataConnection
Dim SQL
Dim strUserInput
Dim strError1
Dim strError2

strZipcode = Request.Form("ZipBox")

strError1 = "* Max. 5 numbers"
strError2 = "* Min. 5 numbers"

If Len(strZipcode) > 5 Then
Response.Write (strError1)
ElseIf Len (strZipcode) <= 4 Then 
Response.Write (strError2)
Else

strUserInput = Replace(strUserInput, " ' " , " ' ' ")
dbPath = "c:\accounts\valleyba\wwwroot\HomeBaseDB.mdb"
db=server.mappath("HomeBaseDB.mdb") 
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

sSQL = " INSERT INTO Zipcodes (Zipcode) VALUES ( '" & Zipcode & " ')"
DataConnection.Execute sSQL
DataConnection.Close
Set DataConnection = Nothing

Response.Write "Record has been updated. Thank you"
End if
End IF

**mikeistyke** · Jan 25, 2005 08:58

Hey, Thing! Thanks, bro.
I stumbled on to something lat last night at the microsoft site. Acccidentally, of course. It's a script like what Fumbler was talking about. I can now validate any page with list boxes that accept data. Here's the link. Thank you guys a million!

http://msdn.microsoft.com/library/de...22828fc2c0.asp

User Tag List

Thread: sql query using COUNT

Thread Tools

Display

sql query using COUNT

Bookmarks

Bookmarks

Posting Permissions