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
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"
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))
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.
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?
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
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?
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)
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> </p>
</body>
</html>
<%
rsPostcode.Close()
Set rsPostcode = Nothing
%>
<%
rsDistance.Close()
Set rsDistance = Nothing
%>
<%
rsResults.Close()
Set rsResults = Nothing
%>
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.
Before you post, read our: 
