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.

 

[monksnake]

Hmm you may want to ask this question in the Access Database Tables forum. I'll bet that's a cake question for them.

I'm sorry I couldn't help.

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

 

[Tarwn]

Try using single quotes in your replace statement, the double quotes for most database designate a field name (kinda like the square brackets):

strsql = "SELECT * from tblLock where version = '" & selVersion & "' and REPLACE([country],[COLOR=red]' '[/color],[COLOR=red]''[/color]) = '" & Replace(selCountry," ","") & "'"

However I am still of the belief that the following should work given the values you posted in your first post:

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

If neither of these work, do a response.Write on your SQL string (followed by a response.Flush) and paste it into MS Access's query window. That should give you more information on why it isn't working.

-T

 

[malaygal]

1st code:
SELECT * from tblLock where version = 'Budget' and REPLACE([country],' ','') = 'HONG'
Microsoft JET Database Engine error '80040e14'

Undefined function 'REPLACE' in expression.

/countryform.asp, line 228


2nd code:

SELECT * from tblLock where [version] = 'Budget' and [country] = 'HONG'

 

[malaygal]

............................

country=trim(Request.Form("country")

<SELECT NAME="country" ONCHANGE="document.countryform.submit()">
<%

Set rsCountry = Server.CreateObject("ADODB.Recordset")
strsql = "SELECT distinct country from tblLock where version = '" & version & "' group by country "
rsCountry.Open strsql, objConn
' loop through the recordset...
if not rsCountry.eof then
rsCountry.movefirst
Do Until rsCountry.EOF
If rsCountry("country") <> country Then
%>
<OPTION VALUE =<%=rsCountry("country")%>><%=rsCountry("country")%></OPTION>
<%
else
%>
<OPTION VALUE =<%=rsCountry("country")%> selected><%=rsCountry("country")%></OPTION>
<%
end if
rsCountry.MoveNext
Loop
' finish the version listbox...
end if
rsCountry.close
%>

This is where I am getting the value of selCountry = trim(Request.Form("country").
Somehow, ONCHANGE="document.countryform.submit(), only passes the value 'HONG' to the page.

 

[Tarwn]

Only half of your "Hong Kong" is getting posted,. Generally this means you don't have quotes around the value in your HTML and it is assuming that only the characters up tot he space are supposed to be the value.

Looking at your code you acn see where this is happening:

   If  rsCountry("country") <> country  Then
%>
    <OPTION VALUE =<%=rsCountry("country")%>><%=rsCountry("country")%></OPTION>
<%
    else
%>
    <OPTION VALUE =<%=rsCountry("country")%> selected><%=rsCountry("country")%></OPTION>
<%
    end if

If you put quotes around the values your inserting into this section, then you will get the whole "Hong Kong" value:

   If  rsCountry("country") <> country  Then
%>
    <OPTION VALUE =[COLOR=red]"[/color]<%=rsCountry("country")%>[COLOR=red]"[/color]><%=rsCountry("country")%></OPTION>
<%
    else
%>
    <OPTION VALUE=[COLOR=red]"[/color]<%=rsCountry("country")%>[COLOR=red]"[/color] selected><%=rsCountry("country")%></OPTION>
<%
    end if

Now that you will be gettig the whole value, try the 2nd one again and see if you are still getting an error. I would suggest trying it with [] around the field names just to be sure your not using a reserved name as a column name.

-T

 

[malaygal]

That did the trick.
Thanks to you and everybody for all the help.