...

View Full Version : SQL query problem



russ21282
07-06-2005, 09:04 PM
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

Brandoe85
07-06-2005, 09:11 PM
Where is yrpostcode being set? And what happends when you response.write yrpostcode?

russ21282
07-06-2005, 09:23 PM
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"

Brandoe85
07-06-2005, 09:50 PM
Try writing it out in your loop, if indeed thats the first place your are giving it a value.

russ21282
07-06-2005, 10:06 PM
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))

Brandoe85
07-06-2005, 10:13 PM
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.

russ21282
07-06-2005, 10:25 PM
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.

Brandoe85
07-06-2005, 10:28 PM
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?

russ21282
07-06-2005, 10:35 PM
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

Brandoe85
07-06-2005, 10:37 PM
I mean the query for the rsDistance result set, where you're trying to get the rsDistance("Postcode") value.

russ21282
07-06-2005, 10:44 PM
<%
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

russ21282
07-09-2005, 12:54 AM
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?

miranda
07-09-2005, 03:50 PM
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)

russ21282
07-09-2005, 07:36 PM
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?



<%@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
%>

miranda
07-10-2005, 03:37 AM
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.

russ21282
07-11-2005, 06:39 PM
Right, I did a response.write for all the sql statements and then variables, everything seems in order until this part of the code:



Dim yrpostcode
Do While Not rsDistance.Eof
If rsDistance("postcode")<>"" Then
yrpostcode = yrpostcode &"'" & rsDistance("postcode")& "'" & ","
End If


So i'm guessing its pretty certain the problem is in this piece of code somewhere. the response.write for yrpostcode after this code doesn't return anything at all.

Any ideas anyone?

miranda
07-11-2005, 07:11 PM
Now do a response.Write(rsDistance("postcode")) Does that give you a value?

Also try changing the code to this

Dim yrpostcode : yrpostcode = ""
Do While Not rsDistance.Eof
If Len(rsDistance("postcode")) > 0 Then
yrpostcode = yrpostcode &"'" & rsDistance("postcode")& "'" & ","
End If

russ21282
07-11-2005, 11:24 PM
the response.Write(rsDistance("postcode")) doesn't produce a result either!
even with the code you did, it still doesn't have a value. i'm nearly on the verge of pulling my hair out over this!

do you have any suggestions why its not producing a value?

Thanks for all your help already

Russ

miranda
07-12-2005, 05:46 AM
ok the SQL query that gives you the postcode is the one you need to look at. So do this

Response.Write("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.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, "'", "''")

And show us what is output to the screen from that SQL statement

russ21282
07-12-2005, 03:25 PM
the output from that statement is:

SELECT * FROM postcode WHERE longitude BETWEEN -1.868 + 0.5 AND -1.868 - 0.5 AND latitude BETWEEN 52.903 + 0.5 AND 52.903 - 0.5

Brandoe85
07-12-2005, 03:36 PM
Now run that query against your db, in the mysql shell, phpmyadmin, or whatever you're using, and see what results are returned. Make sure it returns a row with a value in the postcode field.

russ21282
07-12-2005, 03:47 PM
I think i ran the query correctly, i put it into the console of dbmanager and took off the quotes either side and ran it.

in the results it it showed the table fields but with not results underneath.

have i run it in the correct way?

Brandoe85
07-12-2005, 03:57 PM
Copy and paste the output for us to see, but now that tells us your query isn't returning any rows. Try a Select * from postcode or something obvious that will show us if any values are indeed in the table.

russ21282
07-12-2005, 04:09 PM
select * from postcode does select all the records, so im guessing its the query thats not working.

does it matter that variables like rsDistance__long1 aren't defined in the dbmanager console. how does it know what the values of those are?

Brandoe85
07-12-2005, 04:12 PM
It doesn't need to, because you ran this query right?
SELECT * FROM postcode WHERE longitude BETWEEN -1.868 + 0.5 AND -1.868 - 0.5 AND latitude BETWEEN 52.903 + 0.5 AND 52.903 - 0.5
And it returned 0 rows?

russ21282
07-12-2005, 04:15 PM
My mistake, i ran the original one from the script. i've run the correct one though it still doesn't produce a result.

Brandoe85
07-12-2005, 04:43 PM
Ok that tells your your query is off, i'm not sure what the results are suppose to be but obviously not empty. Now do you still have your access database set up? If you do, go into access and run the same query:
SELECT * FROM postcode WHERE longitude BETWEEN -1.868 + 0.5 AND -1.868 - 0.5 AND latitude BETWEEN 52.903 + 0.5 AND 52.903 - 0.5

Does that return any rows?

russ21282
07-12-2005, 05:04 PM
i've run the same query in access and it returns what look like the correct results.

any ideas why it works in access but not mysql?

miranda
07-12-2005, 05:54 PM
what are the datatypes of the latitude and longitude fields?

russ21282
07-12-2005, 06:03 PM
the datatypes in the mySQL database are double, not entirely sure what that means!

It was originally from an access database though and was imported into mysql.

miranda
07-12-2005, 06:18 PM
The double datatype is a double-precision floating-point number. I just wanted to make sure that it was a numeric datatype. Since it is the query that isn't returning results, have you performed a query on that table to pull a result off of another field in that row? Look at what is returned in the latitude and longitude fields. Do they match what you are looking for?

russ21282
07-12-2005, 09:09 PM
I don't really understand what you mean sorry, nothing at all is returned when i've run the query in the mysql console. wen i tried the same query in the access database it pulled up all the correct records within the distance.

i ran another sql statement(from further up the page) in the console:

"SELECT * FROM postcode WHERE postcode LIKE '%" + left(rsPostcode__MMColParam, 4) + "%'"

and that brings up the correct record.

russ21282
07-13-2005, 08:44 PM
i have tried the query without the + and - 5's and it brings back 1 record, which is correct.

can anyone suggest how to rework the statement so it works with them in.

Brandoe85
07-13-2005, 11:25 PM
Post the exact query you ran that worked. Did you take out the adding and subtracting all together, or did you add and subtract first, then run the query with those values?

russ21282
07-14-2005, 02:04 AM
I just took them out completely to see if was that that was causing the problems. without them, it shows the one result, which is correct as the 0.5's represent within 50 miles. with these in it should bring up 20 or 30 postcode, the exact one and others within 50 miles.


SELECT * FROM postcode WHERE longitude BETWEEN -1.868 AND -1.868 AND latitude BETWEEN 52.903 AND 52.903

I also tried this code with the 0.5's back in:


SELECT * FROM postcode WHERE longitude BETWEEN '-1.868 + 0.50' AND '-1.868 - 0.5' AND latitude BETWEEN '52.903 + 0.5' AND '52.903 - 0.5'


This code brings up the same one result, so obviously isn't using the 0.5's.

Could it just be a case of how they are being called? as i've read that accessa nd mysql have slightly a different syntax in some areas.

Brandoe85
07-14-2005, 02:23 AM
Ok so the first query, without the .5's is suppose to return 1 row, but when you add the .5's it's suppose to return more than 1 row? When you have this:
-1.868 + 0.50
Are you meaning that to be added together, so it would be evaluated to -1.368?

russ21282
07-14-2005, 12:21 PM
yes, the -1.868 and 52.903 are the exact longitude and latitude co-ordinates of the postcode i typed in the search. on their own they bring up the correct postcode.

there is also a 'distance willing to travel' field on the form with 10, 20 30,40 ,50 miles. The + and - 0.5 is supposed to find all records that are 50 miles either side of the postcode typed in.

So the 0.5's need to be added and subtracted to/from the exact co-ordinates to give a kind of parameter for the results returned.

Hope this makes sense!

Brandoe85
07-14-2005, 03:34 PM
Ok, so why don't you try adding/subtracting the 0.5's before you run the query? So whenever the 0.5's condition is met, do the math and then run the query, so it'd be something like this:


SELECT * FROM postcode WHERE longitude BETWEEN -1.368 AND -2.368 AND latitude BETWEEN 53.403 AND 52.403

If i'm missunderstanding you let me know :)

miranda
07-14-2005, 05:50 PM
Have you tried putting the math part in parenthesis like so?



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, "'", "''") + ")"

this would make your sql query produce

SELECT * FROM postcode WHERE longitude BETWEEN (-1.868 + 0.50) AND (-1.868 - 0.5) AND latitude BETWEEN (52.903 + 0.5) AND (52.903 - 0.5)

russ21282
07-14-2005, 07:01 PM
It turned out just being the wrong way round! works like this

longitude BETWEEN -1.868 - 0.5 AND -1.868 + 0.5

I guess its just mysql's way of doing things!

However im now getting this problem, the error message i get is just there is an error in the sql statement around 'B1'

this is the response.write of the query.
SELECT * FROM Adverts WHERE Trim(Left(yrPostcode, 4)) IN (st14pf'B1','B10','B11','B12','B13'

could it be the beginning, should it not be like this ('st14pf','B1......etc

below is the code that produces those results.

yrpostcode = yrpostcode & "'" & rsDistance("postcode")& "'" & ","

Cheers for all the help :thumbsup:

Russ

russ21282
07-14-2005, 08:09 PM
i've been testing the query in dbmanager and this one works:



SELECT * FROM Adverts WHERE TRIM(LEFT(yrPostcode, 4)) IN ('st14pf','B1','B10','B11'.........etc


this is what the query in the script is producing:


SELECT * FROM Adverts WHERE TRIM(LEFT(yrPostcode, 4)) IN (st14pf'B1','B10','B11'..........etc


how can i get the extra quotation marks and a comma around the first result. ive tried putting them in this code:



yrpostcode = yrpostcode & "'" & rsDistance("postcode")& "'" & ","


but it adds them in for all the results, so the rest of the codes have 2 extra quotation marks and 1 extra comma and so doesn't work.

russ21282
07-16-2005, 02:05 PM
I really need to get this working, does anyone have any ideas!!?

This line defines yrpostcode:


yrpostcode = yrpostcode & "'" & rsDistance("postcode")& "'" & ","

and produces this: (response.write of yrpostcode)

(st14pf'B1','B10','B11','B12','B13','B14','B15','B16'..etc

however it needs to be like this to work:

('st14pf','B1','B10','B11','B12','B13','B14','B15','B16'

Does anyone one know how i can change the code to get the second lines of results?

Thanks

Russ

miranda
07-16-2005, 04:19 PM
concerning the st14pf part, is this always a fixed number of characters? also is the parenthesis always there?

If these are true then you can do something like this

dim str, changepart, theRest, changeStr
str = "(st14pf'B1','B10','B11','B12','B13','B14','B15','B16')"
' str = yrpostcode
changepart = Left(str,7)
changeStr = Right(changepart,6)
theRest = Right(str,Len(str)-7)
str = "(" & Replace(changeStr,changeStr,"'" & changeStr & "',") & theRest
Response.Write str
which will produce
('st14pf','B1','B10','B11','B12','B13','B14','B15','B16')

russ21282
07-17-2005, 02:40 PM
Thanks for that Miranda, however i now have it working, turned out to be a line of code i'd left in when i was trying something else!

It's taken the best part of a week to get this working, so i'm glad it's finally done.

Thanks for all your help guys :thumbsup:

Would have probably taken me 50 times longer without it!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum