asked on

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

Scott McDaniel (EE MVE )

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.

DJ P

ASKER

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 (EE MVE )

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.

DJ P

ASKER

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

Dirk Strauss

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.

DJ P

ASKER

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 Strauss

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.

DJ P

ASKER

correct. but I still don't see where the check of a duplicate value can comes into play.

ASKER CERTIFIED SOLUTION

Dirk Strauss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial