SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: sql query using COUNT
-
Jan 24, 2005, 08:58 #1
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
sql query using COUNT
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....
-
Jan 24, 2005, 09:10 #2
hi,
try first converting the strZipcode value you get from text box to Integer..
like this,
Code:strZipcode = CInt(strZipcode)
-
Jan 24, 2005, 09:23 #3
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
After converting that...
strZipcode = CInt(strZipcode)
Do I request it like this?
CInt(strZipcode) = Request.Form("Zipcode")
Does that make sense?
-
Jan 24, 2005, 10:10 #4
- Join Date
- Oct 2000
- Location
- Philadelphia, PA
- Posts
- 4,708
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
No you convert while setting the variable:
Code:strZipcode = CInt(request.form("Zipcode"))
"Does this napkin smell like chloroform?"
...now with SnapFoo!
My Blog | My Twitter | My Company | SitePoint Podcast
*** Matt Mullenweg on the SitePoint Podcast ***
-
Jan 24, 2005, 10:36 #5
- Join Date
- Sep 2003
- Location
- Kingston
- Posts
- 246
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 24, 2005, 11:52 #6
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jan 24, 2005, 12:21 #7
- Join Date
- Sep 2003
- Location
- Kingston
- Posts
- 246
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It's simpler than that.
SELECT Count(Zipcodes.Zipcode) AS Zipcode FROM Zipcodes where zipcode = '" & strZipcode & "';
-
Jan 24, 2005, 12:25 #8
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Jan 24, 2005, 16:58 #9
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 24, 2005, 19:24 #10
- Join Date
- Oct 2000
- Location
- Philadelphia, PA
- Posts
- 4,708
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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%> </td> </tr> </table>
Code:strZipcode = REPLACE(Request.Form("ZipBox"), "'", "''")
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?"Does this napkin smell like chloroform?"
...now with SnapFoo!
My Blog | My Twitter | My Company | SitePoint Podcast
*** Matt Mullenweg on the SitePoint Podcast ***
-
Jan 24, 2005, 20:19 #11
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jan 24, 2005, 22:31 #12
- Join Date
- Oct 2000
- Location
- Philadelphia, PA
- Posts
- 4,708
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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
"Does this napkin smell like chloroform?"
...now with SnapFoo!
My Blog | My Twitter | My Company | SitePoint Podcast
*** Matt Mullenweg on the SitePoint Podcast ***
-
Jan 25, 2005, 08:58 #13
- Join Date
- Dec 2004
- Location
- staunton
- Posts
- 207
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks