Button to check for duplicates before saving record

Good afternoon experts. Newbie here to vb.net. I created a form with ms visual studio 2017 community addition. The form contains a datagridview that connects to a sql database (sql 2008). On my form I have several text boxes that are used to enter data which then populates my grid via a "add record" button. The connection to my sql database is handled in a class I called SQLControl.vb (if that matters). Listed below is my code for the add record button. So far everything works great and now what I need is a check to prevent duplicate data from being entered so I am looking for a check against my text box and datagridview. The field (not a primary key) I would like to validate against is called EMPL_ID (employee id). I don't know how to accomplish what I'm sure is a simple task.

Here is the code behind the button:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If txtboxEmplID.Text = "" Or txtboxLastName.Text = "" Or txtboxFirstName.Text = "" Or txtboxEmailID.Text = "" Or txtboxSupervisor.Text = "" Or txtboxSupervisorID.Text = "" Or txtboxManager.Text = "" Or txtboxManagerID.Text = "" Or txtboxSupervisorEmail.Text = "" Or txtboxMgrEmail.Text = "" Then
            MsgBox("All Fields Are Required", MsgBoxStyle.Information)
            Exit Sub
        End If

        'Add SQL Params & Run The Command
        SQL.AddParam("@Empl_ID", txtboxEmplID.Text)
        SQL.AddParam("@Last_Name", txtboxLastName.Text)
        SQL.AddParam("@First_Name", txtboxFirstName.Text)
        SQL.AddParam("@Email_ID", txtboxEmailID.Text)
        SQL.AddParam("@Supervisor", txtboxSupervisor.Text)
        SQL.AddParam("@SupervisorID", txtboxSupervisorID.Text)
        SQL.AddParam("@Manager", txtboxManager.Text)
        SQL.AddParam("@ManagerID", txtboxManagerID.Text)
        SQL.AddParam("@SPVSR_Email_ID", txtboxSupervisorEmail.Text)
        SQL.AddParam("@Mgr_Email_ID", txtboxMgrEmail.Text)

        SQL.ExecQuery("Insert Into tbl_Supervisors (Empl_ID,Last_Name,First_Name,Email_ID,Supervisor,SupervisorID,Manager,ManagerID,SPVSR_Email_ID,MGR_Email_ID) " &
                      "Values (@Empl_ID,@Last_Name,@First_Name,@Email_ID,@Supervisor,@SupervisorID,@Manager,@ManagerID,@SPVSR_Email_ID,@MGR_Email_ID);", True)

        'Report & Abort On Errors
        If SQL.hasException(True) Then Exit Sub
        If SQL.DBDT.Rows.Count > 0 Then
            Dim r As DataRow = SQL.DBDT.Rows(0)
        End If
        MsgBox("Record created sucessfully")


    End Sub
DJ PAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check for duplicates by executing a COUNT query with the appropriate filtering. For example, if I wanted to determine if a record existed where Customer_Name = "Ford", I'd to something like this:

Using con As New SQLConnection(MyConnectString)
  Using cmd As New SQLCommand
    cmd.Connection = con
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable WHERE Customer_Name='Ford'"
    If cmd.ExecuteScalar > 0 Then
      '/ the record exists
      '/ the record does not exist
    End If
  End Using
End Using

I don't know how you'd use that with your SQLControl module, however.
DJ PAuthor Commented:
That not really what I am looking for or due to my limited programming knowledge that's over my head at this point. Let me put it like this. In my datagrid I have employee an column called "Empl_ID (same name as column in my table. In my form I have a text box called txtboxEmplID

Can't I just say something like this in my add button per my code above? if EMPl_ID = txtboxEmplID then msg box "Record Already exists"?

The syntax and where to place it in my code above is what my issue is.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That would only compare your entered data against a single value (the value in your textbox). I doubt that will get you where you need to be.

Can you show the code for SQLControl.vb? There may be a process in that module that would help you to do this.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

DJ PAuthor Commented:
SQLControl.vb code

Imports System.Data.SqlClient
Public Class SQLControl
    Private DBCon As New SqlConnection("Server=Test;Database=HRTest Security=True")
    Private DBCmd As SqlCommand

    'DB Data
    Public DBDA As SqlDataAdapter
    Public DBDT As DataTable

    'Query Parameters
    Public params As New List(Of SqlParameter)

    'Query Stats

    Public RecordCount As Integer
    Public Exception As String

    Public Sub New()
    End Sub

    'Allow Connection string override
    Public Sub New(connectionstring As String)
        DBCon = New SqlConnection(connectionstring)
    End Sub

    'Execute Query Sub

    Public Sub ExecQuery(query As String, Optional returnidentity As Boolean = False)
        'Reset Query Stats
        RecordCount = 0
        Exception = ""


            'Create DB Command
            DBCmd = New SqlCommand(query, DBCon)

            'Load params into db command
            params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            'Clear Parameter list

            'Execute Command & fill Dataset
            DBDT = New DataTable
            DBDA = New SqlDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)

            'Prevents record locking
            If returnidentity = True Then
                Dim returnquery As String = "Select @@Identity as Empl_ID;"
                '@@Identity - Session
                DBCmd = New SqlCommand(returnquery, DBCon)
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBCmd)
                RecordCount = DBDA.Fill(DBDT)

            End If

        Catch ex As Exception
            'Capture Error
            Exception = "ExecQuery Error: " & vbNewLine & ex.Message
            'Close Connection
            If DBCon.State = ConnectionState.Open Then DBCon.Close()

        End Try

    End Sub

    'Add Params
    Public Sub AddParam(Name As String, Value As Object)
        Dim newparam As New SqlParameter(Name, Value)

    End Sub

    'Error Checking

    Public Function hasException(Optional Report As Boolean = False) As Boolean
        If String.IsNullOrEmpty(Exception) Then Return False
        If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
        Return True
    End Function

End Class
Dirk StraussSenior Full Stack DeveloperCommented:
I always check for the existance of a record on a database level. So I'm assuming that the data displayed in the grid is coming from your database and that you don't want to insert a duplicate record where the employee ID exists.

What I do is create a stored procedure. I then always do an UPDATE first where that ID exists (in your case @Empl_ID). If a record with that ID doesn't exist, @@ROWCOUNT will be 0. Then the stored procedure will perform an INSERT.

UPDATE tbl_Supervisors SET Last_Name = @Last_Name, [YOUR OTHER FIELDS HERE] WHERE Empl_ID = @Empl_ID;
  INSERT tbl_Supervisors(Empl_ID,Last_Name,First_Name,[YOUR OTHER FIELDS HERE])
  Values (@Empl_ID,@Last_Name,@First_Name,[YOUR OTHER VALUES HERE]);

Open in new window

I then wrap it in a transaction for good measure. It's faster to check the existance of a record this way than using IF EXISTS, because that then goes out and does a read on the table. So you waste a lookup on that table. If the table is big, it could give you a bit of a performance hit. Calling UPDATE first and then INSERT if nothing updated is faster and more efficient.
DJ PAuthor Commented:
Dirk, I somewhat follow. Pardon being a newbie here but if I create your recommended stored procedure, How does that get called from my "add record button"? Hope that makes sense.

Still a little lost on how to tie it all together.
Dirk StraussSenior Full Stack DeveloperCommented:
I'm assuming that SQL is a SqlCommand object?

Dim strConn As String = "your_connection_string"
Dim sqlCon As SqlConnection = New SqlConnection(strConn)

SQL = New SqlCommand
SQL.Connection = sqlCon
SQL.CommandText = "StoredProcName"
SQL.CommandType = CommandType.StoredProcedure

SQL.Parameters.AddWithValue("@Empl_ID", txtboxEmplID.Text)
SQL.Parameters.AddWithValue("@Last_Name", txtboxLastName.Text)

'Add the rest

Open in new window

Change StoredProcName to the name of your stored procedure. I'm sorry, but my VB.NET is really rusty.
DJ PAuthor Commented:
correct. but I still don't see where the check of a duplicate value can comes into play.
Dirk StraussSenior Full Stack DeveloperCommented:
The stored procedure syntax does that. If the employee ID exists in the database, the existing record will be updated. If no data changes on the app, then the update on the data table essentially has no effect.

If however the employee ID does not exist, the stored procedure inserts that new record into the table. The stored procedure guarantees that you will not have duplicate employee ID's in the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.