Help with Replace() function 5

[malaygal]

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 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.

 

[DotNetGnat]

are you sure that this is not working:

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

-DNG

 

[mainmast]

Re-Write you SQL statement to this:

strSQL = "SELECT * FROM tblLock where Version = ['" & selVersion & "'] and County = ['" & selCountry & "']"

The added brackets will allow for spaces.

 

[monksnake]

The space in "HONG KONG" should have no bearing at all in retrieving the record. DotNetGnat's example should work just fine.

[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[monksnake]

As written, there is no variable named version according to your code, it's selVersion.

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

I know I'm not the first to notice that, but I figured I'd point it out, you could be possibly overlooking it.


[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

Thanks for the replies.

I tried both.

DotNetGnat,
Still did not return any record(s).

mainmast,

Got this error:

No value given for one or more required parameters.

BTW, the database is Access.

 

[malaygal]

monksnake, thanks for catching that. That should have been version = '" & selVersion & "'

 

[monksnake]

Are you sure there are not leading or trailing spaces in the data? Try this:

strsql = "SELECT * from tblLock where version = [!]ltrim(rtrim([/!]'" & selVersion & "'[!]))[/!] and country = [!]ltrim(rtrim([/!]'" & selCountry  & "'[!]))[/!]"

[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

I am pretty sure, there isn't because when I select another country (without space in between i.e India) from my dropdown, I got some records. I tried your sql string and no record(s) were returned.

 

[DotNetGnat]

monksnake, thanks for catching that. That should have been version = '" & selVersion & "'

did you check my post carefully...

any ways..try this

strsql = "SELECT * from tblLock where [version] = '" & trim(selVersion) & "' and country = '" & trim(selCountry) & "'"

-DNG

 

[monksnake]

Are you 100% sure that the record got sucessfully added to tblLock?

If it did get added, was it an escaped version? (i.e. put %20 for a space)

Are you checking the case sensitivity correctly??
(searching for "HONG KONG" but "Hong Kong" is in the table)

Without looking directly at the table, I'm not sure what to tell you, but I guess those few things are a start.


[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

DotNetGnat,

No luck.

 

[malaygal]

monksanke,

All the DB records are in uppercase. This includes the dropdown on my asp page.

 

[monksnake]

Maybe this will work?? Back to what you were originally trying to do:

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

I'm curious as to what the problem is.

[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

It did not work either:

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

-will translate to:

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

I googled it last night and found a link that would also replace the database record, something like this:

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

 

[malaygal]

BTW,

This is the error I was getting:

Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/countryform.asp, line 226, column 94
strsql = "SELECT * from tblLock where version = '" & selVersion & "' and Replace(country," ","") = '" & Replace(selCountry," ","") & "'"
---------------------------------------------------------------------------------------------^

 

[monksnake]

Have you taken a look at the database directly in Access and seen the table row with value country column = "HONG KONG" and version column = "Budget" ?

My only guess, since it works for other records (as you explained earler, with India) is that either that row with HONG KONG in it does not exist, or you are pulling an incorrect version value in.



[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

I run this from the SQL view of the Access DB and it did not return any records

SELECT *
FROM tblLock
WHERE version="Budget" And country = REPLACE("HONG KONG","HONG KONG","HONGKONG");

I run this and records were retrieved.

SELECT *
FROM tblLock
WHERE version="Budget" And REPLACE(country," ","") = REPLACE("HONG KONG","HONG KONG","HONGKONG");

I just need to translate it to an asp query string.

 

[monksnake]

In your query statement, change it up like this:

REPLACE([country]," ","")

bracket the column name.

[small]"King Coleman, live foreva!! Yeah buddy, light weight!!!"[/small]
<.

 

[malaygal]

I am getting this compilation error:

Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/DECIWEB/countryform.asp, line 222, column 96
strsql = "SELECT * from tblLock where version = '" & selVersion & "' and REPLACE([country]," ","") = '" & Replace(selCountry," ","") & "'"
-----------------------------------------------------------------------------------------------^