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