如何避免使用VB.net在表中插入重复数据 [英] How to avoid inserting duplicate data in tables using VB.net

问题描述

我是一个学习者..我正在创建一个数据库，其中的字段是consumr_id，consumr_nm，consumr_add& consumr_cn

每当我保存数据时，我想检查consumr_cn(消费者联系号码)是否重复.如果重复，则msgbox将显示一条消息或将检测到错误.....我的代码是:-

I am a learner.. i am creating a database where the fields are consumr_id,consumr_nm,consumr_add & consumr_cn

i want to check whether the consumr_cn(consumer contact no)is duplicate or not whenever i save the data.. if its a duplicate one, msgbox will show a message or error will be detected..... my code is:-

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
            cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DIGISMART\details.mdb")
            cn.Open()
            str = "Insert into consumer_details values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"
            cmd = New OleDbCommand(str, cn)
            icount = cmd.ExecuteNonQuery
            MessageBox.Show(icount & " items added")
            CONN()
            FILL()
            cn.Close()
End Sub

什么是代码来检查重复数据与否以及应将其放置在我的代码中的位置???请帮助..

what will be the code to check duplicate data or not and where it should be placed in my code??? pls help..

推荐答案

尝试在consumr_cn列上添加唯一约束.

http://msdn.microsoft.com/en-us/library/ms177420 (v = sql.105).aspx [

Try by adding a unique constraint on the column consumr_cn.

http://msdn.microsoft.com/en-us/library/ms177420(v=sql.105).aspx[^]


Then try to catch this catch exception in your C# code behind.

Try
Catch ex As SqlException
			
	If ex.Message.Contains("UniqueConstraint") Then
              ' do something
	End If
End Try

这可能不是最优雅的解决方案，但它可以工作.


干杯




SqlException具有SqlError对象的集合:错误. SqlError具有错误编号的属性，您可以将其与已知的违反约束错误编号(例如2627)进行比较.

虽然SqlException本身公开了Number属性，但如果在单个批处理中发生多个错误，则是不准确的，因此最好检查Errors集合.

It is maybe not the most elegant solution but it works.


Cheers




SqlException has a collection of SqlError objects: Errors. The SqlError have properties for error Number and you can compare this with the known constraint violation error numbers (eg. 2627).

While is true that SqlException itself exposes a Number property, it is not accurate if multiple errors happen in a single batch and hence is better to inspect the Errors collection.

这篇关于如何避免使用VB.net在表中插入重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！