Invalid Column Name 1

[damipera]

hi guys, i have the code below in asp and it's having some error that says: invalid column name 'text here is the content of a field'. the column for 'Country' is in varchar and i don't know if it has something to do with it. fruitid is in int data type. i read somewhere that it has to do with the ". that it needs to be ' on the sql statement, ive tried it but it's just commenting out the whole sql statement. do you have other ideas?

'
set myRS = Server.CreateObject("ADODB.Recordset")
myRS.ActiveConnection = MM_myTestConn_STRING
myRS.Source = "SELECT fruitID,Country FROM fruitCountryTable where Country=" & Request("countryCode") & " and fruitID=" & myarray(i)
myRS.CursorType = 0
myRS.CursorLocation = 2
myRS.LockType = 3
myRS.Open()
'

 

[gmmastros]

try this:

myRS.Source = "SELECT fruitID,Country FROM fruitCountryTable where Country=[!]'[/!]" & [!]Replace([/!]Request("countryCode")[!], "'", "''")[/!] & "[!]'[/!] and fruitID=" & myarray(i)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[damipera]

thanks a lot gmmastros, it worked like a charm!!!!