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)
            MsgBox(r("Empl_ID").ToString)
        End If
        MsgBox("Record created sucessfully")

        txtboxEmplID.Clear()
        txtboxLastName.Clear()
        txtboxFirstName.Clear()
        txtboxEmailID.Clear()
        txtboxSupervisor.Clear()
        txtboxSupervisorID.Clear()
        txtboxManager.Clear()
        txtboxManagerID.Clear()
        txtboxSupervisorEmail.Clear()
        txtboxMgrEmail.Clear()
        txtSearch.Clear()
        LoadGrid()



    End Sub
DJ PAsked:
Who is Participating?
 
Dirk StraussConnect With a Mentor Senior 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.
0
 
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)
  con.Open
  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
    Else
      '/ 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.
0
 
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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.
0
 
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 = ""

        Try

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

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

            'Clear Parameter list
            params.Clear()

            '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
        Finally
            '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)
        params.Add(newparam)

    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
0
 
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.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE tbl_Supervisors SET Last_Name = @Last_Name, [YOUR OTHER FIELDS HERE] WHERE Empl_ID = @Empl_ID;
IF @@ROWCOUNT = 0
BEGIN
  INSERT tbl_Supervisors(Empl_ID,Last_Name,First_Name,[YOUR OTHER FIELDS HERE])
  Values (@Empl_ID,@Last_Name,@First_Name,[YOUR OTHER VALUES HERE]);
END
COMMIT TRANSACTION;

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.
0
 
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.
0
 
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
sqlCon.Open()
SQL.ExecuteNonQuery()

Open in new window

Change StoredProcName to the name of your stored procedure. I'm sorry, but my VB.NET is really rusty.
0
 
DJ PAuthor Commented:
correct. but I still don't see where the check of a duplicate value can comes into play.
0
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.

All Courses

From novice to tech pro — start learning today.