Server.URLEncode :-(

[Fawke101]

Re: Server.URLEncod e :-(

OK, on page 1, when i click the link that has the apostrophy in -

******
<a href= "summary_manage r.asp?manager=< %=RS("manager") %>"><%[color=blue][color=green]
>> =RS("manager")% ></a>[/color][/color]
******

I get a invalid syntax near 'S' error. this is the link that links to page
2, but only the one with the apostrophy is erroring.

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Randy Rahbar" <rvrahbar@_JUNK ETY_JUNK_hotmai l.com> wrote in message
news:uW$9$PaDEH A.1604@TK2MSFTN GP11.phx.gbl...[color=blue]
> Fawke, which page - and which line - are you actually getting an error on?
>
>
>
> "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> news:%23q5aJtZD EHA.624@TK2MSFT NGP10.phx.gbl.. .[color=green]
> > Note i havent implemented the function or the Server URLEncode because[/color][/color]
it[color=blue][color=green]
> > doesnt work, this is my code as it stands now.
> >
> > ****Summary_Are a.asp(Page1)*** ***
> >
> > <%Dim RS
> >
> > Function sql_quote (str)
> > sql_quote = Replace(str,"'" ,"''")
> > End Function
> >
> > Set RS = DataConnection. Execute("SELECT manager, customer, income, FROM
> > vw_Summary_Area WHERE vw_Summary_Area .Area='" & Session("lstAre a") &[/color][/color]
"'")[color=blue][color=green]
> > do until RS.EOF
> > %>
> > <a href= "summary_manage r.asp?manager=< %=RS("manager") %>"><%
> > =RS("manager")% ></a>
> >
> > <% =RS("customer") %>
> >
> > <% =formatcurrency (RS("income"),2 )%>
> >
> > <% RS.MoveNext
> > Loop %>
> >
> > *************** *************** **
> > ****Summary_Man ager.asp(Page2) ****
> >
> > <%Dim RS
> >
> > Function sql_quote (str)
> > sql_quote = Replace(str,"'" ,"''")
> > End Function
> >
> > Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,[/color]
> income[color=green]
> > FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> > request.queryst ring("manager") & "'")
> > do until RS.EOF
> >
> > <a href=
> >[/color]
>[/color]
"summary_assist ant_manager.asp ?assistant_mana ger=<%=RS("assi stant_manager") %[color=blue][color=green][color=darkred]
> > >"><% =RS ("assistant_man ager")%></a>[/color]
> >
> > <% =RS("customer") %>
> >
> > <% =formatcurrency (RS("income"),2 )%>
> >
> > <% RS.MoveNext
> > Loop %>
> >
> > *************** *************** *
> > To get to page1 you select an area from a list box and click submit.[/color][/color]
data[color=blue]
> is[color=green]
> > arranged in accordance to the selection. A list of managers are listed[/color][/color]
for[color=blue][color=green]
> > that area, along with their customers and income. If you click on the
> > manager you go to page 2 and get a breakdown of the assistant manager/s[/color]
> for[color=green]
> > that manager and so on and so on.......
> >
> > I hope this has made it a bit clearer.
> >
> > --
> > Thanks in advance
> >
> > Fawke
> >
> > Please remove ANTI and SPAM
> > from my email address before emailing me.
> >
> > www.bradflack.com
> > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > news:#Ev60sUDEH A.3692@tk2msftn gp13.phx.gbl...[color=darkred]
> > > jesus man, i didnt mean to annoy anyone.
> > > Il post the "actual" code.
> > >
> > > The reason that the 2 select statements differ, is because the data is
> > > coming from a different view etc.
> > > On page1.asp the data comes from view1 etc. The value of that data is[/color][/color]
> put[color=green][color=darkred]
> > > into a querystring in order for page2.asp (which gets its data from[/color]
> > ANOTHER[color=darkred]
> > > view - view2) to proccess the data.
> > > I will paste my code up here without the naming ammendments, just[/color][/color]
> thought[color=green][color=darkred]
> > > it'd be easier (clearly not).
> > >
> > > Apologies once again for annoying you guys
> > >
> > > --
> > > Thanks in advance
> > >
> > > Fawke
> > >
> > > Please remove ANTI and SPAM
> > > from my email address before emailing me.
> > >
> > > www.bradflack.com
> > > "Aaron Bertrand [MVP]" <aaron@TRASHasp faq.com> wrote in message
> > > news:ei8lJuSDEH A.3404@TK2MSFTN GP10.phx.gbl...
> > > > I think if Fawke101 were to use real column/parameter names instead[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > > kludging it up with this COL1/COL2 nonsense we'd get to the bottom[/color][/color][/color]
of[color=blue][color=green]
> > this[color=darkred]
> > > a
> > > > lot quicker.
> > > >
> > > > --
> > > > Aaron Bertrand
> > > > SQL Server MVP
> > > > http://www.aspfaq.com/
> > > >
> > > >
> > > > "Peter Foti" <peter@Idontwan tnostinkingemai lfromyou.com> wrote in[/color]
> > message[color=darkred]
> > > > news:105jum4k6j 5jb6e@corp.supe rnews.com...
> > > > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > > > news:ejFaFVRDEH A.2600@TK2MSFTN GP09.phx.gbl...
> > > > > > that doesnt work tho, it completely ignores it and still[/color][/color][/color]
generates[color=blue]
> a[color=green]
> > '[color=darkred]
> > > > > > i cant get this damn thing to work, its not your fault, im sure[/color][/color][/color]
im[color=blue][color=green][color=darkred]
> > > doing
> > > > > > something wrong here.
> > > > > >
> > > > > > The page with the link (with the apostrophy) -
> > > > > > ****page1.asp** **
> > > > > >
> > > > > > Function SQL_QUOTE (str)
> > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > End Function
> > > > > >
> > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view1[/color][/color]
> WHERE[color=green][color=darkred]
> > > > > > view1.COL3='" & SQL_QUOTE(sessi on("lstbox")) & "'")
> > > > > >
> > > > > > 'incorrect link looks like - St Mathew's - when clicked -
> > > > > >
> > > > > > <a href= "page2.asp?VALU E=<%[/color][/color][/color]
=server.urlenco de(RS("value1") )%>"><%[color=blue][color=green][color=darkred]
> > > > > > =RS("value1")%> </a>
> > > > > >
> > > > > > *************** *
> > > > > > Then on page2.asp
> > > > > >
> > > > > > Function SQL_QUOTE (str)
> > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > End Function
> > > > > >
> > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view2[/color][/color]
> WHERE[color=green][color=darkred]
> > > > > > view1.COL3='" & SQL_QUOTE(reque st.querystring( "value1")) & "'")
> > > > > > *************** *
> > > > > > and so on like that........... ..............
> > > > >
> > > > >
> > > > > You're making this difficult to debug... which is the correct
> > > querystring
> > > > > variable name, "value" or "value1"? On page1.asp you called it[/color]
> > "value"[color=darkred]
> > > > and
> > > > > on page 2 you are trying to reference "value1". In addition, you[/color][/color]
> used[color=green][color=darkred]
> > > the
> > > > > fieldnames "COL1" and "COL2" in the query above that. WHICH IS[/color][/color]
> IT???[color=green][color=darkred]
> > > > Also,
> > > > > your query on page2.asp is pulling from table "view2", but using a[/color]
> > value[color=darkred]
> > > > > from "view1"???? Are these cut and paste errors?
> > > > >
> > > > > Peter
> > > > > PS- Odd naming convention (giving the sql_quote function all[/color][/color]
> uppercase[color=green][color=darkred]
> > > > > letters... very strange).
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Randy Rahbar]

Re: Server.URLEncod e :-(

I know this was mentioned in an earlier post and you said it didn't work,
but did you replace apostrophes with double quotes in all your sql
statements? Try this...

On page 1, change this...

Set RS = DataConnection. Execute("SELECT manager, customer, income, FROM
vw_Summary_Area WHERE vw_Summary_Area .Area='" & Session("lstAre a") & "'")

to...

Set RS = DataConnection. Execute("SELECT manager, customer, income, FROM
vw_Summary_Area WHERE vw_Summary_Area .Area='" &
replace(Session ("lstArea"),"'" ,"''") & "'")

On page 2, change this...

Set RS = DataConnection. Execute("SELECT assistant_manag er, customer, income
FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
request.queryst ring("manager") & "'")

to...

Set RS = DataConnection. Execute("SELECT assistant_manag er, customer, income
FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
replace(request .querystring("m anager"),"'","' '") & "'")


Randy

"Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:%23tm71eaD EHA.3852@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> OK, on page 1, when i click the link that has the apostrophy in -
>
> ******
> <a href= "summary_manage r.asp?manager=< %=RS("manager") %>"><%[color=green][color=darkred]
> >> =RS("manager")% ></a>[/color][/color]
> ******
>
> I get a invalid syntax near 'S' error. this is the link that links to page
> 2, but only the one with the apostrophy is erroring.
>
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
> "Randy Rahbar" <rvrahbar@_JUNK ETY_JUNK_hotmai l.com> wrote in message
> news:uW$9$PaDEH A.1604@TK2MSFTN GP11.phx.gbl...[color=green]
> > Fawke, which page - and which line - are you actually getting an error[/color][/color]
on?[color=blue][color=green]
> >
> >
> >
> > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > news:%23q5aJtZD EHA.624@TK2MSFT NGP10.phx.gbl.. .[color=darkred]
> > > Note i havent implemented the function or the Server URLEncode because[/color][/color]
> it[color=green][color=darkred]
> > > doesnt work, this is my code as it stands now.
> > >
> > > ****Summary_Are a.asp(Page1)*** ***
> > >
> > > <%Dim RS
> > >
> > > Function sql_quote (str)
> > > sql_quote = Replace(str,"'" ,"''")
> > > End Function
> > >
> > > Set RS = DataConnection. Execute("SELECT manager, customer, income,[/color][/color][/color]
FROM[color=blue][color=green][color=darkred]
> > > vw_Summary_Area WHERE vw_Summary_Area .Area='" & Session("lstAre a") &[/color][/color]
> "'")[color=green][color=darkred]
> > > do until RS.EOF
> > > %>
> > > <a href=[/color][/color][/color]
"summary_manage r.asp?manager=< %=RS("manager") %>"><%[color=blue][color=green][color=darkred]
> > > =RS("manager")% ></a>
> > >
> > > <% =RS("customer") %>
> > >
> > > <% =formatcurrency (RS("income"),2 )%>
> > >
> > > <% RS.MoveNext
> > > Loop %>
> > >
> > > *************** *************** **
> > > ****Summary_Man ager.asp(Page2) ****
> > >
> > > <%Dim RS
> > >
> > > Function sql_quote (str)
> > > sql_quote = Replace(str,"'" ,"''")
> > > End Function
> > >
> > > Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,[/color]
> > income[color=darkred]
> > > FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> > > request.queryst ring("manager") & "'")
> > > do until RS.EOF
> > >
> > > <a href=
> > >[/color]
> >[/color]
>[/color]
"summary_assist ant_manager.asp ?assistant_mana ger=<%=RS("assi stant_manager") %[color=blue][color=green][color=darkred]
> > > >"><% =RS ("assistant_man ager")%></a>
> > >
> > > <% =RS("customer") %>
> > >
> > > <% =formatcurrency (RS("income"),2 )%>
> > >
> > > <% RS.MoveNext
> > > Loop %>
> > >
> > > *************** *************** *
> > > To get to page1 you select an area from a list box and click submit.[/color][/color]
> data[color=green]
> > is[color=darkred]
> > > arranged in accordance to the selection. A list of managers are listed[/color][/color]
> for[color=green][color=darkred]
> > > that area, along with their customers and income. If you click on the
> > > manager you go to page 2 and get a breakdown of the assistant[/color][/color][/color]
manager/s[color=blue][color=green]
> > for[color=darkred]
> > > that manager and so on and so on.......
> > >
> > > I hope this has made it a bit clearer.
> > >
> > > --
> > > Thanks in advance
> > >
> > > Fawke
> > >
> > > Please remove ANTI and SPAM
> > > from my email address before emailing me.
> > >
> > > www.bradflack.com
> > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > news:#Ev60sUDEH A.3692@tk2msftn gp13.phx.gbl...
> > > > jesus man, i didnt mean to annoy anyone.
> > > > Il post the "actual" code.
> > > >
> > > > The reason that the 2 select statements differ, is because the data[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > > coming from a different view etc.
> > > > On page1.asp the data comes from view1 etc. The value of that data[/color][/color][/color]
is[color=blue][color=green]
> > put[color=darkred]
> > > > into a querystring in order for page2.asp (which gets its data from
> > > ANOTHER
> > > > view - view2) to proccess the data.
> > > > I will paste my code up here without the naming ammendments, just[/color]
> > thought[color=darkred]
> > > > it'd be easier (clearly not).
> > > >
> > > > Apologies once again for annoying you guys
> > > >
> > > > --
> > > > Thanks in advance
> > > >
> > > > Fawke
> > > >
> > > > Please remove ANTI and SPAM
> > > > from my email address before emailing me.
> > > >
> > > > www.bradflack.com
> > > > "Aaron Bertrand [MVP]" <aaron@TRASHasp faq.com> wrote in message
> > > > news:ei8lJuSDEH A.3404@TK2MSFTN GP10.phx.gbl...
> > > > > I think if Fawke101 were to use real column/parameter names[/color][/color][/color]
instead[color=blue]
> of[color=green][color=darkred]
> > > > > kludging it up with this COL1/COL2 nonsense we'd get to the bottom[/color][/color]
> of[color=green][color=darkred]
> > > this
> > > > a
> > > > > lot quicker.
> > > > >
> > > > > --
> > > > > Aaron Bertrand
> > > > > SQL Server MVP
> > > > > http://www.aspfaq.com/
> > > > >
> > > > >
> > > > > "Peter Foti" <peter@Idontwan tnostinkingemai lfromyou.com> wrote in
> > > message
> > > > > news:105jum4k6j 5jb6e@corp.supe rnews.com...
> > > > > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > > > > news:ejFaFVRDEH A.2600@TK2MSFTN GP09.phx.gbl...
> > > > > > > that doesnt work tho, it completely ignores it and still[/color][/color]
> generates[color=green]
> > a[color=darkred]
> > > '
> > > > > > > i cant get this damn thing to work, its not your fault, im[/color][/color][/color]
sure[color=blue]
> im[color=green][color=darkred]
> > > > doing
> > > > > > > something wrong here.
> > > > > > >
> > > > > > > The page with the link (with the apostrophy) -
> > > > > > > ****page1.asp** **
> > > > > > >
> > > > > > > Function SQL_QUOTE (str)
> > > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > > End Function
> > > > > > >
> > > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view1[/color]
> > WHERE[color=darkred]
> > > > > > > view1.COL3='" & SQL_QUOTE(sessi on("lstbox")) & "'")
> > > > > > >
> > > > > > > 'incorrect link looks like - St Mathew's - when clicked -
> > > > > > >
> > > > > > > <a href= "page2.asp?VALU E=<%[/color][/color]
> =server.urlenco de(RS("value1") )%>"><%[color=green][color=darkred]
> > > > > > > =RS("value1")%> </a>
> > > > > > >
> > > > > > > *************** *
> > > > > > > Then on page2.asp
> > > > > > >
> > > > > > > Function SQL_QUOTE (str)
> > > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > > End Function
> > > > > > >
> > > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view2[/color]
> > WHERE[color=darkred]
> > > > > > > view1.COL3='" & SQL_QUOTE(reque st.querystring( "value1")) &[/color][/color][/color]
"'")[color=blue][color=green][color=darkred]
> > > > > > > *************** *
> > > > > > > and so on like that........... ..............
> > > > > >
> > > > > >
> > > > > > You're making this difficult to debug... which is the correct
> > > > querystring
> > > > > > variable name, "value" or "value1"? On page1.asp you called it
> > > "value"
> > > > > and
> > > > > > on page 2 you are trying to reference "value1". In addition,[/color][/color][/color]
you[color=blue][color=green]
> > used[color=darkred]
> > > > the
> > > > > > fieldnames "COL1" and "COL2" in the query above that. WHICH IS[/color]
> > IT???[color=darkred]
> > > > > Also,
> > > > > > your query on page2.asp is pulling from table "view2", but using[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > value
> > > > > > from "view1"???? Are these cut and paste errors?
> > > > > >
> > > > > > Peter
> > > > > > PS- Odd naming convention (giving the sql_quote function all[/color]
> > uppercase[color=darkred]
> > > > > > letters... very strange).
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Fawke101]

Re: Server.URLEncod e :-(

Hi, thanks for that.

I have tried this and it doesnt work, the incorrect syntax error still
appears as does the apostrophy.
Does it make a difference that its the "manager" column that contains the
apostrophy? as the code you gave seems to replace the "Area" part of it?
Can i incorporate a replace function into the Hyperlink/URL?

Not sure why its not working, did exacly what you said

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Randy Rahbar" <rvrahbar@_JUNK ETY_JUNK_hotmai l.com> wrote in message
news:#wJaCtaDEH A.3256@TK2MSFTN GP09.phx.gbl...[color=blue]
> I know this was mentioned in an earlier post and you said it didn't work,
> but did you replace apostrophes with double quotes in all your sql
> statements? Try this...
>
> On page 1, change this...
>
> Set RS = DataConnection. Execute("SELECT manager, customer, income, FROM
> vw_Summary_Area WHERE vw_Summary_Area .Area='" & Session("lstAre a") & "'")
>
> to...
>
> Set RS = DataConnection. Execute("SELECT manager, customer, income, FROM
> vw_Summary_Area WHERE vw_Summary_Area .Area='" &
> replace(Session ("lstArea"),"'" ,"''") & "'")
>
> On page 2, change this...
>
> Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,[/color]
income[color=blue]
> FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> request.queryst ring("manager") & "'")
>
> to...
>
> Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,[/color]
income[color=blue]
> FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> replace(request .querystring("m anager"),"'","' '") & "'")
>
>
> Randy
>
> "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> news:%23tm71eaD EHA.3852@TK2MSF TNGP10.phx.gbl. ..[color=green]
> > OK, on page 1, when i click the link that has the apostrophy in -
> >
> > ******
> > <a href= "summary_manage r.asp?manager=< %=RS("manager") %>"><%[color=darkred]
> > >> =RS("manager")% ></a>[/color]
> > ******
> >
> > I get a invalid syntax near 'S' error. this is the link that links to[/color][/color]
page[color=blue][color=green]
> > 2, but only the one with the apostrophy is erroring.
> >
> > --
> > Thanks in advance
> >
> > Fawke
> >
> > Please remove ANTI and SPAM
> > from my email address before emailing me.
> >
> > www.bradflack.com
> > "Randy Rahbar" <rvrahbar@_JUNK ETY_JUNK_hotmai l.com> wrote in message
> > news:uW$9$PaDEH A.1604@TK2MSFTN GP11.phx.gbl...[color=darkred]
> > > Fawke, which page - and which line - are you actually getting an error[/color][/color]
> on?[color=green][color=darkred]
> > >
> > >
> > >
> > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > news:%23q5aJtZD EHA.624@TK2MSFT NGP10.phx.gbl.. .
> > > > Note i havent implemented the function or the Server URLEncode[/color][/color][/color]
because[color=blue][color=green]
> > it[color=darkred]
> > > > doesnt work, this is my code as it stands now.
> > > >
> > > > ****Summary_Are a.asp(Page1)*** ***
> > > >
> > > > <%Dim RS
> > > >
> > > > Function sql_quote (str)
> > > > sql_quote = Replace(str,"'" ,"''")
> > > > End Function
> > > >
> > > > Set RS = DataConnection. Execute("SELECT manager, customer, income,[/color][/color]
> FROM[color=green][color=darkred]
> > > > vw_Summary_Area WHERE vw_Summary_Area .Area='" & Session("lstAre a") &[/color]
> > "'")[color=darkred]
> > > > do until RS.EOF
> > > > %>
> > > > <a href=[/color][/color]
> "summary_manage r.asp?manager=< %=RS("manager") %>"><%[color=green][color=darkred]
> > > > =RS("manager")% ></a>
> > > >
> > > > <% =RS("customer") %>
> > > >
> > > > <% =formatcurrency (RS("income"),2 )%>
> > > >
> > > > <% RS.MoveNext
> > > > Loop %>
> > > >
> > > > *************** *************** **
> > > > ****Summary_Man ager.asp(Page2) ****
> > > >
> > > > <%Dim RS
> > > >
> > > > Function sql_quote (str)
> > > > sql_quote = Replace(str,"'" ,"''")
> > > > End Function
> > > >
> > > > Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,
> > > income
> > > > FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> > > > request.queryst ring("manager") & "'")
> > > > do until RS.EOF
> > > >
> > > > <a href=
> > > >
> > >[/color]
> >[/color]
>[/color]
"summary_assist ant_manager.asp ?assistant_mana ger=<%=RS("assi stant_manager") %[color=blue][color=green][color=darkred]
> > > > >"><% =RS ("assistant_man ager")%></a>
> > > >
> > > > <% =RS("customer") %>
> > > >
> > > > <% =formatcurrency (RS("income"),2 )%>
> > > >
> > > > <% RS.MoveNext
> > > > Loop %>
> > > >
> > > > *************** *************** *
> > > > To get to page1 you select an area from a list box and click submit.[/color]
> > data[color=darkred]
> > > is
> > > > arranged in accordance to the selection. A list of managers are[/color][/color][/color]
listed[color=blue][color=green]
> > for[color=darkred]
> > > > that area, along with their customers and income. If you click on[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > manager you go to page 2 and get a breakdown of the assistant[/color][/color]
> manager/s[color=green][color=darkred]
> > > for
> > > > that manager and so on and so on.......
> > > >
> > > > I hope this has made it a bit clearer.
> > > >
> > > > --
> > > > Thanks in advance
> > > >
> > > > Fawke
> > > >
> > > > Please remove ANTI and SPAM
> > > > from my email address before emailing me.
> > > >
> > > > www.bradflack.com
> > > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > > news:#Ev60sUDEH A.3692@tk2msftn gp13.phx.gbl...
> > > > > jesus man, i didnt mean to annoy anyone.
> > > > > Il post the "actual" code.
> > > > >
> > > > > The reason that the 2 select statements differ, is because the[/color][/color][/color]
data[color=blue]
> is[color=green][color=darkred]
> > > > > coming from a different view etc.
> > > > > On page1.asp the data comes from view1 etc. The value of that data[/color][/color]
> is[color=green][color=darkred]
> > > put
> > > > > into a querystring in order for page2.asp (which gets its data[/color][/color][/color]
from[color=blue][color=green][color=darkred]
> > > > ANOTHER
> > > > > view - view2) to proccess the data.
> > > > > I will paste my code up here without the naming ammendments, just
> > > thought
> > > > > it'd be easier (clearly not).
> > > > >
> > > > > Apologies once again for annoying you guys
> > > > >
> > > > > --
> > > > > Thanks in advance
> > > > >
> > > > > Fawke
> > > > >
> > > > > Please remove ANTI and SPAM
> > > > > from my email address before emailing me.
> > > > >
> > > > > www.bradflack.com
> > > > > "Aaron Bertrand [MVP]" <aaron@TRASHasp faq.com> wrote in message
> > > > > news:ei8lJuSDEH A.3404@TK2MSFTN GP10.phx.gbl...
> > > > > > I think if Fawke101 were to use real column/parameter names[/color][/color]
> instead[color=green]
> > of[color=darkred]
> > > > > > kludging it up with this COL1/COL2 nonsense we'd get to the[/color][/color][/color]
bottom[color=blue][color=green]
> > of[color=darkred]
> > > > this
> > > > > a
> > > > > > lot quicker.
> > > > > >
> > > > > > --
> > > > > > Aaron Bertrand
> > > > > > SQL Server MVP
> > > > > > http://www.aspfaq.com/
> > > > > >
> > > > > >
> > > > > > "Peter Foti" <peter@Idontwan tnostinkingemai lfromyou.com> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > > message
> > > > > > news:105jum4k6j 5jb6e@corp.supe rnews.com...
> > > > > > > "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> > > > > > > news:ejFaFVRDEH A.2600@TK2MSFTN GP09.phx.gbl...
> > > > > > > > that doesnt work tho, it completely ignores it and still[/color]
> > generates[color=darkred]
> > > a
> > > > '
> > > > > > > > i cant get this damn thing to work, its not your fault, im[/color][/color]
> sure[color=green]
> > im[color=darkred]
> > > > > doing
> > > > > > > > something wrong here.
> > > > > > > >
> > > > > > > > The page with the link (with the apostrophy) -
> > > > > > > > ****page1.asp** **
> > > > > > > >
> > > > > > > > Function SQL_QUOTE (str)
> > > > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > > > End Function
> > > > > > > >
> > > > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM[/color][/color][/color]
view1[color=blue][color=green][color=darkred]
> > > WHERE
> > > > > > > > view1.COL3='" & SQL_QUOTE(sessi on("lstbox")) & "'")
> > > > > > > >
> > > > > > > > 'incorrect link looks like - St Mathew's - when clicked -
> > > > > > > >
> > > > > > > > <a href= "page2.asp?VALU E=<%[/color]
> > =server.urlenco de(RS("value1") )%>"><%[color=darkred]
> > > > > > > > =RS("value1")%> </a>
> > > > > > > >
> > > > > > > > *************** *
> > > > > > > > Then on page2.asp
> > > > > > > >
> > > > > > > > Function SQL_QUOTE (str)
> > > > > > > > SQL_QUOTE = Replace(str,"'" ,"''")
> > > > > > > > End Function
> > > > > > > >
> > > > > > > > Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM[/color][/color][/color]
view2[color=blue][color=green][color=darkred]
> > > WHERE
> > > > > > > > view1.COL3='" & SQL_QUOTE(reque st.querystring( "value1")) &[/color][/color]
> "'")[color=green][color=darkred]
> > > > > > > > *************** *
> > > > > > > > and so on like that........... ..............
> > > > > > >
> > > > > > >
> > > > > > > You're making this difficult to debug... which is the correct
> > > > > querystring
> > > > > > > variable name, "value" or "value1"? On page1.asp you called[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > > "value"
> > > > > > and
> > > > > > > on page 2 you are trying to reference "value1". In addition,[/color][/color]
> you[color=green][color=darkred]
> > > used
> > > > > the
> > > > > > > fieldnames "COL1" and "COL2" in the query above that. WHICH[/color][/color][/color]
IS[color=blue][color=green][color=darkred]
> > > IT???
> > > > > > Also,
> > > > > > > your query on page2.asp is pulling from table "view2", but[/color][/color][/color]
using[color=blue]
> a[color=green][color=darkred]
> > > > value
> > > > > > > from "view1"???? Are these cut and paste errors?
> > > > > > >
> > > > > > > Peter
> > > > > > > PS- Odd naming convention (giving the sql_quote function all
> > > uppercase
> > > > > > > letters... very strange).
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Bob Barrows [MVP]]

Re: Server.URLEncod e :-(

Let's get this working without the database involved.

page 1:
<a href= "summary_manage r.asp?manager=O 'Connor">O'Conn or</a>

page 2 (summary_manage r.asp):
<%
dim sSQL
response.write request.queryst ring("manager") & "<BR>"
sSQL = "SELECT assistant_manag er, customer, income " & _
FROM vw_Summary_Mana ger " & _
"WHERE vw_Summary_Mana ger.manager='" & _
request.queryst ring("manager") & "'")

Response.write sSQL & "<BR>"
%>

When you click the link, the resulting page will look like this:
O'Connor
.... WHERE vw_Summary_Mana ger.manager='O' Connor'

The problem here is that the query engine will think the apostrophe after
the O is the string delimiter. You need to tell it to treat it as a literal
apostrophe instead of a delimiter by doubling it. Like this:

sSQL = "SELECT assistant_manag er, customer, income " & _
FROM vw_Summary_Mana ger " & _
"WHERE vw_Summary_Mana ger.manager='" & _
Replace(request .querystring("m anager"), "'", "''") & "'")

Response.write sSQL & "<BR>"

Now, when you click the link, you should see this:
.... WHERE vw_Summary_Mana ger.manager='O' 'Connor'

Always use response.write to test your concatenations.


You can avoid this problem entirely by using a stored procedure (sql server)
or saved parameter query (access) instead of dynamic sql.

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=%23RP6V33UDHA.2452%40TK2MSFTNGP09.phx.gbl&rnum=2&prev=/groups%3Fas_q%3Dparameters%2520%2520stored%2520procedures%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_uauthors%3DBob%2520Barrows%26lr%3D%26as_drrb%3Db%26as_mind%3D12%26as_minm%3D7%26as_miny%3D2003%26as_maxd%3D13%26as_maxm%3D8%26as_maxy%3D2003%26hl%3Den

You can also avoid it by parameterizing your dynamic sql statement and using
a command object to execute it.

http://groups.google.com/groups?q=parameter+dynamic+sql+group:microsoft.public.inetserver.asp.*&start=30&hl=en&lr=&ie=UTF-8&oe=UTF-8&c2coff=1&selm=eU%23%249EUIDHA.1720%40TK2MSFTNGP11.phx.gbl&rnum=33

Bob Barrows

Fawke101 wrote:[color=blue]
> Note i havent implemented the function or the Server URLEncode
> because it doesnt work, this is my code as it stands now.
>
> ****Summary_Are a.asp(Page1)*** ***
>
> <%Dim RS
>
> Function sql_quote (str)
> sql_quote = Replace(str,"'" ,"''")
> End Function
>
> Set RS = DataConnection. Execute("SELECT manager, customer, income,
> FROM vw_Summary_Area WHERE vw_Summary_Area .Area='" &
> Session("lstAre a") & "'") do until RS.EOF
> %>
> <a href=
> "summary_manage r.asp?manager=< %=RS("manager") %>"><%
> =RS("manager")% ></a>
>
> <% =RS("customer") %>
>
> <% =formatcurrency (RS("income"),2 )%>
>
> <% RS.MoveNext
> Loop %>
>
> *************** *************** **
> ****Summary_Man ager.asp(Page2) ****
>
> <%Dim RS
>
> Function sql_quote (str)
> sql_quote = Replace(str,"'" ,"''")
> End Function
>
> Set RS = DataConnection. Execute("SELECT assistant_manag er, customer,
> income FROM vw_Summary_Mana ger WHERE vw_Summary_Mana ger.manager='" &
> request.queryst ring("manager") & "'")
> do until RS.EOF
>
> <a href=
>[/color]
"summary_assist ant_manager.asp ?assistant_mana ger=<%=RS("assi stant_manager") %[color=blue][color=green]
>> "><% =RS ("assistant_man ager")%></a>[/color]
>
> <% =RS("customer") %>
>
> <% =formatcurrency (RS("income"),2 )%>
>
> <% RS.MoveNext
> Loop %>
>
> *************** *************** *
> To get to page1 you select an area from a list box and click submit.
> data is arranged in accordance to the selection. A list of managers
> are listed for that area, along with their customers and income. If
> you click on the manager you go to page 2 and get a breakdown of the
> assistant manager/s for that manager and so on and so on.......
>
> I hope this has made it a bit clearer.
>
>
> www.bradflack.com
> "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
> news:#Ev60sUDEH A.3692@tk2msftn gp13.phx.gbl...[color=green]
>> jesus man, i didnt mean to annoy anyone.
>> Il post the "actual" code.
>>
>> The reason that the 2 select statements differ, is because the data
>> is coming from a different view etc.
>> On page1.asp the data comes from view1 etc. The value of that data
>> is put into a querystring in order for page2.asp (which gets its
>> data from ANOTHER view - view2) to proccess the data.
>> I will paste my code up here without the naming ammendments, just
>> thought it'd be easier (clearly not).
>>
>> Apologies once again for annoying you guys
>>
>> --
>> Thanks in advance
>>
>> Fawke
>>
>> Please remove ANTI and SPAM
>> from my email address before emailing me.
>>
>> www.bradflack.com
>> "Aaron Bertrand [MVP]" <aaron@TRASHasp faq.com> wrote in message
>> news:ei8lJuSDEH A.3404@TK2MSFTN GP10.phx.gbl...[color=darkred]
>>> I think if Fawke101 were to use real column/parameter names instead
>>> of kludging it up with this COL1/COL2 nonsense we'd get to the
>>> bottom of this a lot quicker.
>>>
>>> --
>>> Aaron Bertrand
>>> SQL Server MVP
>>> http://www.aspfaq.com/
>>>
>>>
>>> "Peter Foti" <peter@Idontwan tnostinkingemai lfromyou.com> wrote in
>>> message news:105jum4k6j 5jb6e@corp.supe rnews.com...
>>>> "Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
>>>> news:ejFaFVRDEH A.2600@TK2MSFTN GP09.phx.gbl...
>>>>> that doesnt work tho, it completely ignores it and still
>>>>> generates a ' i cant get this damn thing to work, its not your
>>>>> fault, im sure im doing something wrong here.
>>>>>
>>>>> The page with the link (with the apostrophy) -
>>>>> ****page1.asp** **
>>>>>
>>>>> Function SQL_QUOTE (str)
>>>>> SQL_QUOTE = Replace(str,"'" ,"''")
>>>>> End Function
>>>>>
>>>>> Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view1
>>>>> WHERE view1.COL3='" & SQL_QUOTE(sessi on("lstbox")) & "'")
>>>>>
>>>>> 'incorrect link looks like - St Mathew's - when clicked -
>>>>>
>>>>> <a href= "page2.asp?VALU E=<% =server.urlenco de(RS("value1") )%>"><%
>>>>> =RS("value1")%> </a>
>>>>>
>>>>> *************** *
>>>>> Then on page2.asp
>>>>>
>>>>> Function SQL_QUOTE (str)
>>>>> SQL_QUOTE = Replace(str,"'" ,"''")
>>>>> End Function
>>>>>
>>>>> Set RS = DataConnection. Execute("SELECT COL1, COL2 FROM view2
>>>>> WHERE view1.COL3='" & SQL_QUOTE(reque st.querystring( "value1")) &
>>>>> "'") *************** *
>>>>> and so on like that........... ..............
>>>>
>>>>
>>>> You're making this difficult to debug... which is the correct
>>>> querystring variable name, "value" or "value1"? On page1.asp you
>>>> called it "value" and on page 2 you are trying to reference
>>>> "value1". In addition, you used the fieldnames "COL1" and "COL2"
>>>> in the query above that. WHICH IS IT??? Also, your query on
>>>> page2.asp is pulling from table "view2", but using a value from
>>>> "view1"???? Are these cut and paste errors?
>>>>
>>>> Peter
>>>> PS- Odd naming convention (giving the sql_quote function all
>>>> uppercase letters... very strange).[/color][/color][/color]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: Server.URLEncod e :-(

Bob Barrows [MVP] wrote:[color=blue]
> Let's get this working without the database involved.
> page 2 (summary_manage r.asp):
>. You need to tell it to treat it as a
> literal
> apostrophe instead of a delimiter by doubling it. Like this:
>
> sSQL = "SELECT assistant_manag er, customer, income " & _
> FROM vw_Summary_Mana ger " & _
> "WHERE vw_Summary_Mana ger.manager='" & _
> Replace(request .querystring("m anager"), "'", "''") & "'")
>
> Response.write sSQL & "<BR>"
>[/color]
Oops - here's the TESTED code:

<%
dim sSQL
response.write request.queryst ring("manager") & "<BR>"
sSQL = "SELECT assistant_manag er, customer, income " & _
"FROM vw_Summary_Mana ger " & _
"WHERE vw_Summary_Mana ger.manager='" & _
replace(Request .QueryString("m anager"),"'","' '") & "'"

Response.write sSQL & "<BR>"
%>

[Fawke101]

Re: Server.URLEncod e :-(

Thats great, but the database wont find a record unless it has the
apostrophy? how can i match it up!?

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:#bCe$qbDEH A.2768@tk2msftn gp13.phx.gbl...[color=blue]
> Bob Barrows [MVP] wrote:[color=green]
> > Let's get this working without the database involved.
> > page 2 (summary_manage r.asp):
> >. You need to tell it to treat it as a
> > literal
> > apostrophe instead of a delimiter by doubling it. Like this:
> >
> > sSQL = "SELECT assistant_manag er, customer, income " & _
> > FROM vw_Summary_Mana ger " & _
> > "WHERE vw_Summary_Mana ger.manager='" & _
> > Replace(request .querystring("m anager"), "'", "''") & "'")
> >
> > Response.write sSQL & "<BR>"
> >[/color]
> Oops - here's the TESTED code:
>
> <%
> dim sSQL
> response.write request.queryst ring("manager") & "<BR>"
> sSQL = "SELECT assistant_manag er, customer, income " & _
> "FROM vw_Summary_Mana ger " & _
> "WHERE vw_Summary_Mana ger.manager='" & _
> replace(Request .QueryString("m anager"),"'","' '") & "'"
>
> Response.write sSQL & "<BR>"
> %>
>
>[/color]

[Bob Barrows [MVP]]

Re: Server.URLEncod e :-(

What database are you using?

The sql statement containing the doubled apostrophe DOES contain an
apostrophe. Prove it to yourself:

Open up the query tool in whatever database you are using (query Analyzer
for SQL Server; Query Builder if Access). Copy and paste the sql statement
that resulted from the response.write from the browser window into the query
tool and run it. It should get you your results. Give us more details if it
does not.

Bob Barrows

Fawke101 wrote:[color=blue]
> Thats great, but the database wont find a record unless it has the
> apostrophy? how can i match it up!?
>
>
> www.bradflack.com
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:#bCe$qbDEH A.2768@tk2msftn gp13.phx.gbl...[color=green]
>> Bob Barrows [MVP] wrote:[color=darkred]
>>> Let's get this working without the database involved.
>>> page 2 (summary_manage r.asp):
>>> . You need to tell it to treat it as a
>>> literal
>>> apostrophe instead of a delimiter by doubling it. Like this:
>>>
>>> sSQL = "SELECT assistant_manag er, customer, income " & _
>>> FROM vw_Summary_Mana ger " & _
>>> "WHERE vw_Summary_Mana ger.manager='" & _
>>> Replace(request .querystring("m anager"), "'", "''") & "'")
>>>
>>> Response.write sSQL & "<BR>"
>>>[/color]
>> Oops - here's the TESTED code:
>>
>> <%
>> dim sSQL
>> response.write request.queryst ring("manager") & "<BR>"
>> sSQL = "SELECT assistant_manag er, customer, income " & _
>> "FROM vw_Summary_Mana ger " & _
>> "WHERE vw_Summary_Mana ger.manager='" & _
>> replace(Request .QueryString("m anager"),"'","' '") & "'"
>>
>> Response.write sSQL & "<BR>"
>> %>[/color][/color]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Fawke101]

Re: Server.URLEncod e :-(

My apologies bob, i replced the apostrophy with "" rather than "''", i didnt
realise the DB would look for a ' when i entered ''. A mistunderstandi ng on
my part.
I apologise to all who bared with me in this post,

It all working!!

kind regards and thanks to all

--
Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OE808VcDEH A.3392@TK2MSFTN GP11.phx.gbl...[color=blue]
> What database are you using?
>
> The sql statement containing the doubled apostrophe DOES contain an
> apostrophe. Prove it to yourself:
>
> Open up the query tool in whatever database you are using (query Analyzer
> for SQL Server; Query Builder if Access). Copy and paste the sql statement
> that resulted from the response.write from the browser window into the[/color]
query[color=blue]
> tool and run it. It should get you your results. Give us more details if[/color]
it[color=blue]
> does not.
>
> Bob Barrows
>
> Fawke101 wrote:[color=green]
> > Thats great, but the database wont find a record unless it has the
> > apostrophy? how can i match it up!?
> >
> >
> > www.bradflack.com
> > "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> > news:#bCe$qbDEH A.2768@tk2msftn gp13.phx.gbl...[color=darkred]
> >> Bob Barrows [MVP] wrote:
> >>> Let's get this working without the database involved.
> >>> page 2 (summary_manage r.asp):
> >>> . You need to tell it to treat it as a
> >>> literal
> >>> apostrophe instead of a delimiter by doubling it. Like this:
> >>>
> >>> sSQL = "SELECT assistant_manag er, customer, income " & _
> >>> FROM vw_Summary_Mana ger " & _
> >>> "WHERE vw_Summary_Mana ger.manager='" & _
> >>> Replace(request .querystring("m anager"), "'", "''") & "'")
> >>>
> >>> Response.write sSQL & "<BR>"
> >>>
> >> Oops - here's the TESTED code:
> >>
> >> <%
> >> dim sSQL
> >> response.write request.queryst ring("manager") & "<BR>"
> >> sSQL = "SELECT assistant_manag er, customer, income " & _
> >> "FROM vw_Summary_Mana ger " & _
> >> "WHERE vw_Summary_Mana ger.manager='" & _
> >> replace(Request .QueryString("m anager"),"'","' '") & "'"
> >>
> >> Response.write sSQL & "<BR>"
> >> %>[/color][/color]
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]