2 Replies - 337 Views - Last Post: 29 October 2017 - 02:15 PM Rate Topic: -----

#1 jemerald   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 24-October 17

Nullity Checking in Retrieve Customer Record

Posted 29 October 2017 - 01:45 AM

Can someone help me to fix the code. This is only part of the whole code. It is to retrieve collection of Customers (array-form). When I run the program the message box "Failed to retrieve All Customer Record" only shows, so I think the part which is wrong is here.
Some said that I should put nullity checking using IIf wherein if it's null it should be blank, otherwise get the value. But still the message box only shows.

Note: clsCustomer is a class.

Public Function retrieveAllCustomers() As clsCustomer()
        On Error GoTo errHandler

        Dim CustomerArray(0) As clsCustomer  
        Dim SqlStatement As New OleDb.OleDbCommand
        Dim CustomerRecordReader As OleDb.OleDbDataReader 
        RowIndex As Integer

        CustomerArray(0) = Nothing
        If IsConnectedToDatabase() = True Then

            SqlStatement.Connection = DBConnection
            SqlStatement.CommandText = "SELECT * FROM Customers ORDER BY AccountNo,LastName,FirstName "
            CustomerRecordReader = SqlStatement.ExecuteReader

            RowIndex = 0
            While CustomerRecordReader.Read = True

                ReDim Preserve CustomerArray(RowIndex)
                CustomerArray(RowIndex) = New clsCustomer
                CustomerArray(RowIndex).ID = CLng(CustomerRecordReader.Item("ID"))
                CustomerArray(RowIndex).AccountNo = IIf(IsDBNull(CustomerRecordReader.Item("AccountNo")), String.Empty, (CustomerRecordReader.Item("AccountNo"))) 
                CustomerArray(RowIndex).LastName = IIf(IsDBNull(CustomerRecordReader.Item("LastName")), String.Empty, (CustomerRecordReader.Item("LastName")))
                CustomerArray(RowIndex).FirstName = IIf(IsDBNull(CustomerRecordReader.Item("FirstName")), String.Empty, (CustomerRecordReader.Item("FirstName")))
                CustomerArray(RowIndex).Address = IIf(IsDBNull(CustomerRecordReader.Item("Address")), String.Empty, (CustomerRecordReader.Item("Address")))
                CustomerArray(RowIndex).PIN = IIf(IsDBNull(CustomerRecordReader.Item("PIN")), "0000", (CustomerRecordReader.Item("PIN")))
                CustomerArray(RowIndex).AccountBalance = IIf(IsDBNull(CustomerRecordReader.Item("AccountBalance")), 0, (CustomerRecordReader.Item("AccountBalance")))
                RowIndex = RowIndex + 1
            End While

        End If
        retrieveAllCustomers = CustomerArray

        Exit Function
        MsgBox("Failed to retrieve All Customer Record", MsgBoxStyle.Information)
    End Function

Is This A Good Question/Topic? 0
  • +

Replies To: Nullity Checking in Retrieve Customer Record

#2 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,715
  • Joined: 12-December 12

Re: Nullity Checking in Retrieve Customer Record

Posted 29 October 2017 - 02:02 AM

The On Error Goto construct is an artefact from VB6 and should not be used. Use proper Try.. Catch error handling.

When you catch an exception you need to do something with it, not display an uninformative message as you are currently.

To make some immediate progress, remove the On Error ... and let the exception happen so you can see the error details.
Was This Post Helpful? 0
  • +
  • -

#3 mtnerd594   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 22-May 16

Re: Nullity Checking in Retrieve Customer Record

Posted 29 October 2017 - 02:15 PM

Surround your While . . . End While statement with:

If CustomerRecordReader.HasRows Then

     While . . .

     End While

End If

Using the HasRows check makes sure there is a row of data. As long as it has a row, then the values will return values.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1