Button to check for duplicates before saving record

Posted on 2018-03-13
Low Priority
Last Modified: 2018-03-15
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
Question by:DJ P
  • 4
  • 3
  • 2
LVL 86
ID: 42498145
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.

Author Comment

by:DJ P
ID: 42498939
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.
LVL 86
ID: 42498951
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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

by:DJ P
ID: 42498962
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
LVL 11

Expert Comment

by:Dirk Strauss
ID: 42499118
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.

Author Comment

by:DJ P
ID: 42499181
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.
LVL 11

Expert Comment

by:Dirk Strauss
ID: 42499214
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.

Author Comment

by:DJ P
ID: 42499242
correct. but I still don't see where the check of a duplicate value can comes into play.
LVL 11

Accepted Solution

Dirk Strauss earned 1000 total points
ID: 42499372
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.

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.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question