Restrict Duplicate Enties in SQL Database

Question

tashiDuks 0 Light Poster

13 Years Ago

Hi EveryOne,

I have some problem in validating duplicate values while saving & updating my records to SQL database. I have following codes:

1. Decalaration Code

#Region "Declarations"
    'Declaration for Navigation
    Dim inc As Integer
    Dim maxRows As Integer
    Dim ds As DataSet = New DataSet
    Dim dvEmpDesignation As DataView
    Dim da As New OleDbDataAdapter
    Dim trns As OleDbTransaction
    Dim bNewData, bEditData As Boolean
    Dim iFocusedRecID As Integer
    Dim connStr As String = ("Provider = SQLOLEDB;Data Source = TASHI\HRMS;Initial Catalog = HRBDFCL;User ID = sa;" & _
                             "Password = suma2010;Persist Security Info = TRUE;")
    Dim conn As OleDbConnection = New OleDbConnection(connStr)
#End Region

2. Connection String Function

#Region "GetSQLStr()"
    Private Function GetSQLStr() As String
        Dim sDate As String = CType(DateAndTime.Now, String)
        If bNewData Then
            GetSQLStr = "INSERT INTO dbo.hrEmpDesignation(DesID,DesName,DesShortName,DesDescription,DesSetDate,DesIsDefault) VALUES ('" & _
            edsID.Text & "','" & edsDesigName.Text & "','" & edsDesShortName.Text & "','" & edsDescription.Text & "', " & _
            "'" & sDate & "','" & edsDefaultCheckbox.CheckState & "')"
        Else
            GetSQLStr = "UPDATE dbo.hrEmpDesignation SET DesName ='" & edsDesigName.Text & "',DesShortName = '" & edsDesShortName.Text & "'," & _
            "DesDescription = '" & edsDescription.Text & "',DesSetDate= '" & sDate & "',DesIsDefault = '" & edsDefaultCheckbox.CheckState & "' WHERE DesID = '" & edsID.Text & "'"
        End If
    End Function
    Private Function DelSQLStr() As String
        DelSQLStr = "DELETE FROM dbo.hrEmpDesignation WHERE DesID='" & edsID.Text & "'"
    End Function
#End Region

3. Get Data

#Region "GetData()"
    Private Sub GetData()
        Try
            ds.Clear() 'Clear the dataset
            Dim SQLStr As String
            Dim desTableName1 As String
            desTableName1 = "dbo.hrEmpDesignation"
            SQLStr = "SELECT DesID,DesName,DesShortName,DesDescription,DesIsDefault FROM dbo.hrEmpDesignation ORDER BY DesID ASC"
            da = New OleDbDataAdapter(SQLStr, conn)
            da.Fill(ds, desTableName1)
            Dim dvm As DataViewManager = New DataViewManager(ds)
            dvEmpDesignation = dvm.CreateDataView(ds.Tables(desTableName1))
            bNewData = False
            bEditData = False
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
#End Region

4. Databinding

#Region "DataBinding()"
    Private Sub DataBinding()
        Try
            Dim desTableName2 As String
            desTableName2 = "dbo.hrEmpDesignation"
            edsID.DataBindings.Add(New Binding("Text", ds.Tables(desTableName2), "DesID"))
            edsDesigName.DataBindings.Add(New Binding("Text", ds.Tables(desTableName2), "DesName"))
            edsDesShortName.DataBindings.Add(New Binding("Text", ds.Tables(desTableName2), "DesShortName"))
            edsDescription.DataBindings.Add(New Binding("Text", ds.Tables(desTableName2), "DesDescription"))
            edsDefaultCheckbox.DataBindings.Add(New Binding("Checked", ds.Tables(desTableName2), "DesIsDefault"))
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
#End Region

5. Functions for Validate Duplicate value.

Function DesignationNameExists(ByVal sDesName As String) As Boolean
        Try
            Dim oDr1 As OleDbDataReader
            Dim mySelectQuery1 As String = "SELECT DesName FROM dbo.hrEmpDesignation WHERE DesName='" & sDesName & "'"
            Dim myCommand1 As New OleDbCommand(mySelectQuery1, conn)
            conn.Open()
            oDr1 = myCommand1.ExecuteReader()
            DesignationNameExists = oDr1.HasRows
            oDr1.Close()
            conn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Function DesignationShortNameExists(ByVal sDesShortName As String) As Boolean
        Try
            Dim oDr3 As OleDbDataReader
            Dim mySelectQuery3 As String = "SELECT DesShortName FROM dbo.hrEmpDesignation WHERE DesShortName='" & sDesShortName & "'"
            Dim myCommand3 As New OleDbCommand(mySelectQuery3, conn)
            conn.Open()
            oDr3 = myCommand3.ExecuteReader()
            DesignationShortNameExists = oDr3.HasRows
            oDr3.Close()
            conn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Function desCheckBoxCheck(ByVal sCheckboxCheck As Boolean) As Boolean
        Try
            Dim oDr2 As OleDbDataReader
            Dim mySelectQuery2 As String = "SELECT DesISDefault FROM dbo.hrEmpDesignation WHERE DesIsDefault='" & sCheckboxCheck & "'"
            Dim myCommand2 As New OleDbCommand(mySelectQuery2, conn)
            conn.Open()
            oDr2 = myCommand2.ExecuteReader()
            desCheckBoxCheck = oDr2.HasRows
            oDr2.Close()
            conn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Function

6. Save command button code

Private Sub btnedsSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnedsSave.Click
        If ValidateData() Then
            Dim cmdCom As OleDbCommand
            Try
                cmdCom = New OleDbCommand
                conn.Open()
                trns = conn.BeginTransaction
                With cmdCom
                    .CommandText = GetSQLStr()
                    .Connection = conn
                    .Transaction = trns
                    .ExecuteNonQuery()
                End With
                trns.Commit()
                conn.Close()
                GetData()
                EnableControlsFormLoadMode(True)
                bNewData = False
                bEditData = False
                Count()
            Catch ex As Exception
                trns.Rollback()
                MessageBox.Show("Critical Error!" & ex.Message, "Critical Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub

7. Textbox validation code

Private Sub edsDesigName_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles edsDesigName.Validated
        Try
            If DesignationNameExists(edsDesigName.Text.Trim) Then
                MessageBox.Show("Designation Name [" & edsDesigName.Text.Trim & "] already Exists. Please Check.", "Designation Name", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                edsDesigName.Focus()
                btnedsSave.Enabled = False
                edsDesigName.Text = ""
            Else
                EnableControlsInputAccept(True)
                edsDesShortName.Focus()
                btnedsSave.Enabled = True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub edsDesShortName_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles edsDesShortName.Validated
        Try
            If DesignationShortNameExists(edsDesShortName.Text.Trim) Then
                MessageBox.Show("Designation Short Name [" & edsDesShortName.Text.Trim & "] already Exists. Please Check.", "Designation Short Name", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                edsDesShortName.Focus()
                btnedsSave.Enabled = False
                edsDesShortName.Text = ""
            Else
                EnableControlsInputAccept(True)
                edsDescription.Focus()
                btnedsSave.Enabled = True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub edsDefaultCheckbox_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles edsDefaultCheckbox.Validated
        Try
            If desCheckBoxCheck(edsDefaultCheckbox.CheckState) = True Then
                MessageBox.Show("Cannot assign multiple default value.", "Is Default", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                edsDescription.Focus()
                btnedsSave.Enabled = False
                edsDefaultCheckbox.CheckState = CheckState.Unchecked
            Else
                EnableControlsInputAccept(True)
                btnedsSave.Focus()
                btnedsSave.Enabled = True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

8. DataGrid View code

#Region "GridView"
    Private Sub grvDesignationList_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grvDesignationList.CellClick
        Try
            Dim i As Integer
            i = grvDesignationList.CurrentRow.Index
            edsID.Text = grvDesignationList.Item(0, i).Value
            edsDesigName.Text = grvDesignationList.Item(1, i).Value
            edsDesShortName.Text = grvDesignationList.Item(2, i).Value
            edsDescription.Text = grvDesignationList.Item(3, i).Value
            If (CBool(grvDesignationList.Item(4, i).Value)) Then
                edsDefaultCheckbox.CheckState = CheckState.Checked
            Else
                edsDefaultCheckbox.CheckState = CheckState.Unchecked
            End If
            bEditData = True
            EnableControlsEditMode(True)
            edsDesigName.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Sub grvDesignationList_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles grvDesignationList.Click
        Me.BindingContext(grvDesignationList.DataSource, grvDesignationList.DataMember).Position = grvDesignationList.CurrentRow.Index
        Count()
    End Sub
#End Region

Problem:

When i save the record for first time the above textbox validation code works fine for me. But when it comes for updating the existing records its giving me a problem, for example, say i already have a record Designation Name : Managing Director and i am updating the other records of the same and when i save it says the record Managing Director already exists. Even if i dont make any changes and click on save button its gives me a same message.

So can anyone help out where did i go wrong in codding? Can anyone help me correcting my above code please. I am struck here....

Thanks,

Tashi Duks

vb.net

1 Contributor
2 Replies
131 Views
1 Year Discussion Span
Latest Post 12 Years Ago Latest Post by tashiDuks

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tashiDuks 0 Light Poster · Answer 1 · 2010-10-27T13:28:47+00:00

Its NOBODY there to fix my problem? Please its really urgent matter... PLEASE SOMEBODY COME .....

tashiDuks 0 Light Poster · Answer 2 · 2011-12-02T16:08:30+00:00

Hi Members,

Is any body who can look into my codding error. Since there is no one who replied, i am reactivating my problem.

PLEASE EXPERTS HELP ME OUT...:-/

regards,
TashiDuks