Best way to implement a customer notes system...

slight

New member
Joined
Apr 11, 2019
Messages
1
Programming Experience
Beginner
I am a beginner with vb.net and am porting an program I wrote on a old mainframe computer years ago to VB.net. The software is a utility billing system that I want to store notes about a customer and meters that is stored in a access database (for now with future upgrade to some other database).

I have my tables created, one for customers, one for meters, one for customer notes and one for meter notes. I can insert, update and select to these tables.

My question is what is the best way to enter, save and display these notes for the customers and meters. I want to store the date/time of when the note was added.

Right now I have:
customer notes table
customer acct no field
date/time added field
customer notes field

The table will have duplicate enters for each customer depending on how many notes are added for the customer.

My question is how is the best way to retrieve the date/time and the notes from the database and display them on the form in a textbox.

Below is the current code that I can select one row from the table, combine and display in a text box:

VB.NET:
Private Sub FindCustomerNotes()
        Try
            Dim drCustomerNotes As OleDbDataReader = Nothing

            With cm
                .CommandText = "SELECT * FROM tblCusNotes WHERE fldCusNotesAcctNo = @CusAcctNo"
                Try
                    .Parameters.Clear()
                    .Parameters.Add("@CusAcctNo", OleDbType.Integer).Value = tbCusAcctNo.Text
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical)
                End Try
                drCustomerNotes = .ExecuteReader
            End With
            MsgBox("Cus Notes Start read.", MsgBoxStyle.Information)
            While drCustomerNotes.Read()

                CusNotesTemp = drCustomerNotes("fldCusNotesDateEntered").ToString
                CusNotesTemp = CusNotesTemp + vbCrLf + drCustomerNotes("fldCusNotesText").ToString
                tbCusNotes.Text = CusNotesTemp

                CusNotesfound = True
            End While

            If CusNotesfound = True Then MsgBox("Cus Notes Info Found.", MsgBoxStyle.Information)
            If CusNotesfound = False Then MsgBox("Cus Notes Info Not Found.", MsgBoxStyle.Information)
            drCustomerNotes.Close()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            errornumber = 23 'Assigned error number for logging & debugging.
            clsLogFile = New LogFile(ex.Message, errornumber)
        End Try
        If CusNotesfound = False Then MsgBox("No Cus Notes Info Found.", MsgBoxStyle.Information)

    End Sub

Any help or guidance would be appreciated.
 
Last edited by a moderator:
Back
Top