Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL query problem

    Hi All,

    I'm working on a distance by postcode script, but have become stuck. it works fine using Access but not mySQL which i need. it seems to be having trouble picking up data from a defined variable within the sql statement below:

    strSQL = "SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 3)) IN ('" & yrpostcode & "') AND Selectmake = '" + Replace(rsResults__MMColParam, "'", "''") + "' AND " + Replace(rsResults__pricemin, "'", "''") + " <= Pricemax AND " + Replace(rsResults__pricemax, "'", "''") + " >= Pricemax"

    when i've put a response.write after the sql statement it produces this:

    SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 3)) IN ('') AND Selectmake = 'Renault' AND 500 <= Pricemax AND 17000 >= Pricemax

    As you can see where the value from yrpostcode should be, it's missing. does anyone have any ideas

    Cheers

    Russ

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Where is yrpostcode being set? And what happends when you response.write yrpostcode?

  • #3
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The code below is from where yrpostcode starts in the page, when ive tried to response.write yrpostcode it doesn't produce anything, just a blank screen.

    any ideas?


    Dim yrpostcode
    Do While Not rsDistance.Eof
    If rsDistance("Postcode")<>"" Then
    yrpostcode = yrpostcode &"'" & rsDistance("Postcode")& "'" & ","
    End If
    rsDistance.MoveNext()
    Loop
    yrpostcode = Left(yrpostcode, Len(yrpostcode-1))
    %>
    <%
    Dim rsResults
    Dim rsResults_numRows

    Set rsResults = Server.CreateObject("ADODB.Recordset")
    rsResults.ActiveConnection = MM_conNatCarSearch_STRING
    rsResults.Source = "SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 3)) IN ('" & yrpostcode & "') AND Selectmake = '" + Replace(rsResults__MMColParam, "'", "''") + "' AND " + Replace(rsResults__pricemin, "'", "''") + " <= Price AND " + Replace(rsResults__pricemax, "'", "''") + " >= Price"

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Try writing it out in your loop, if indeed thats the first place your are giving it a value.

  • #5
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It doesn't do anything when its placed in the loop, just goes straight to the 'there is no results to display' response.write. when i put it just after the loop its just a blank page.

    so im guessing that there's something wrong with the variable itself?

    are the lines highlighted below ok? ive marked out the second line and tried it and it does the same as it does with it so that doesn't seem to be having an effect on anything at the moment.

    Dim yrpostcode
    Do While Not rsDistance.Eof
    If rsDistance("Postcode")<>"" Then
    yrpostcode = yrpostcode &"'" & rsDistance("Postcode")& "'" & ","
    End If
    rsDistance.MoveNext()
    Loop
    yrpostcode = Left(yrpostcode, Len(yrpostcode-1))

  • #6
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Make sure your rsDistance("Postcode") has a value by writing that out in your loop, I'm assuming that it doesn't have anything in it, if it's not being put into your yrpostcode variable.

  • #7
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it doesn't work within the loop again and when placed outside it produces the error message:

    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

  • #8
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    If your rsDistance("Postcode") has no value, then you should check your table in your database that you are reading it from, it doesn't look like their is any data in that field. Whats the query you are running for that result set?

  • #9
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i'll check the database now. this is the result set query:

    strSQL = "SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 3)) IN ('" & yrpostcode & "') AND Selectmake = '" + Replace(rsResults__MMColParam, "'", "''") + "' AND " + Replace(rsResults__pricemin, "'", "''") + " <= Price AND " + Replace(rsResults__pricemax, "'", "''") + " >= Price"

    i have actually removed from dim yrpostcode until just before this qeury and it doesn't effect the results at all so i'm guessing none of it is actually doing anything!

    this is what it produces (with or without the yrpostcode, code before it)

    SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 3)) IN ('') AND Selectmake = 'Renault' AND 500 <= Price AND 20000 >= Price

  • #10
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I mean the query for the rsDistance result set, where you're trying to get the rsDistance("Postcode") value.

  • #11
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    <%
    Dim rsDistance
    Dim rsDistance_numRows

    Set rsDistance = Server.CreateObject("ADODB.Recordset")
    rsDistance.ActiveConnection = MM_conNatCarSearch_STRING
    rsDistance.Source = "SELECT * FROM postcode WHERE longitude BETWEEN " + Replace(rsDistance__long1, "'", "''") + " + " + Replace(rsDistance__dist, "'", "''") + " AND " + Replace(rsDistance__long1, "'", "''") + " - " + Replace(rsDistance__dist, "'", "''") + " AND latitude BETWEEN " + Replace(rsDistance__lat1, "'", "''") + " + " + Replace(rsDistance__dist, "'", "''") + " AND " + Replace(rsDistance__lat1, "'", "''") + " - " + Replace(rsDistance__dist, "'", "''") + ""
    rsDistance.CursorType = 0
    rsDistance.CursorLocation = 2
    rsDistance.LockType = 1
    rsDistance.Open()

    rsDistance_numRows = 0

  • #12
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i believe it is to do with this line of code:

    yrpostcode = Left(yrpostcode, Len(yrpostcode-1))

    originally the -1 was in between the last two brackets, like the code below. this worked when its been connected to an access database but when its connected to a mysql one it doesn't, it produces an error saying, Invalid procedure call or argument: 'left':

    yrpostcode = Left(yrpostcode, Len(yrpostcode)-1)

    is there a way to change it so i get the same result as the original code but works with mysql?

  • #13
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by russ21282
    i believe it is to do with this line of code:

    yrpostcode = Left(yrpostcode, Len(yrpostcode-1))

    originally the -1 was in between the last two brackets, like the code below. this worked when its been connected to an access database but when its connected to a mysql one it doesn't, it produces an error saying, Invalid procedure call or argument: 'left':
    yrpostcode = Left(yrpostcode, Len(yrpostcode)-1)

    is there a way to change it so i get the same result as the original code but works with mysql?

    You get the error Invalid procedure call or argument: 'left':because the string yrpostcode is empty. So if the string is empty then the string length is 0 and you end up with a negative number.

    also, you are not using the Len() function correctly when you have it like this Left(yrpostcode, Len(yrpostcode-1))
    The proper way to use the Len() function in the Left() function is to put the number to subtract outside of the Len function's parenthesis like so
    yrpostcode = Left(yrpostcode, Len(yrpostcode)-1)

  • #14
    New Coder
    Join Date
    Jul 2005
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Miranda, i found that out just after i made the post last night, which is typical! do you have any ideas of why yrpostcode isn't being assigned a value? I've put the whole page in below incase there's a problem earlier on in the script. i can't understand why yrpostcode doesn't have a value, as when its connected to an access database it obviously does because it works! Any ideas?

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="conNatCarSearch.asp" -->
    <%
    Dim rsPostcode__MMColParam
    rsPostcode__MMColParam = "1"
    If (Request.Form("yrpostcode")  <> "") Then 
      rsPostcode__MMColParam = Request.Form("yrpostcode") 
    End If
    %>
    <%
    Dim rsPostcode
    Dim rsPostcode_numRows
    
    Set rsPostcode = Server.CreateObject("ADODB.Recordset")
    rsPostcode.ActiveConnection = MM_conNatCarSearch_STRING
    rsPostcode.Source = "SELECT *  FROM postcode  WHERE postcode LIKE '%" + left(rsPostcode__MMColParam, 4) + "%'"
    rsPostcode.CursorType = 0
    rsPostcode.CursorLocation = 2
    rsPostcode.LockType = 1
    rsPostcode.Open()
    
    rsPostcode_numRows = 0-
    %>
    <%
    Dim rsDistance__long1
    rsDistance__long1 = "1"
    If (rsPostcode.Fields.Item("longitude").Value  <> "") Then 
      rsDistance__long1 = rsPostcode.Fields.Item("longitude").Value 
    End If
    %>
    <%
    Dim rsDistance__lat1
    rsDistance__lat1 = "1"
    If (rsPostcode.Fields.Item("latitude").Value  <> "") Then 
      rsDistance__lat1 = rsPostcode.Fields.Item("latitude").Value 
    End If
    %>
    <%
    Dim rsDistance__dist
    rsDistance__dist = "1"
    If (Request.Form("yrdistance")  <> "") Then 
      rsDistance__dist = Request.Form("yrdistance") 
    End If
    %>
    <%
    Dim rsDistance
    Dim rsDistance_numRows
    
    Set rsDistance = Server.CreateObject("ADODB.Recordset")
    rsDistance.ActiveConnection = MM_conNatCarSearch_STRING
    rsDistance.Source = "SELECT *  FROM postcode  WHERE longitude BETWEEN " + Replace(rsDistance__long1, "'", "''") + " + " + Replace(rsDistance__dist, "'", "''") + " AND " + Replace(rsDistance__long1, "'", "''") + " - " + Replace(rsDistance__dist, "'", "''") + " AND latitude BETWEEN " + Replace(rsDistance__lat1, "'", "''") + " + " + Replace(rsDistance__dist, "'", "''") + " AND " + Replace(rsDistance__lat1, "'", "''") + " - " + Replace(rsDistance__dist, "'", "''") + ""
    rsDistance.CursorType = 0
    rsDistance.CursorLocation = 2
    rsDistance.LockType = 1
    rsDistance.Open()
    
    rsDistance_numRows = 0
    %>
    <%
    Dim rsResults__MMColParam
    rsResults__MMColParam = "1"
    If (Request.Form("Selectmake")   <> "") Then 
      rsResults__MMColParam = Request.Form("Selectmake")  
    End If
    %>
    <%
    Dim yrpostcode
    Do While Not rsDistance.Eof
    If rsDistance("postcode")<>"" Then
    yrpostcode = yrpostcode &"'" & rsDistance("postcode")& "'" & ","
    End If
    rsDistance.MoveNext()
    Loop
    yrpostcode = Left(yrpostcode, Len(yrpostcode)-1)
    %>
    <%
    Dim rsResults
    Dim rsResults_numRows
    
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    rsResults.ActiveConnection = MM_conNatCarSearch_STRING
    rsResults.Source = "SELECT *  FROM Adverts  WHERE Trim(Left(yrPostcode, 4)) IN ('" & yrpostcode & "') AND Selectmake = '" + Replace(rsResults__MMColParam, "'", "''") + "'"
    
    rsResults.CursorType = 0
    rsResults.CursorLocation = 2
    rsResults.LockType = 1
    rsResults.Open()
    
    rsResults_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    rsResults_numRows = rsResults_numRows + Repeat1__numRows
    %>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    
    <body>
    <p><font color="#000066" size="4" face="Arial, Helvetica, sans-serif">Vehicle Search Results
      </font>
    </p>
    <table width="80%" border="0" cellspacing="0" cellpadding="0">
      <tr bgcolor="#FFCC00">
        <td width="22%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Make</font></div></td>
        <td width="22%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Model</font></div></td>
        <td width="8%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Year</font></div></td>
        <td width="12%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Colour</font></div></td>
        <td width="21%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Description</font></div></td>
        <td width="15%"><div align="center"><font color="#000033" size="3" face="Arial, Helvetica, sans-serif">Price</font></div></td>
      </tr>
      <% 
    While ((Repeat1__numRows <> 0) AND (NOT rsResults.EOF)) 
    %>
      <tr>
        <td><div align="center"><%=(rsResults.Fields.Item("Selectmake").Value)%></div>
        </td>
        <td><div align="center"><%=(rsResults.Fields.Item("Model").Value)%></div>
        </td>
        <td><div align="center"><%=(rsResults.Fields.Item("Year").Value)%></div>
        </td>
        <td><div align="center"><%=(rsResults.Fields.Item("Colour").Value)%></div>
        </td>
        <td><div align="center"><%=(rsResults.Fields.Item("Notes").Value)%></div>
        </td>
        <td><div align="center"><%=(rsResults.Fields.Item("Price").Value)%></div>
        </td>
      </tr>
      <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsResults.MoveNext()
    Wend
    %>
    
    </table>
    <p>&nbsp;</p>
    </body>
    </html>
    <%
    rsPostcode.Close()
    Set rsPostcode = Nothing
    %>
    <%
    rsDistance.Close()
    Set rsDistance = Nothing
    %>
    <%
    rsResults.Close()
    Set rsResults = Nothing
    %>

  • #15
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Try to do a response.Write on each of your sql SELECT querys. like so
    SQL = "SELECT * FROM postcode WHERE postcode LIKE '%" + left(rsPostcode__MMColParam, 4) + "%'"
    Response.Write(SQL)
    rsPostcode.Source = SQL

    Next, do a response.Write on each of the variables you use. At some point something is not going to have a value. If you still need help figuring it out after that show us the values that appear on the screen in the response.Write.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

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