Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vb.Net and Access Memo Field problem

Posted on 2004-10-27
8
Medium Priority
?
590 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
Comment
Question by:stephenz43
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 12430452
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:
Dhaest earned 252 total points
ID: 12430480
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
Arthur_Wood earned 248 total points
ID: 12443701
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
ID: 12816501
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
ID: 12824059
thanks for the vote of conficence, Dhaest.

AW
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

618 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