vbscript passing a Memo field from adodb connection returns Null after reading once 
Author Message
 vbscript passing a Memo field from adodb connection returns Null after reading once

I am new to using VBScript in a active server page so I'm not sure if it has
been posted before.

I have a asp that opens and reads the fields quite happily from a Access
database.

However I've come across an interesting situation where once you reference a
field of type Memo in Access, it return Null after you reference it the
first time.

I came across it while trying to print a "-" charater is the field was Null
else print the field.. Below is the vbscript without the html stuff that
gets sent to the user.


<!--#include file="adovbs.inc"-->
<%

'I've placed a copy of the adovbs.inc file at the base of the web so that it
uses the
'named parameters in the Open statement

Dim conDB
Dim datBathrooms, rstBathrooms
dim dateLower, dateUpper

dateLower = "1 May 2001"
dateUpper = "31 May 2001"
strRenovatorID = "POL01"

Set conDB = Server.CreateObject("ADODB.Connection")
conDB.ConnectionTimeout = 15
conDB.CommandTimeout = 30
conDB.Open "DBQ=" & Server.MapPath("fpdb/sabathrooms.mdb") &
";Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"

Set datBathrooms= Server.CreateObject("ADODB.Command")
datBathrooms.ActiveConnection = conDB
datBathrooms.CommandText = "SELECT * FROM [Bathrooms] WHERE
((([Bathrooms].[RenovatorID])='" & strRenovatorID & "') AND
(([Bathrooms].[LeadDate]) BETWEEN #" & dateLower  & "# AND #" & dateUpper &
"#)) ORDER BY [Bathrooms].[LeadDate], [Bathrooms].[LeadTime];"
datBathrooms.CommandType = 1
Set rstBathrooms = Server.CreateObject("ADODB.Recordset")
rstBathrooms.Open datBathrooms, ,adOpenStatic,adLockOptimistic

if not rstBathrooms.EOF then
 rstBathrooms.MoveFirst
 do until rstBathrooms.EOF

' The If statement will check if the Access memo field called Notes is Null
' however when it is not Null the field rstBathrooms.fields("Notes") has the
' value Null after the If statement. Note : the Access record is not
affected.
'         if isnull(rstBathrooms.fields("Notes")) then
'            %>-<%
'         else
'            response.write rstBathrooms.fields("Notes")
'         end if

' Here if we set a variable to the value of rstBathrooms.fields("Notes")
then
' even though rstBathrooms.fields("Notes") is set to Null it doesn't matter
' as we have stored it's value.
         dim strNotes
         strNotes = rstBathrooms.fields("Notes")
         if isnull(strNotes) then
            %>-<%
         else
            response.write strNotes
         end if
      rstBathrooms.MoveNext
   loop
end if

 rstBathrooms.Close
 set rstBathrooms= Nothing
 set datBathrooms = Nothing

 conDB.Close
 Set conDB = Nothing

 %>



Mon, 03 Nov 2003 13:16:03 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

2. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

3. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

4. Weird problems with MEMO fields in ADODB

5. Accessing empty/null Memo Fields in a Access 97

6. Problem returning data from a scroll text box to an Access memo field

7. ADODB.Command Output Params return null for GUID types

8. adodb connection to an oracle not returning any records

9. Problem reading data from memo field (Access)

10. Append memo field to a different memo field

11. Accessing NULL VFP memo fields

12. VB6 ADODB.Recordset.Field = NULL Error

 

 
Powered by phpBB® Forum Software