Need to replace & in SQL query string

[JulesBos]

Hi, I've got an SQL query string that works fine getting info from a back end Access database from an ASP.Net page. However I've found one of the query fields could contain illegal characters. I've managed to work out how to replace the ' character, but not the & character. What should I do?

Example:

lifecycle = lifecycle.Replace("'", "''")
selectCommandString = "Select Quotes.QuoteID from Quotes WHERE Lifecycle='" + lifecycle + "'"

If the lifecycle variable contains the ampersand character for example "A&W" the selectCommandString looks like this:

"Select Quotes.QuoteID from Quotes WHERE Lifecycle='A'"

How do I get round this?

Thanks in advance

Julia

 

[Tarwn]

Are you absolutely sure that the variable contains that ampersand before it is added to the SQL string and executed? That it wasn't lost earlier as part of a querystring addition or something like that?

Ampersands are special characters in MS Access, but I thought only when used in Labels and such in the interface. The way to escape them in that situation is to double them. I'm not sure this is the problem you are seeing, though, as it should just be treated as part of the string your inserting.

 

[JulesBos]

Oh yes! I missed the blindingly obvious! The & was lost earlier. I got the variable input from a page query string:

breakdown = Request.QueryString("Breakdown")

Stupid of me not to check that!

So how do I resolve that one then?

Julia

 

[damber]

If you create the url from ASP then:

Server.URLEncode("YourStringVariableHere")

else in javascript:

escape("yourStringhere");



A smile is worth a thousand kind words. So smile, it's easy!