Solved

vb.Net and Access Memo Field problem

Posted on 2004-10-27
493 Views
Last Modified: 2013-12-25
I have a small app that reads data from a access 2000 database. Everythign works except when I try to read a memo  field from the database that it empty. Heres what im doing

 sqlStatement = "SELECT Cust_FirstName, Cust_LastName,Cust_Address1," & _
        "Cust_City,Cust_State,Cust_zip,Cust_Year, Cust_Make, Cust_Model,Cust_notes " & _
        "FROM Master where str(trim(MasterId)) =  ' " & Trim(glbLookup) & " '"

        SetUpDataBase()
        Reader = cmd.ExecuteReader()
        While Reader.Read
            Label1.Text = Reader("Cust_FirstName") + "   " + Reader("Cust_LastName")
            Label2.Text = Reader("cust_Address1")
            Label3.Text = Reader("Cust_City") + "  " + Reader("Cust_state") + "  " + Reader("Cust_Zip")
            Label4.Text = Reader("Cust_Year") + "  " + Reader("Cust_make") + "  " + Reader("Cust_Model")
            RichTextBox1.Text = Reader("Cust_Notes") <------------CRASHES HERE ONLY IF MEMO FIELD IS EMPTY

any help would be appreciated as I am very very new to this stuff

Steve
0
Question by:stephenz43
    5 Comments
     
    LVL 53

    Expert Comment

    by:Dhaest
    Just try to add a check to see if it contains a null-value...
    something like this (i haven't got vb.net here, so there can be a syntax error)
     RichTextBox1.Text = iif(Reader.isDbNull("Cust_Notes") = true, "", reader("Cust_notes"))
    0
     
    LVL 53

    Accepted Solution

    by:
    Perhaps a better alternative (adding an error-handler)

                    Try
                        RichTextBox1.Text = Reader("Cust_Notes")
                    Catch ex As Exception
                         RichTextBox1.Text = ""
                    End Try
    0
     
    LVL 44

    Assisted Solution

    by:Arthur_Wood
    an even better solution is to use this class:


    Imports System.Data

    ''' <summary>
    '''
    ''' </summary>
    Namespace Data

      ''' <summary>
      ''' This is a DataReader that 'fixes' any null values before
      ''' they are returned to our business code.
      ''' </summary>
      Public Class SafeDataReader

        Implements IDataReader

        Private mDataReader As IDataReader

        ''' <summary>
        ''' Initializes the SafeDataReader object to use data from
        ''' the provided DataReader object.
        ''' </summary>
        ''' <param name="DataReader">The source DataReader object containing the data.</param>
        Public Sub New(ByVal DataReader As IDataReader)
          mDataReader = DataReader
        End Sub

        ''' <summary>
        ''' Gets a string value from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns "" for null.
        ''' </remarks>
        Public Function GetString(ByVal i As Integer) As String Implements IDataReader.GetString
          If mDataReader.IsDBNull(i) Then
            Return ""
          Else
            Return mDataReader.GetString(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a value of type <see cref="System.Object" /> from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns Nothing for null.
        ''' </remarks>
        Public Function GetValue(ByVal i As Integer) As Object Implements IDataReader.GetValue
          If mDataReader.IsDBNull(i) Then
            Return Nothing
          Else
            Return mDataReader.GetValue(i)
          End If
        End Function

        ''' <summary>
        ''' Gets an integer from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns 0 for null.
        ''' </remarks>
        Public Function GetInt32(ByVal i As Integer) As Integer Implements IDataReader.GetInt32
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetInt32(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a double from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns 0 for null.
        ''' </remarks>
        Public Function GetDouble(ByVal i As Integer) As Double Implements IDataReader.GetDouble
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetDouble(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a <see cref="T:CSLA.SmartDate" /> from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' A null is converted into either the min or max possible date
        ''' depending on the MinIsEmpty parameter. See Chapter 5 for more
        ''' details on the SmartDate class.
        ''' </remarks>
        ''' <param name="i">The column number within the datareader.</param>
        ''' <param name="MinIsEmpty">A flag indicating whether the min or max value of a data means an empty date.</param>
        Public Function GetSmartDate(ByVal i As Integer, Optional ByVal MinIsEmpty As Boolean = True) As SmartDate
          If mDataReader.IsDBNull(i) Then
            Return New SmartDate(MinIsEmpty)

          Else
            Return New SmartDate(mDataReader.GetDateTime(i), MinIsEmpty)
          End If
        End Function

        ''' <summary>
        ''' Gets a Guid value from the datareader.
        ''' </summary>
        Public Function GetGuid(ByVal i As Integer) As Guid Implements IDataReader.GetGuid
          If mDataReader.IsDBNull(i) Then
            Return Guid.Empty
          Else
            Return mDataReader.GetGuid(i)
          End If
        End Function

        ''' <summary>
        ''' Reads the next row of data from the datareader.
        ''' </summary>
        Public Function Read() As Boolean Implements IDataReader.Read
          Return mDataReader.Read
        End Function

        ''' <summary>
        ''' Moves to the next result set in the datareader.
        ''' </summary>
        Public Function NextResult() As Boolean Implements IDataReader.NextResult
          Return mDataReader.NextResult()
        End Function

        ''' <summary>
        ''' Closes the datareader.
        ''' </summary>
        Public Sub Close() Implements IDataReader.Close
          mDataReader.Close()
        End Sub

        ''' <summary>
        ''' Returns the depth property value from the datareader.
        ''' </summary>
        Public ReadOnly Property Depth() As Integer Implements System.Data.IDataReader.Depth
          Get
            Return mDataReader.Depth
          End Get
        End Property

        ''' <summary>
        ''' Calls the Dispose method on the underlying datareader.
        ''' </summary>
        Public Sub Dispose() Implements System.IDisposable.Dispose
          mDataReader.Dispose()
        End Sub

        ''' <summary>
        ''' Returns the FieldCount property from the datareader.
        ''' </summary>
        Public ReadOnly Property FieldCount() As Integer Implements System.Data.IDataRecord.FieldCount
          Get
            Return mDataReader.FieldCount
          End Get
        End Property

        ''' <summary>
        ''' Gets a boolean value from the datareader.
        ''' </summary>
        Public Function GetBoolean(ByVal i As Integer) As Boolean Implements System.Data.IDataRecord.GetBoolean
          If mDataReader.IsDBNull(i) Then
            Return False
          Else
            Return mDataReader.GetBoolean(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a byte value from the datareader.
        ''' </summary>
        Public Function GetByte(ByVal i As Integer) As Byte Implements System.Data.IDataRecord.GetByte
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetByte(i)
          End If
        End Function

        ''' <summary>
        ''' Invokes the GetBytes method of the underlying datareader.
        ''' </summary>
        Public Function GetBytes(ByVal i As Integer, ByVal fieldOffset As Long, ByVal buffer() As Byte, ByVal bufferoffset As Integer, ByVal length As Integer) As Long Implements System.Data.IDataRecord.GetBytes
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetBytes(i, fieldOffset, buffer, bufferoffset, length)
          End If
        End Function

        ''' <summary>
        ''' Gets a char value from the datareader.
        ''' </summary>
        Public Function GetChar(ByVal i As Integer) As Char Implements System.Data.IDataRecord.GetChar
          If mDataReader.IsDBNull(i) Then
            Return Char.MinValue
          Else
            Return mDataReader.GetChar(i)
          End If
        End Function

        ''' <summary>
        ''' Invokes the GetChars method of the underlying datareader.
        ''' </summary>
        Public Function GetChars(ByVal i As Integer, ByVal fieldoffset As Long, ByVal buffer() As Char, ByVal bufferoffset As Integer, ByVal length As Integer) As Long Implements System.Data.IDataRecord.GetChars
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetChars(i, fieldoffset, buffer, bufferoffset, length)
          End If
        End Function

        ''' <summary>
        ''' Invokes the GetData method of the underlying datareader.
        ''' </summary>
        Public Function GetData(ByVal i As Integer) As System.Data.IDataReader Implements System.Data.IDataRecord.GetData
          Return mDataReader.GetData(i)
        End Function

        ''' <summary>
        ''' Invokes the GetDataTypeName method of the underlying datareader.
        ''' </summary>
        Public Function GetDataTypeName(ByVal i As Integer) As String Implements System.Data.IDataRecord.GetDataTypeName
          Return mDataReader.GetDataTypeName(i)
        End Function

        ''' <summary>
        ''' Gets a date value from the datareader.
        ''' </summary>
        Public Function GetDateTime(ByVal i As Integer) As Date Implements System.Data.IDataRecord.GetDateTime
          If mDataReader.IsDBNull(i) Then
            Return Date.MinValue
          Else
            Return mDataReader.GetDateTime(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a decimal value from the datareader.
        ''' </summary>
        Public Function GetDecimal(ByVal i As Integer) As Decimal Implements System.Data.IDataRecord.GetDecimal
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetDecimal(i)
          End If
        End Function

        ''' <summary>
        ''' Invokes the GetFieldType method of the underlying datareader.
        ''' </summary>
        Public Function GetFieldType(ByVal i As Integer) As System.Type Implements System.Data.IDataRecord.GetFieldType
          Return mDataReader.GetFieldType(i)
        End Function

        ''' <summary>
        ''' Gets a Single value from the datareader.
        ''' </summary>
        Public Function GetFloat(ByVal i As Integer) As Single Implements System.Data.IDataRecord.GetFloat
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetFloat(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a Short value from the datareader.
        ''' </summary>
        Public Function GetInt16(ByVal i As Integer) As Short Implements System.Data.IDataRecord.GetInt16
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetInt16(i)
          End If
        End Function

        ''' <summary>
        ''' Gets a Long value from the datareader.
        ''' </summary>
        Public Function GetInt64(ByVal i As Integer) As Long Implements System.Data.IDataRecord.GetInt64
          If mDataReader.IsDBNull(i) Then
            Return 0
          Else
            Return mDataReader.GetInt64(i)
          End If
        End Function

        ''' <summary>
        ''' Invokes the GetName method of the underlying datareader.
        ''' </summary>
        Public Function GetName(ByVal i As Integer) As String Implements System.Data.IDataRecord.GetName
          Return mDataReader.GetName(i)
        End Function

        ''' <summary>
        ''' Gets an ordinal value from the datareader.
        ''' </summary>
        Public Function GetOrdinal(ByVal name As String) As Integer Implements System.Data.IDataRecord.GetOrdinal
          Return mDataReader.GetOrdinal(name)
        End Function

        ''' <summary>
        ''' Invokes the GetSchemaTable method of the underlying datareader.
        ''' </summary>
        Public Function GetSchemaTable() As System.Data.DataTable Implements System.Data.IDataReader.GetSchemaTable
          Return mDataReader.GetSchemaTable
        End Function

        ''' <summary>
        ''' Invokes the GetValues method of the underlying datareader.
        ''' </summary>
        Public Function GetValues(ByVal values() As Object) As Integer Implements System.Data.IDataRecord.GetValues
          Return mDataReader.GetValues(values)
        End Function

        ''' <summary>
        ''' Returns the IsClosed property value from the datareader.
        ''' </summary>
        Public ReadOnly Property IsClosed() As Boolean Implements System.Data.IDataReader.IsClosed
          Get
            Return mDataReader.IsClosed
          End Get
        End Property

        ''' <summary>
        ''' Invokes the IsDBNull method of the underlying datareader.
        ''' </summary>
        Public Function IsDBNull(ByVal i As Integer) As Boolean Implements System.Data.IDataRecord.IsDBNull
          Return mDataReader.IsDBNull(i)
        End Function

        ''' <summary>
        ''' Returns a value from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns Nothing if the value is null.
        ''' </remarks>
        Default Public Overloads ReadOnly Property Item(ByVal name As String) As Object Implements System.Data.IDataRecord.Item
          Get
            Dim value As Object = mDataReader.Item(name)
            If DBNull.Value.Equals(value) Then
              Return Nothing
            Else
              Return value
            End If
          End Get
        End Property

        ''' <summary>
        ''' Returns a value from the datareader.
        ''' </summary>
        ''' <remarks>
        ''' Returns Nothing if the value is null.
        ''' </remarks>
        Default Public Overloads ReadOnly Property Item(ByVal i As Integer) As Object Implements System.Data.IDataRecord.Item
          Get
            If mDataReader.IsDBNull(i) Then
              Return Nothing
            Else
              Return mDataReader.Item(i)
            End If
          End Get
        End Property

        ''' <summary>
        ''' Returns the RecordsAffected property value from the underlying datareader.
        ''' </summary>
        Public ReadOnly Property RecordsAffected() As Integer Implements System.Data.IDataReader.RecordsAffected
          Get
            Return mDataReader.RecordsAffected
          End Get
        End Property
      End Class

    End Namespace


    which resolves ALL of the potential problems that can arise when accessing data via a standard DataReader.

    It is used like this"

    Dim dr as New SafeDataReader(cmd.ExecuteReader)

    so your code would look like this:
            Dim Reader as New SafeDataReader(cmd.ExecuteReader)
            While Reader.Read
                Label1.Text = Reader("Cust_FirstName") + "   " + Reader("Cust_LastName")
                Label2.Text = Reader("cust_Address1")
                Label3.Text = Reader("Cust_City") + "  " + Reader("Cust_state") + "  " + Reader("Cust_Zip")
                Label4.Text = Reader("Cust_Year") + "  " + Reader("Cust_make") + "  " + Reader("Cust_Model")
                RichTextBox1.Text = Reader("Cust_Notes")
           Loop


    The SafeDataReader class is taken from

    Visual Basic.NET Business Objects
    Rockford Lhotka
    APress
    ISBN 1-59059-145-3

    AW
    0
     
    LVL 53

    Expert Comment

    by:Dhaest
    My recommandation. Split points between  Arthur_Wood (100 points because the most complete answer) and myself (dhaest - 25 points)
    0
     
    LVL 44

    Expert Comment

    by:Arthur_Wood
    thanks for the vote of conficence, Dhaest.

    AW
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    845 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now