Memo field trouble

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm using an AccessDataSource to perform a select query on an MDB file.

However, my Memo fields are getting truncated? (to about 250 characters).

Why is this happening and how do I fix it?! Thanks,


Dan
 
¤ Hi
¤
¤ I'm using an AccessDataSource to perform a select query on an MDB file.
¤
¤ However, my Memo fields are getting truncated? (to about 250 characters).
¤
¤ Why is this happening and how do I fix it?! Thanks,

You can't tread Memo fields the same as Text. Below is an example of how to read the characters from
a Memo column:

Dim retVal As Long
Dim FieldLen As Int32

Dim MemoCol As Integer = 1 ' the column # of the Memo field in the query
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("SELECT [record ID], MemoField FROM Table1 WHERE
[record id] = 1", AccessConnection)
AccessConnection.Open()
Dim dr As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
dr.Read()
FieldLen = dr.Item(MemoCol).Length
Dim MemoBuffer(FieldLen - 1) As Char
Dim startIndex As Integer = 0
retVal = dr.GetChars(1, startIndex, MemoBuffer, 0, MemoBuffer.Length)
Console.WriteLine(MemoBuffer)
dr.Close()
AccessConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Dan,

There is no limitation in AccessDataSouce. Would you please show us your
exact query?
As far as I know, memo filed will be truncated if "DISTINCT" was used in
the query.
Additionally, I think you may get the more information about this issue.
http://allenbrowne.com/ser-63.html
[Truncation of Memo fields]

Please feel free to reply me if you have anything unclear and we will
follow up.
Hope this helps.
Wen Yuan
 
Hi Wen Yuan,

Thanks for your reply. I still haven't managed to fix this, so perhaps you
could have a look at my query?

SELECT Stories.StoryTitle, Stories.Synopsis, Stories.Story
COUNT(Comments.CommentID) AS ComCount
FROM (Stories LEFT OUTER JOIN Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)
GROUP BY Stories.StoryTitle, Stories.Synopsis, Stories.Story

Stories.Story is the MEMO field, and according to the page you gave me a
link to, I'm using Aggregation (cos I'm using GROUP BY). How do I change the
query to use FIRST like it says?!

Also, does this mean that I could only use one memo field in a query which
uses a GROUP BY clause?!

Thanks,



Dan
 
Hi Dan,
Thank for your reply.

Yes, as you see, memo field will be truncate if you leave Group By under
memo field.
The KB as below descript this issue. Hope this helpful to you.
http://support.microsoft.com/kb/250640/en-us
[PRB: GetChunk Ignores Offset if Memo Field Is Included in GROUP BY]

The workaround is to rewrite the query to eliminate all Memo fields from
the GROUP BY clause. This can be done by using an aggregate function on the
Memo fields, such as the FIRST function. This allows the Memo fields to be
removed from the GROUP BY clause.

To your special case, the query command could be changed as
SELECT Stories.StoryTitle, Stories.Synopsis, First (Stories.Story)
COUNT(Comments.CommentID) AS ComCount
FROM (Stories LEFT OUTER JOIN Comments ON Stories.StoryID =
Comments.StoryID)
WHERE (Stories.StoryID = ?)
GROUP BY Stories.StoryTitle, Stories.Synopsis

Additionally, this doesn't mean we could only use one memo field in a query
which uses a GROUP BY clause. If you have more than one memo field, you can
use First under all this memo field.
For example:
Select c1, First (c2), First (c3) from table group by c1.
Both c2 and c3 are memo filed.

Please don't hesitate to let me know if you have any further question.
Have a great day.
Best regards,
Wen Yuan
 
¤ Hi Wen Yuan,
¤
¤ Thanks for your reply. I still haven't managed to fix this, so perhaps you
¤ could have a look at my query?
¤
¤ SELECT Stories.StoryTitle, Stories.Synopsis, Stories.Story
¤ COUNT(Comments.CommentID) AS ComCount
¤ FROM (Stories LEFT OUTER JOIN Comments ON Stories.StoryID = Comments.StoryID)
¤ WHERE (Stories.StoryID = ?)
¤ GROUP BY Stories.StoryTitle, Stories.Synopsis, Stories.Story
¤
¤ Stories.Story is the MEMO field, and according to the page you gave me a
¤ link to, I'm using Aggregation (cos I'm using GROUP BY). How do I change the
¤ query to use FIRST like it says?!
¤
¤ Also, does this mean that I could only use one memo field in a query which
¤ uses a GROUP BY clause?!
¤

Just an FYI, joins on Memo columns are not supported.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
WenYuan,

Thanks - worked great with one slight change, needed to do
First(Stories.Story) AS Story to prevent the DataView not being able to find
the field after removing it from the GROUPBY.

Works now though - thank you for your patience!



Dan
 
Back
Top