如何使用我的代码防止重复记录 [英] How Do I Prevent Duplicate Record With My Code

问题描述

Imports MySql.Data.MySqlClient
Public Class UserAdmin
Public mycon As New MySqlConnection
Public myadap As New MySqlDataAdapter
Public mycmd As New MySqlCommand
Public myds As New DataSet
Public rec As Integer
Public sb As Boolean
Public idnum As String
Public trec As Integer

Sub connect()
        mycon = New MySqlConnection
        mycon.ConnectionString = "server=localhost;user id=root;password=;database=dbase"
        mycon.Open()
        mycmd.Connection = mycon
        mycmd.CommandText = "select*from tbluseradmin"
        myds = New DataSet
        myadap.SelectCommand = mycmd
        myadap.Fill(myds, "tbl")
        trec = myds.Tables("tbl").Rows.Count - 1
        rec = 0
End Sub

Private Sub UserAdmin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Call connect()
       Call display()
end sub

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
        If txtid.Text = "" Or txtname.Text = "" Or txtmn.Text = "" Or txtln.Text = "" Or cbogender.Text = "" Or cbosub.Text = "" Or cbopos.Text = "" Then
            MsgBox("Complete All Fields Needed", MsgBoxStyle.Information, "")
            txtid.Focus()
        ElseIf IsNumeric(txtid.Text) = False Then
            MsgBox("ID number should be numbers only")
        ElseIf sb = True Then
            mycmd.Connection = mycon
            mycmd.CommandText = "insert into `tbluseradmin`(`ID`,`Name`,`MiddleName`,`LastName`,`Gender`,`Subject`,`Position`)values('" & txtid.Text & "','" & txtname.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & cbogender.Text & "','" & cbosub.Text & "','" & cbopos.Text & "')"
            mycmd.ExecuteNonQuery()
            mycon.Close()
            My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
            MsgBox("Record Save", MsgBoxStyle.Information, "")
            Call connect()
            Call display()
            Call textboxlocked()
            Call buttonunlock()
            Call cmdlock()
            Call cmd2unlock()
        Else
            mycmd.Connection = mycon
            mycmd.CommandText = "update `tbluseradmin` set`ID`='" & txtid.Text & "',`Name`='" & txtname.Text & "',`MiddleName`='" & txtmn.Text & "',`LastName`='" & txtln.Text & "',`Gender`='" & cbogender.Text & "'where`ID`='" & idnum & "'"
            mycmd.ExecuteNonQuery()
            mycon.Close()
            My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
            MsgBox("Record Update", MsgBoxStyle.Information, "")
            Call connect()
            Call display()
            Call textboxlocked()
            Call buttonunlock()
            Call cmdlock()
            Call cmd2unlock()
        End If
End Sub

推荐答案

下面的解决方案假设ID列不是自动递增的值。



1.用户输入'ID'的值从前端开始。

2.检查数据集中是否存在该值。

3.如果数据集中不存在，则将值插入数据库。

4.如果数据集中存在该值，则显示一条消息。



下面我将向您展示如何更改插入部分

The solution below is with the assumption that the "ID" column is not auto incremented value.

1. The user enters the value of the 'ID' from the front end.
2. Check if that value exists in your dataset.
3. If it does not exists in your dataset then insert the value in the database.
4. If that value exists in the dataset then display a message.

Below I will just show you how to change the insert part

Dim result() As DataRow = myds.Tables(0).select("ID = '"+ txtid.Text +"'")
If (result.Length == 0) Then
	mycmd.Connection = mycon
    mycmd.CommandText = "insert into `tbluseradmin`(`ID`,`Name`,`MiddleName`,`LastName`,`Gender`,`Subject`,`Position`)values('" & txtid.Text & "','" & txtname.Text & "','" & txtmn.Text & "','" & txtln.Text & "','" & cbogender.Text & "','" & cbosub.Text & "','" & cbopos.Text & "')"
    mycmd.ExecuteNonQuery()
    mycon.Close()
    My.Computer.FileSystem.CopyFile(ofd.FileName, Application.StartupPath & "\images\" & txtid.Text & ".jpg")
    MsgBox("Record Save", MsgBoxStyle.Information, "")
Else
	MsgBox("ID already exists")
End

希望上面的代码有帮助。

Hope the above code helps.

这篇关于如何使用我的代码防止重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！