如果数据库结构设计时完全考虑到这点 数据表建立连个索引属性定为无重复,不用这个当然可以。但是随着需求的更改来更改数据库的结构带来维护的风险可能比较大,在程序中重构也可以解决这个问题。
Public Function IsAlone(ByVal sTalbe As String, _
ByVal IDFiled As String, _
ByVal IDVal As Long, _
ByVal sFiled As String, _
ByVal sFiledVal As String) As Boolean
'' ==========================================================
' 开发人员:段利庆
' 编写时间:09-08-18
' 函数名称:sTalbe 表名称
' 参数说明:IDFiled 数据表索引ID字段的名称 例: 编号
' IDVal 数据表索引ID字段的数值 例: 120
' sFiled 要测试不重复的字段的名称
' sFiledVal 要测试不重复字段的字符串值
'
' 功能说明:在更新操作前,检查除本条记录外,是否和其他的此字段值有重复
' 数据库设计时 采用字段属性为【索引无重复】 目的相同
' 无重复 为【True】
'' ==========================================================
On Error GoTo PROC_ERR
Dim strMessage As String
Dim strSQL As String
strSQL = "SELECT " & IDFiled & " , " & _
sFiled & " " & _
"FROM " & sTalbe & " " & _
"WHERE " & IDFiled & " <> " & IDVal & " AND " & _
sFiled & " = " & QueryStr(sFiledVal) & " "
If Me.GetRecordCount(strSQL) = 0 Then
Return True
End If
PROC_EXIT:
Exit Function
PROC_ERR:
Call ShowError("DBbase", "IsAlone", Err.Number, Err.Description)
GoTo PROC_EXIT
End Function
应用举例:
Dim ID As Long
ID = Val(LV.SelectedItems(0).SubItems(0).Text)
'*===================================================
'*检查名称字段值是否有重复的
'*===================================================
Dim sTable As String = "操作员"
Dim IDFiled As String = "编号"
Dim IDVal As String = ID
Dim sFiled As String = "名称"
Dim FiledVal As String = TxtCaption.Text
If Not objData.IsAlone(sTable, IDFiled, ID, sFiled, FiledVal) Then
strMessage = sFiled & ":" & FiledVal & " 已经存在," & vbCrLf & _
"请更改后重试。"
MessageBox.Show(strMessage, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
GoTo PROC_EXIT
End If