SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict mikeistyke's Avatar
    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....

  2. #2
    SitePoint Addict rokc's Avatar
    Join Date
    Oct 2004
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

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

    like this,

    Code:
    strZipcode = CInt(strZipcode)

  3. #3
    SitePoint Addict mikeistyke's Avatar
    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?

  4. #4
    Original Gangster silver trophy Thing's Avatar
    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"))

  5. #5
    SitePoint Addict
    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.

  6. #6
    SitePoint Addict mikeistyke's Avatar
    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?

  7. #7
    SitePoint Addict
    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 & "';
    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.

  8. #8
    SitePoint Addict mikeistyke's Avatar
    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...

  9. #9
    SitePoint Addict mikeistyke's Avatar
    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") %>&nbsp;</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.

  10. #10
    Original Gangster silver trophy Thing's Avatar
    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%>&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?

  11. #11
    SitePoint Addict mikeistyke's Avatar
    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

  12. #12
    Original Gangster silver trophy Thing's Avatar
    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

  13. #13
    SitePoint Addict mikeistyke's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •