|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Memo field troubleHi
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 On Tue, 20 Feb 2007 07:11:13 -0800, musosdev <musoswire@community.nospam> wrote:
¤ 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 ""WenYuan Wang"" wrote: Show quote > 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 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 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 ""WenYuan Wang"" wrote: Show quote > 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 > > On Thu, 22 Feb 2007 03:57:00 -0800, musosdev <musoswire@community.nospam> wrote:
¤ 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) Thanks Paul - I didn't know that, worth remembering!
Cheers Dan Show quote "Paul Clement" wrote: > On Thu, 22 Feb 2007 03:57:00 -0800, musosdev <musoswire@community.nospam> wrote: > > ¤ 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) >  |
|||||||||||||||||||||||
Other interesting topics