ASP, Ampersand, Detailsview, escape sequence No data

ASP, Ampersand, Detailsview, escape sequence No data

I am having a problem with data being pulled back when the data field
being used to filter
a query contains an ampersand and not data gets returned to the
detailsview in an ASP page.

Example query:

Declare @Company_Name NVARCHAR(5)

Set Company_Name  = 'Bob & George Used Stuff'

Select * from Company_Data where CompanyName = @Company_Name


*************************************************************

The data source is a SQLDATASOURCE connector, I am using Web Developer
Express.
The example query works fine for any data where the company name does
not contain an
ampersand (&). I have tried doubling up the ampersand and at this
point about 50 billion escape
versions.

The above example works fine in a regular query window. I also tested
using a stored procedure
as the source for the query and had the stored procedure e-mail me
back the value of the company name and it looks proper.

It just returns no data to the detailsview and there does not seem to
be a good reason for it not to work.

date: Tue, 1 Jul 2008 11:16:57 -0700 (PDT) author: unknown

Re: ASP, Ampersand, Detailsview, escape sequence No data

Well you are only declaring the variable with 5 characters (10 bytes 
Unicode). So you will truncate the value you are assigning to the variable 
and you will not get a match.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


 wrote in message 
news:19d2d09e-c822-471a-844d-3b600c7d3058@p39g2000prm.googlegroups.com...
>I am having a problem with data being pulled back when the data field
> being used to filter
> a query contains an ampersand and not data gets returned to the
> detailsview in an ASP page.
>
> Example query:
>
> Declare @Company_Name NVARCHAR(5)
>
> Set Company_Name  = 'Bob & George Used Stuff'
>
> Select * from Company_Data where CompanyName = @Company_Name
>
>
> *************************************************************
>
> The data source is a SQLDATASOURCE connector, I am using Web Developer
> Express.
> The example query works fine for any data where the company name does
> not contain an
> ampersand (&). I have tried doubling up the ampersand and at this
> point about 50 billion escape
> versions.
>
> The above example works fine in a regular query window. I also tested
> using a stored procedure
> as the source for the query and had the stored procedure e-mail me
> back the value of the company name and it looks proper.
>
> It just returns no data to the detailsview and there does not seem to
> be a good reason for it not to work.

date: Tue, 1 Jul 2008 15:08:46 -0400 author: Andrew J. Kelly

Re: ASP, Ampersand, Detailsview, escape sequence No data

On Jul 1, 12:08 pm, "Andrew J. Kelly" 
wrote:
> Well you are only declaring the variable with 5 characters (10 bytes
> Unicode). So you will truncate the value you are assigning to the variable> and you will not get a match.

 IT could be 100 characters long and it would not matter, subsitute a
100 if you like or a 1000.

I clearly state that this ONLY affects those queries where a filter is
applied and the data
has an ampersand in it. ALL other data queries where no ampersand
exists in the variable being used as a filer
in the where clause returns infofmation to the detailsview just fine.
So it's not the length of the variable.

The basics

   A sqldatasource connector, on an ASP page, passing a variable to a
select statement that fails to return
  data when the the variable being passed contains an Ampersand in it
and is being used as a filter in the
"where " portion of the SQL query.

I already verified the variable get's passed correctly via a stored
procedure, yet no data get's returned.

date: Tue, 1 Jul 2008 13:25:10 -0700 (PDT) author: unknown

Re: ASP, Ampersand, Detailsview, escape sequence No data

>>>IT could be 100 characters long and it would not matter, subsitute a
100 if you like or a 1000.
I clearly state that this ONLY affects those queries where a filter is
applied and the data
<<<

Well when you provide a sample with a clear error in it we do not know how 
you got there or what is really going on.  The bottom line is the example 
you gave will not work as expected.  Barring that something is obviously 
doing something to the value you are passing. Did you run profiler to see 
exactly what the command is that is being passed in by the client and what 
the actual values are?  If so can you post that here. That is the only way 
we can see what SQL Server sees.  I highly doubt that it is a problem with 
SQL Server itself. It is more likely something on the client or driver that 
is stripping the & away. The starting event of a profiler trace will show 
you exactly what is being passed in and we can go from there.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors

 wrote in message 
news:ad7b71cc-8475-4217-91cd-694eeadd53ca@z24g2000prf.googlegroups.com...
On Jul 1, 12:08 pm, "Andrew J. Kelly" 
wrote:
> Well you are only declaring the variable with 5 characters (10 bytes
> Unicode). So you will truncate the value you are assigning to the variable
> and you will not get a match.

 IT could be 100 characters long and it would not matter, subsitute a
100 if you like or a 1000.

I clearly state that this ONLY affects those queries where a filter is
applied and the data
has an ampersand in it. ALL other data queries where no ampersand
exists in the variable being used as a filer
in the where clause returns infofmation to the detailsview just fine.
So it's not the length of the variable.

The basics

   A sqldatasource connector, on an ASP page, passing a variable to a
select statement that fails to return
  data when the the variable being passed contains an Ampersand in it
and is being used as a filter in the
"where " portion of the SQL query.

I already verified the variable get's passed correctly via a stored
procedure, yet no data get's returned.

date: Tue, 1 Jul 2008 22:19:49 -0400 author: Andrew J. Kelly