asked on

MYSQL Not showing all Data...

Hello Experts,

I am trying to copy data from an MYSQL DB to an MSSQL DB, however when I run this and look at the results, the SQL statements produced stop picking up values just after the 'pet_friendly' value. I have written the following code (it's my first time using MYSQL):

Set TVLRS = Conn.Execute("Select * from properties order by PropID")
While NOT TVLRS.EOF
		'get and format data from villalink
		OriginalID = TVLRS("PropId")
		your_ref = TVLRS("RefNo")
		property_description = TVLRS("description") & "<BR>&nbsp;<BR>" & TVLRS("notes")
		property_description = replace(property_description, "'", "''")
		
		If TVLRS("handicap") = NULL then 
		disabled_access = "0"
		Else
			If left(TVLRS("handicap"), 1) = "Y" then
			disabled_access = "1"
			else
			disabled_access = "0"
			end if
		end if
		
		Set TownRS = Conn.Execute("Select * from towns where townID = " & TVLRS("TownID"))
		Town = replace(TownRS("town"), "'", "''")
		CountryID = TownRS("CountryID")
		TownRS.Close
		Set TownRS = Nothing
		
		Set CountryRS = Conn.Execute("Select country from countries where countryID = " & CountryID)
		Country = CountryRS("country")
		CountryRS.Close
		Set CountryRS = Nothing
		
		bedrooms = TVLRS("NoOfBedrooms")
		sleeps = TVLRS("sleepsmax")
		
		If TVLRS("AirCon") = NULL then 
		air_conditioning = "0"
		else
			If left(TVLRS("aircon"), 1) = "N" then
			air_conditioning = "0"
			else
			air_conditioning = "1"
			end if
		end if
		
		If TVLRS("pool") = NULL then
		pool = "0"
		else
			if left(TVLRS("pool"), 1) <> "S" then
			pool = "1"
			else
			pool = "0"
			end if
		end if
		
		if TVLRS("entertainment") <> NULL then
		tv = "1"
		else
		tv = "0"
		end if
		
		if TVLRS("internet") = NULL then
		internet = "0"
		else
			if left(TVLRS("internet"), 1) <> "N" then
			internet = "1"
			else
			internet = "0"
			end if
		end if
		
		If TVLRS("children") = NULL then
		child_friendly = 0
		else
			if left(TVLRS("children"), 1) = "Y" then
			child_friendly = 1
			else
			child_friendly = 0
			end if
		end if
		
		if TVLRS("included") = NULL then
		linen = "0"
		else
			if left(TVLRS("included"), 1) = "N" then
			linen = "0"
			else
			linen = "1"
			end if
		end if
		
		beach = "1"
		IF TVLRS("Online") = -1 then
		show_on_site = 1
		else
		show_on_site = 0
		end if
		
		If TVLRS("animals") = NULL then
		Pet_friendly = 0
		else
			If Left(TVLRS("animals"), 1) = "N" then
			pet_friendly = 0
			else
			pet_friendly = 1
			end if
		end if
		
		if TVLRS("LowPrice") <> NULL then
		price_low = TVLRS("LowPrice")
		else
		price_low = 0
		end if
		
		if TVLRS("HighPrice") <> NULL then
		price_high = TVLRS("HighPrice")
		else
		price_high = 0
		end if
		
		If TVLRS("GFSize") <> NULL then
		desc_exterior = "Garden Size: " & replace(TVLRS("GFSize"), "'", "''")
		else
		desc_exterior = ""
		end if
		
		If TVLRS("terracesize") <> NULL then
		desc_exterior = desc_exterior & "<BR>Terrace: " & replace(TVLRS("terracesize"), "'", "''")
		end if
		
		if TVLRS("outdoordining") <> NULL then
		desc_exterior = desc_exterior & "<BR>Outdoor Dining: " & replace(TVLRS("outdoordining"), "'", "''")
		end if
		
		if TVLRS("parking") <> NULL then
		desc_exterior = desc_exterior & "<BR>Parking: " & Replace(TVLRS("parking"), "'", "''")
		end if
		
		if TVLRS("livingroom") <> NULL then
		desc_living = replace(TVLRS("livingroom"), "'", "''")
		else
		desc_living = ""		
		end if
		
		If TVLRS("diningarea") <> NULL then
		desc_dining = replace(TVLRS("diningarea"), "'","''")
		else
		desc_dining = ""
		end if
		
		if TVLRS("kitchen") <> NULL then
		desc_kitchen = replace(TVLRS("kitchen"), "'", "''")
		else
		desc_kitchen = ""
		end if
		
		if TVLRS("bedroomdetails") <> NULL then
		desc_bedrooms = TVLRS("bedroomdetails")
		else
		desc_bedrooms = ""
		end if
		
		if TVLRS("bathroomdetails") <> NULL then
		desc_bathrooms = TVLRS("bathroomdetails")
		else
		desc_bathrooms = ""
		end if
		
		if TVLRS("nearestbeach") <> NULL then
		desc_beach = TVLRS("nearestbeach")
		else
		desc_beach = ""
		end if
		
		If TVLRS("livingspace") <> NULL then
		livingarea = TVLRS("livingspace")
		else
		livingarea = ""
		end if
		
		Set RS = DB.Execute("Select * from Villas Where OriginalID = " & TVLRS("PropID"))
		If RS.EOF then
		SQL = "Insert INTO Villas (client_ID, your_ref, property_description, disabled_access, town, " 												        
		SQL = SQL & "country, bedrooms, sleeps, air_conditioning, pool, tv, internet, child_friendly, "
		SQL = SQL & "linen, beach, property_description, show_on_site, "
		SQL = SQL & "price_low, price_high, rental_currency, pet_friendly, "
		SQL = SQL & "desc_exterior, desc_living, desc_dining, desc_kitchen, desc_bedrooms, "
		SQL = SQL & "desc_bathrooms, desc_beach, SalesCommission, OwnerName, OriginalID, livingArea)"
		SQL = SQL & " VALUES (6610, '" & your_ref & "','" & property_description & "','" 
		SQL = SQL & disabled_access & "','" & town & "','" & country & "'," & bedrooms & "," & sleeps
		SQL = SQL & ",'" & air_conditioning & "','" & pool & "','" & tv & "','" & internet & "'," & child_friendly
		SQL = SQL & ",'" & linen & "','" & beach & "'," & show_on_site & ","
		SQL = SQL & price_low & "," & price_high & "," 
		SQL = SQL & "'EUR'," & pet_friendly & ",'" & desc_exterior & "','"
		SQL = SQL & desc_living & "','" & desc_dining & "','" & desc_kitchen & "','" & desc_bedrooms
		SQL = SQL & "','" & desc_bathrooms & "','" & desc_beach & "',2.5,'Pia'," & OriginalID
		SQL = SQL & ",'" & livingArea & "')"
		Response.write(SQL & "<HR>")
		response.Flush()
		Else
		End If
		RS.Close
		Set RS = Nothing
TVLRS.MoveNext
Wend

Open in new window

HELP Appreciated - thanks!

keithslater

What data type is pet_friendly in your database? If it's something like a char or varchar, you need to wrap pet_friend in quotes.

Nico2011

ASKER

It's an integer... you can see the output of the code here: www.villasdirect.com/_admin/villalink/index.asp

keithslater

What error is MySQL giving you?

Nico2011

ASKER

No error - I'm not updating the db until I see a correct query. The query doesn't have all the data yet so decided to find out before I write to the db...

Fugas

Hi, first I'll write the output with Server.HTMLEncode function, for to be sure those descriptions don't have some tags. I don't understand the condition after last select "If RS.EOF then", shouldn't there be something like while not RS.EOF? Be sure, when no records selected, BOF and EOF are True.

hielo

try changing:

Set TownRS = Conn.Execute("Select * from towns where townID = " & TVLRS("TownID"))
            Town = replace(TownRS("town"), "'", "''")
            CountryID = TownRS("CountryID")

to:
Set TownRS = Conn.Execute("Select * from towns where townID = " & TVLRS("TownID"))

Town=""
If TownRS("town") <> Null Then
            Town = replace(TownRS("town"), "'", "''")
End If
            CountryID = TownRS("CountryID")

Nico2011

ASKER

Thanks but that's not it either. Could it be how I am reading the field data? Some are varchar and some text - do I need to call them in different ways?

ASKER CERTIFIED SOLUTION

Nico2011

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nico2011

ASKER

I have worked it out. The problem was that I was stating if TVLRS("field") <> NULL, but these fields don't have a value NULL as they are text fields, so I had to assign the value of the field to a variable, then check if the variable was empty.

Thanks for trying to help though!