Select query truncates memo field

[Anna]

Hi all,

I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research on this and found some
other Google Groups threads that were helpful, but I still don't have a
solution. My query is rather complicated and uses a union, but not the
keyword distinct. The query is displayed in a datagrid, and I don't
know how I would be able to break it up to solve the 255 problem and
still have it display in the one datagrid. Is there anything I can add
to it? Here's the query:

(Select pk_characterist ic_id, vc_characterist ic_name,
pk_description_ id, tx_description From ((tb_descriptio ns d inner join
tb_agents a on d.fk_agent_id = a.pk_agent_id) inner join
tb_characterist ics c on d.fk_characteri stic_id =
c.pk_characteri stic_id) where d.fk_agent_id = @agentID) UNION (select
pk_characterist ic_id, vc_characterist ic_name, 0 AS pk_description_ id,
'' from tb_characterist ics where pk_characterist ic_id NOT IN (select
fk_characterist ic_id from tb_descriptions where fk_agent_id =
@agentID))

Is there a solution to my problem, or am I going to have to switch
databases? Please help!

Thanks!
Anna

[dedejavu]

Anna,
I don't see a Group By in your query, but this error is usually because
access can't Goup By memo fields. Is it possible you are Gouping
somthing further down the line? Since you are using access as a
backend, rather than fail, Access kindly converts the memo to text(255)
and runs the qry. You have probably done this, but sometimes opening
up Access and pasting the sql into a qry will give you error messages
that you didn't get using it as a backend - to help you troubleshoot.
HTH
Pachydermitis

[Anna]

Thanks for the quick reply. Unfortunately, running the query in Access
doesn't produce any errors, and it truncates the memo fields just like
it does in the code. Is this a bug in Access?

Thanks,
Anna

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query looks like an SQL Server syntax - you have the parameter
@agentID. Access queries do not use parameters like that. In the
second UNION you have a place holder of '' for the tx_description
column. I believe this is the culprit - it is indicating that the data
type of that place holder is a VARCHAR, which, for some reason, is
truncating to 255 chars. If your data back-end really is SQL Server you
might try CAST('' to TEXT) which will set the data type to TEXT (Memo in
SQL Server).

Did you test each query in the UNION query, one at a time, to see if
they both return the correct data? IOW, if the top query returns the
TEXT/Memo data correctly, but adding the bottom query truncates the
data, then you know the bottom query is the culprit.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQla88IechKq OuFEgEQK7nwCfav BByOKu7HCNkafi7 ctDJTF3B9oAnjCk
YntwDg5QPrN0HqL T3RLGbYPp
=tC12
-----END PGP SIGNATURE-----

Anna wrote:

Hi all,

I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research on this and found some
other Google Groups threads that were helpful, but I still don't have a
solution. My query is rather complicated and uses a union, but not the
keyword distinct. The query is displayed in a datagrid, and I don't
know how I would be able to break it up to solve the 255 problem and
still have it display in the one datagrid. Is there anything I can add
to it? Here's the query:

(Select pk_characterist ic_id, vc_characterist ic_name,
pk_description_ id, tx_description From ((tb_descriptio ns d inner join
tb_agents a on d.fk_agent_id = a.pk_agent_id) inner join
tb_characterist ics c on d.fk_characteri stic_id =
c.pk_characteri stic_id) where d.fk_agent_id = @agentID) UNION (select
pk_characterist ic_id, vc_characterist ic_name, 0 AS pk_description_ id,
'' from tb_characterist ics where pk_characterist ic_id NOT IN (select
fk_characterist ic_id from tb_descriptions where fk_agent_id =
@agentID))

Is there a solution to my problem, or am I going to have to switch
databases? Please help!

[Anna]

Thank you - the problem was the union. By the way, I am using Access as
the backend - the "@agentID" is just .NET's syntax for passing in the
variable. Anyways, once I realized the problem was the union I was able
to do a little more research and came across this:

http://groups-beta.google.com/group/...2837e03849daa4

All I had to do was change "union" to "union all" - a nice simple
solution, the way I like it!

Thanks,
Anna