Can't print an Access memo field

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

April 6th, 2008, 03:37 PM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Can't print an Access memo field

Hi,
I can't print the contents of an Access memo field. Every other field type displays fine but this one.
I saw an old thread saying that you should first assign the memo field to a variable but that also doesn't seem to work (please see the attached bit of code). The memo field is "Message".

Code:

For x = 0 To nColumns - 1
	If Not IsNull(objRs(x).Value) Then
		Select Case objRs(x).Name
		Case "From", "To"
			Response.Write "<td>" & GetUserData(objRs(x).Value, 2) & "</td>"
		Case "Message"
			sTmp = objRs.Fields("Message").Value ' this is to solve an issue with memo fields!
			Response.Write "<td>" & sTmp & "</td>"
		Case Else
			Response.Write "<td>" & objRs(x).Value & "</td>"
		End Select
	Else
		Response.Write "<td>&nbsp;</td>"
	End If
Next

Can anybody please help on this?
Thanks.

April 6th, 2008, 09:38 PM

Doug G

Grumpier Old Moderator

Dev Shed God 12th Plane (10500 - 10999 posts)

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

Your code is not copying the value out of the recordset to a local variable. With some db drivers from asp you only have one time to access the blob field, so that one time should be a 'localblob = rs("blobfield")'

__________________
======
Doug G
======
"Hide, hide witch! The good folk come to burn thee. Their keen enjoyment hid behind their gothic mask of duty." -Mark Clifton

April 7th, 2008, 09:29 AM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Quote:

Originally Posted by Doug G

Thanks Doug. I thought I did what you said in the bit of code below:

Code:

Case "Message"
	sTmp = objRs.Fields("Message").Value
	Response.Write "<td>" & sTmp & "</td>"

But that obviously doesn't work. So I moved the following bit before the loop (basically right after I opened the recordset):

Code:

	sTmp = objRs("Message")
	Do Until objRs.EOF
        ....

But that, logically, will display the blob field of the first record only (repeated on every row).
Therefore, I added this other bit:

Code:

    ....
    objRs.MoveNext
    If Not objRs.EOF Then
	sTmp = objRs("Message")
    End If
    ....

But this won't work again, like the original code.
I'm lost then!
If I want to write the contents of a blob field for each and every record in a loop, what should I do?
Please see the complete final code below that is still not working:

Code:

    ....
y = 1
sTmp = objRs("Message")
Do Until objRs.EOF
	If Int(y/2) = y/2 Then
		Response.Write "<tr class='altrow'>"
	Else
		Response.Write "<tr>"
	End If
	For x = 0 To nColumns - 1
		If Not IsNull(objRs(x).Value) Then
			Select Case objRs(x).Name
			Case "From", "To"
				Response.Write "<td>" & GetUserData(objRs(x).Value, 2) & "</td>"
			Case "Message"
				Response.Write "<td>" & sTmp & "</td>"
			Case Else
				Response.Write "<td>" & objRs(x).Value & "</td>"
			End Select
		Else
			Response.Write "<td>&nbsp;</td>"
		End If
	Next
	Response.Write "</tr>"
	objRs.MoveNext
	If Not objRs.EOF Then
		sTmp = objRs("Message")
	End If
	y = y + 1
Loop
    ....

Thanks.

April 7th, 2008, 07:54 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

I suspect your problem is that the IsNull() test counts as an "access" to the recordset blob field. In your loop try a structure something like this (not intended to be cut and paste code):

Code:

myblob = rs(x)
if not isnull(myblob) then
 'blah blah
end if

April 8th, 2008, 07:19 AM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

It doesn't seem to work either. It looks like even testing for EOF counts as a recordset access...

April 9th, 2008, 08:33 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

How are you connecting to the database? Maybe you need to use different connection parameters.

April 10th, 2008, 03:17 AM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Doug, please see the code I use below:

Code:

Dim objConn5

Set objConn5 = Server.CreateObject("ADODB.Connection")

objConn5.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("\database\cse\intmsg.mdb")

objConn5.Open

Function DispIntMessages()
	Dim sTmp
	
	Set objRS = Server.CreateObject ("ADODB.Recordset")

    sSQL = "SELECT * FROM IntMessages ORDER BY Date"

	objRs.Open sSQL, objConn5, adOpenForwardOnly, , adCmdText

	If (Not (objRs.BOF)) Then
		Dim x, y, nColumns, nRows

		nColumns = objRs.Fields.Count

		Response.Write "<table summary='Search Results' class='datatable'>"
		Response.Write "<tr>"
		' Fill the Column Headers
		For x = 0 To nColumns - 1
			Response.Write "<th scope='col'>" & objRs(x).Name & "</th>"
		Next
		Response.Write "</tr>"
		' Get data
		y = 1
		sTmp = objRs("Message") ' this is to solve an issue with memo fields!
		Do Until objRs.EOF
			If Int(y/2) = y/2 Then
				Response.Write "<tr class='altrow'>"
			Else
				Response.Write "<tr>"
			End If
			For x = 0 To nColumns - 1
				If Not IsNull(objRs(x).Value) Then
					Select Case objRs(x).Name
					Case "From", "To"
						Response.Write "<td>" & GetUserData(objRs(x).Value, 2) & "</td>"
					Case "Message"
						Response.Write "<td>" & sTmp & "</td>"
					Case Else
						Response.Write "<td>" & objRs(x).Value & "</td>"
					End Select
				Else
					Response.Write "<td>&nbsp;</td>"
				End If
			Next
			Response.Write "</tr>"
			objRs.MoveNext
			If Not objRs.EOF Then
				sTmp = objRs("Message") ' this is to solve an issue with memo fields!
			End If
			y = y + 1
		Loop
		Response.Write "</table>"
	Else
	    Response.Write "<P>" & _
	                   "Sorry, No IntMessages.</P>"
	End If
	Response.Write "<br>"
	objRS.Close
	Set objRS = Nothing

End Function

...

Cheers.

April 10th, 2008, 07:07 AM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Apparently, changing adOpenForwardOnly to adOpenDynamic in the open clause does the trick. Therefore I used the following:

Code:

objRs.Open sSQL, objConn5, adOpenDynamic, , adCmdText

Thanks a lot for your help Doug.

April 11th, 2008, 02:35 AM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

I'm glad you got it going. Also, you should consider using the Jet OLEDB drivers instead of the older ODBC drivers you are currently using. The OLEDB drivers are much more robust, and operate much better with blob fields. Search here or places like www.w3schools.com to find example connection strings.

#10

April 11th, 2008, 08:15 AM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Thanks for the tip Doug. I tried what you said but I get an error every time I run a query with a WHERE clause.
In other words, if I just have "SELECT * FROM IntCommunications" it works fine but if I have even a simple "SELECT * FROM IntCommunications WHERE Read = False" the I get the infamous error '80004005'.
Any idea why?
Cheers.

#11

April 11th, 2008, 11:28 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

My guess is the use of "false". I always use a numeric test, since the false keyword may not exist in a db language. Access is the only db I use that supports true/false instead of a numeric boolean value.

Beyond that, you should turn off "show friendly http errors" in your IE settings and the 80004005 error should return th complete error description. There are a gazillion causes of 80004005.

#12

April 12th, 2008, 12:45 PM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

I've tried to use "Read = 0" (and also "Read = -1") instead of "Read = False" but I still get the error. If I remove that test all together then it works. Could it be that Read is a sort of reserved word?

BTW, the "show friendly http errors" in IE is off but I still get no description. Also, I mainly use Firefox when possible :-)

Cheers.

#13

April 13th, 2008, 11:56 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716

Time spent in forums: 1 Month 33 m 54 sec
Reputation Power: 688

Quote:

Could it be that Read is a sort of reserved word?

Maybe. Google should turn up lists of reserved words in MS databases.

If you don't get complete error messages from 500 errors perhaps your server admin has disabled IIS detailed error reporting.

#14

April 15th, 2008, 03:34 PM

gpt

Contributing User

Join Date: May 2004

Posts: 38

Time spent in forums: 12 h 39 m 43 sec
Reputation Power: 5

Quote:

Originally Posted by Doug G

Maybe. Google should turn up lists of reserved words in MS databases.

If you don't get complete error messages from 500 errors perhaps your server admin has disabled IIS detailed error reporting.

It doesn't look like it's a reserved word.

As for the IIS, I'm running it on my PC on Vista and it doesn't look like it's disabled. Yet I couldn't find the specific option...

#15

April 16th, 2008, 12:42 AM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,716