• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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!
0
Nico2011
Asked:
Nico2011
1 Solution
 
keithslaterCommented:
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.
0
 
Nico2011Author Commented:
It's an integer... you can see the output of the code here: www.villasdirect.com/_admin/villalink/index.asp
0
 
keithslaterCommented:
What error is MySQL giving you?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Nico2011Author Commented:
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...
0
 
FugasCommented:
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.
0
 
hieloCommented:
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")
0
 
Nico2011Author Commented:
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?
0
 
Nico2011Author Commented:
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!
0
 
Nico2011Author Commented:
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!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now