• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Check for no records or only one record in Access DB


I am working on a project that takes input data and saves it to a database in Access using ADO.
I have the program working correctly until I take out the records and check for no records. It crashes on the totals. Please see below

Dim pintRecords As Integer
        Dim pbndTemp As Binding

        lblDisplay.Visible = True
        lblAddMode.Visible = False
        lblEditMode.Visible = False
        pintRecords = oledbCustomers1.Fill(DsCustomers1)
        btnPrevious.Enabled = False

        ' Populate the DataSet with records using the DataAdapter. This DataAdapter was
        ' configured using the Wizard supplied by the OleDbDataAdapter control.

        ' Set up the bindings for each control instance. For each control instance, create
        ' a Binding object. Specify the property to bind (Text), the DataSet, and the
        ' navigation path to the field. Scond, add the binding the the control instance's
        ' DataBindings collection.


            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldCustomerID")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldFirstName")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldLastName")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldAddress")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldCity")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldState")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldZipCode")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldAnnualSales")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldBalanceDue")

            pbndTemp = New Binding("Text", DsCustomers1, "tblCustomers.fldCreditLimit")
        Call EditState(cblnNotEditing)
        Call Totals()

        If txtCustID.Text = "" Then
            MessageBox.Show("There are no records currently in this database")
            lblOverLimit.Visible = False
            btnAdd.Enabled = False
            btnEdit.Enabled = True
            btnsave.Enabled = True
            btnCancel.Enabled = False
            btnDelete.Enabled = False

            Exit Sub
        End If
        End Sub

****For the totals I have the following:

Private Sub Totals()
        Dim mdblmonthSal As Double
        Dim mdblannualSales As Double
        Dim mdblCreditLimit As Double
        Dim mdblBalDue As Double
        Dim mdblAvailBal As Double

        mdblannualSales = ToDouble(txtAnnSales.Text)  ***************CRASHES ON THIS LINE
        mdblmonthSal = mdblannualSales / 12
        txtMonth.Text = mdblmonthSal.ToString("###,###.##")

        mdblCreditLimit = ToDouble(txtCredit.Text)
        mdblBalDue = ToDouble(txtBalDue.Text)

        mdblAvailBal = mdblCreditLimit - mdblBalDue
        txtBalAvail.Text = mdblAvailBal.ToString("###,###.##")

        If mdblCreditLimit = mdblBalDue Then
            txtBalAvail.Text = "Zero Balance"
        End If
        Call Overlimit()
    End Sub

*********The Form Load is looking at the temp holding variable (mdblCreditLimit)  and telling me the input text is incorrect. Should be ToString.
Also how would I check for only one record in the Access DB.

Thanks for your help.
1 Solution
You could do something like this....

Dim pintRecords As Integer
        Dim pbndTemp As Binding

        lblDisplay.Visible = True
        lblAddMode.Visible = False
        lblEditMode.Visible = False
        pintRecords = oledbCustomers1.Fill(DsCustomers1)
        btnPrevious.Enabled = False

        if DsCustomers1.Tables(0).Rows.Count <= 1 then
              messagebox("I have only one or less records")
              exit sub
       end if
I would change the code above with the name of the table, becaouse I wouldn't know if the table with the index 0 is the table that itcopt wants to check:

Select Case DsCustomers1.Tables("Table_Name").Rows.Count
  case 0:
    msgbox("The table is empty")
  case 1:
    msgbox("You have only 1 record")
  case Else:
    'The execution will be here if you have 2 or more records
End Select
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now