I've run into a problem that I can't seem to work around. (hasn't everyone)

Anyway, I'm using an Access 2000 database as a "back-end" because my 
client is cheap. For the sake of simplicity, let's assume this database 
has one table named tblFoo and that table has one field named strDesc.  
strDesc is a memo field and has no special parameteres assigned to it in 
the table definition other than it's a memo, it's not required and zero 
length strings are allowed.  I have also created a single query that has 
these three fields as output.


Desc1: strDesc
Desc2: UCase([strDesc])
Desc3: IIf(IsNull([strDesc]),"No Description",IIf(Len([strDesc])<1,"No 
Description",[strDesc]))

All of these produce exactly the same output within Access when field 
strDesc contains a string. This is correct and expected behavior.

On the front-end I'm using ASP.NET and ADO.NET to pull that information 
out.  I use a command object set to type "Stored Procedure" and am able to 
retrieve the results of that query.  The problem I've come across is that 
Desc2 and Desc3 are truncated at 256 characters in length while Desc1 
comes across fine.  Any time I use an Access-based function in a query 
field, I end up with truncated data for that fields results. As you can 
see, even something as simple as UCase() is enough to cause the results to 
be 256 truncated.  Does anyone have a clue why this might be happening?  
It appears to me that the datatypes are being converted to type "Text" 
internally so truncation occurs but when you view this  within Access the 
problem does not manifest.  I can also not figure out how to work around 
this issue.  If you have the answer or think you may have more clues, 
please share it with me as I'm approaching my wits end after 17 hours of 
hair pulling.

Regards,
Tony

Have you tried eliminating the functions in your query and doing similar
functions after the string is returned?

Kyle

-----Original Message-----
From: Tony T [mailto:tony.tocco@f...]
Sent: Friday, March 15, 2002 4:35 PM
To: ADO.NET
Subject: [ado_dotnet] ADO.NET/Access 256char truncation problem

I've run into a problem that I can't seem to work around. (hasn't
everyone)

Anyway, I'm using an Access 2000 database as a "back-end" because my
client is cheap. For the sake of simplicity, let's assume this database
has one table named tblFoo and that table has one field named strDesc. 
strDesc is a memo field and has no special parameteres assigned to it in

the table definition other than it's a memo, it's not required and zero
length strings are allowed.  I have also created a single query that has

these three fields as output.


Desc1: strDesc
Desc2: UCase([strDesc])
Desc3: IIf(IsNull([strDesc]),"No Description",IIf(Len([strDesc])<1,"No
Description",[strDesc]))

All of these produce exactly the same output within Access when field
strDesc contains a string. This is correct and expected behavior.

On the front-end I'm using ASP.NET and ADO.NET to pull that information
out.  I use a command object set to type "Stored Procedure" and am able
to
retrieve the results of that query.  The problem I've come across is
that
Desc2 and Desc3 are truncated at 256 characters in length while Desc1
comes across fine.  Any time I use an Access-based function in a query
field, I end up with truncated data for that fields results. As you can
see, even something as simple as UCase() is enough to cause the results
to
be 256 truncated.  Does anyone have a clue why this might be happening?

It appears to me that the datatypes are being converted to type "Text"
internally so truncation occurs but when you view this  within Access
the
problem does not manifest.  I can also not figure out how to work around

this issue.  If you have the answer or think you may have more clues,
please share it with me as I'm approaching my wits end after 17 hours of

hair pulling.

Regards,
Tony

Tony,

Just because your customer is cheap doesn't mean you have to use Access
2000.  You should think about using MSDE instead of access.  MSDE comes with
several of the Microsoft development tools, including the .NET Visual
Studio.

It is basically SQL Server 2000 with a few limitations (eg. an individual
database can't be larger than 2Gig).  There's no fee for it and you can
distribute it with your app.  It's a very nice way to go.

----- Original Message -----
From: "Tony T" <tony.tocco@f...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, March 15, 2002 9:34 PM
Subject: [ado_dotnet] ADO.NET/Access 256char truncation problem


> I've run into a problem that I can't seem to work around. (hasn't
everyone)
>
> Anyway, I'm using an Access 2000 database as a "back-end" because my
> client is cheap. For the sake of simplicity, let's assume this database
> has one table named tblFoo and that table has one field named strDesc.
> strDesc is a memo field and has no special parameteres assigned to it in
> the table definition other than it's a memo, it's not required and zero
> length strings are allowed.  I have also created a single query that has
> these three fields as output.
>
>
> Desc1: strDesc
> Desc2: UCase([strDesc])
> Desc3: IIf(IsNull([strDesc]),"No Description",IIf(Len([strDesc])<1,"No
> Description",[strDesc]))
>
> All of these produce exactly the same output within Access when field
> strDesc contains a string. This is correct and expected behavior.
>
> On the front-end I'm using ASP.NET and ADO.NET to pull that information
> out.  I use a command object set to type "Stored Procedure" and am able to
> retrieve the results of that query.  The problem I've come across is that
> Desc2 and Desc3 are truncated at 256 characters in length while Desc1
> comes across fine.  Any time I use an Access-based function in a query
> field, I end up with truncated data for that fields results. As you can
> see, even something as simple as UCase() is enough to cause the results to
> be 256 truncated.  Does anyone have a clue why this might be happening?
> It appears to me that the datatypes are being converted to type "Text"
> internally so truncation occurs but when you view this  within Access the
> problem does not manifest.  I can also not figure out how to work around
> this issue.  If you have the answer or think you may have more clues,
> please share it with me as I'm approaching my wits end after 17 hours of
> hair pulling.
>
> Regards,
> Tony
>

Terry,

Do .NET applications compiled with MSDE work with Windows 98?

Kyle
-----Original Message-----
From: Terry Carr [mailto:Terry.Carr@r...]
Sent: Friday, March 15, 2002 5:35 PM
To: ADO.NET
Subject: [ado_dotnet] Re: ADO.NET/Access 256char truncation problem

Tony,

Just because your customer is cheap doesn't mean you have to use Access
2000.  You should think about using MSDE instead of access.  MSDE comes
with
several of the Microsoft development tools, including the .NET Visual
Studio.

It is basically SQL Server 2000 with a few limitations (eg. an
individual
database can't be larger than 2Gig).  There's no fee for it and you can
distribute it with your app.  It's a very nice way to go.

----- Original Message -----
From: "Tony T" <tony.tocco@f...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, March 15, 2002 9:34 PM
Subject: [ado_dotnet] ADO.NET/Access 256char truncation problem


> I've run into a problem that I can't seem to work around. (hasn't
everyone)
>
> Anyway, I'm using an Access 2000 database as a "back-end" because my
> client is cheap. For the sake of simplicity, let's assume this
database
> has one table named tblFoo and that table has one field named strDesc.
> strDesc is a memo field and has no special parameteres assigned to it
in
> the table definition other than it's a memo, it's not required and
zero
> length strings are allowed.  I have also created a single query that
has
> these three fields as output.
>
>
> Desc1: strDesc
> Desc2: UCase([strDesc])
> Desc3: IIf(IsNull([strDesc]),"No Description",IIf(Len([strDesc])<1,"No
> Description",[strDesc]))
>
> All of these produce exactly the same output within Access when field
> strDesc contains a string. This is correct and expected behavior.
>
> On the front-end I'm using ASP.NET and ADO.NET to pull that
information
> out.  I use a command object set to type "Stored Procedure" and am
able to
> retrieve the results of that query.  The problem I've come across is
that
> Desc2 and Desc3 are truncated at 256 characters in length while Desc1
> comes across fine.  Any time I use an Access-based function in a query
> field, I end up with truncated data for that fields results. As you
can
> see, even something as simple as UCase() is enough to cause the
results to
> be 256 truncated.  Does anyone have a clue why this might be
happening?
> It appears to me that the datatypes are being converted to type "Text"
> internally so truncation occurs but when you view this  within Access
the
> problem does not manifest.  I can also not figure out how to work
around
> this issue.  If you have the answer or think you may have more clues,
> please share it with me as I'm approaching my wits end after 17 hours
of
> hair pulling.
>
> Regards,
> Tony
>

You might try sequential access on the field. GetBytes can pull chunks of a
memo field out which can then be concatenated. I used it under old ADO but
have not tried it under .Net.

Steve Grant
Product Director
Forward Vision Ltd
www.forwardvision.ltd.uk
Tel +44 (0)118 933 1947
**********************************************************************
This e-mail (including attachments) is intended only for the recipient(s)
named above.  It may contain confidential or privileged information and
should not be read, copied or otherwise used by any other person.  If you
are not the named recipient, please contact the sender
immediately and delete the e-mail from your system.
**********************************************************************
----- Original Message -----
From: "Tony T" <tony.tocco@f...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, March 15, 2002 9:34 PM
Subject: [ado_dotnet] ADO.NET/Access 256char truncation problem


> I've run into a problem that I can't seem to work around. (hasn't
everyone)
>
> Anyway, I'm using an Access 2000 database as a "back-end" because my
> client is cheap. For the sake of simplicity, let's assume this database
> has one table named tblFoo and that table has one field named strDesc.
> strDesc is a memo field and has no special parameteres assigned to it in
> the table definition other than it's a memo, it's not required and zero
> length strings are allowed.  I have also created a single query that has
> these three fields as output.
>
>
> Desc1: strDesc
> Desc2: UCase([strDesc])
> Desc3: IIf(IsNull([strDesc]),"No Description",IIf(Len([strDesc])<1,"No
> Description",[strDesc]))
>
> All of these produce exactly the same output within Access when field
> strDesc contains a string. This is correct and expected behavior.
>
> On the front-end I'm using ASP.NET and ADO.NET to pull that information
> out.  I use a command object set to type "Stored Procedure" and am able to
> retrieve the results of that query.  The problem I've come across is that
> Desc2 and Desc3 are truncated at 256 characters in length while Desc1
> comes across fine.  Any time I use an Access-based function in a query
> field, I end up with truncated data for that fields results. As you can
> see, even something as simple as UCase() is enough to cause the results to
> be 256 truncated.  Does anyone have a clue why this might be happening?
> It appears to me that the datatypes are being converted to type "Text"
> internally so truncation occurs but when you view this  within Access the
> problem does not manifest.  I can also not figure out how to work around
> this issue.  If you have the answer or think you may have more clues,
> please share it with me as I'm approaching my wits end after 17 hours of
> hair pulling.
>
> Regards,
> Tony

> 
>Have you tried eliminating the functions in your query and doing similar
>functions after the string is returned?
>
>Kyle

I was hoping to avoid doing that.  Part of the beauty of stored procedures 
is that you can have the back-end do the work for you.  As the clock ticks 
by however, I believe this is my best and fastest option.  

In response to the message about using the SQLServer 2000 package:  That 
is a good option but wow; that would add serious development time beyond 
what I have available.  Perhaps on the next project (READ: Definately on 
the next project).

Thanks for the input guys/gals.

Regards,
Tony