CODEWITHSUNDEEP
c#vb.net
Essa Alatwan
Essa Alatwan

Reputation: 1

How to avoid adding or inserting information twice to access database in Visual Basic?

I am trying to add some data that I get from some labls and text boxes. but when I click the button twice or more. I get the same rows in the database. This is my code:

{  Public Class PaymentForm

Public Con1 As New OleDb.OleDbConnection
    Public Da1 As OleDb.OleDbDataAdapter
    Public Cb1 As OleDb.OleDbCommandBuilder
    Public Dt1 As New DataTable

    Private Sub PaymentForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load



        Con1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Essa\Desktop\COMP6001-GUI Programming\Assessment_3\2- Assessment_3_Eissa_Alatwan\SitStay_Business.mdb"

        Con1.Open()

        Da1 = New OleDb.OleDbDataAdapter("select * from Payments", Con1)
        Cb1 = New OleDb.OleDbCommandBuilder(Da1)
        Da1.Fill(Dt1)

    End Sub

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click

        Dim new_rec As DataRow = Dt1.NewRow()

            new_rec("BookingID") = txtBookingID.Text
            new_rec("ClientID") = lblClientID.Text
            new_rec("DogName") = lblDogName.Text
            new_rec("CourseCharge") = CDbl(lblCourseCharge.Text)
            new_rec("AdditionalCharges") = CDbl(lblAdditionalCharges.Text)
            new_rec("Subtotal") = CDbl(lblSubtotal.Text)
            new_rec("Tax") = CDbl(lblTax.Text)
            new_rec("Total") = CDbl(lblTotalCharge.Text)
            new_rec("DateAndTime") = lblDateAndTime.Text

            Dt1.Rows.Add(new_rec)
            Da1.Update(Dt1)
     End Sub
end class }

Upvotes: 0

Views: 303

Answers (1)

Casey Crookston
Casey Crookston

Reputation: 13955

A couple of thoughts. First, before you begin saving the data, remove the button. This way when the page is posted back, the user can't hit it again. Replace it with a message such as, "Your booking has been saved. Thank you!"

Second, even with the button gone, it might be possible for the same booking to be saved again. So before you save, you'll want to query your DB to see if that booking already exists. I don't know your data well enough to know what data points constitute a double booking, but in your method:

Private Sub btnSave_Click()

before doing the save, do a SELECT query with a WHERE clause with enough data to sniff out a unique booking. If it returns more than zero rows, then the booking already exists. In which case, don't save. instead display a message that says, "Ooops! Looks like this is already booked" or whatever.

To do that select, look up how to do a SELECT COUNT(*) with an Execute Scalar. Execute Scalar will just return a single number (like a count) instead of a set of records.

Let us know if this works, and how it turns out!

Upvotes: 1

Related Questions