Help with replace() function

[malaygal]

Somebody from the ASP forum recommended that I post this in here. Here is my original post: thread333-1339246

I have this database record from my tblLock:

Country Version Account
HONG KONG Budget A1111


I need to create a query string that will bring this into my asp page but because of the space I am not able to retrieve the record

selCountry = "HONG KONG"
selVersion = "Budget"

strsql = "SELECT * from tblLock where version = '" & version & "' and replace(country," ","") = '" & replace(selCountry," ","") & "'"




I have tried all these:

'strsql = "SELECT * from tblLock where version = '" & selVersion & "' and country = '" & selCountry & "' "
'strSQL = "SELECT * FROM tblLock where version = ['" & selVersion & "'] and country = ['" & selCountry & "']"
'strsql = "SELECT * from tblLock where version = ltrim(rtrim('" & selVersion & "')) and country = ltrim(rtrim('" & selCountry & "'))"
'strsql = "SELECT * from tblLock where [version] = '" & trim(version) & "' and country = '" & trim(selCountry) & "'"
'strsql = "SELECT * from tblLock where version = '" & selVersion & "' and Replace([country]," ","") = '" & Replace(selCountry," ","") & "'"
'strsql = "SELECT * from tblLock where version = '" & selVersion & "' and Replace(country," ","") = '" & Replace(selCountry," ","") & "'"

I did not have any problem if selCounty does not have any space in between (i.e CHINA)
with this:


strsql = "SELECT * from tblLock where version = '" & selVersion & "' and country = '" & selCountry & "' "

I have been struggling with this for two days now and I think it's about time I consult some professional.
Any help will be greatly appreciated.

 

[PHV]

Which error do you get with this ?
strsql = "SELECT * from tblLock WHERE version = '" & selVersion & "' AND country = '" & selCountry & "' "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[malaygal]

I do not get any error, but string that gets genererated (response.write) when selCountry = "HONK KONG" is:

SELECT * from tblLock WHERE version = 'Budget' AND country = 'HONG'

and of course there would be no record since the country is 'HONG KONG'

I do not have any problem with this, records are returned:

selCountry = "INDIA"

SELECT * from tblLock WHERE version = 'Budget' AND country = 'INDIA'

 

[Remou]

Where is selcountry coming from?

 

[PHV]

I'm with Remou: this is an ASP/HTML issue, not a SQL one ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886