How to avoid inserting duplicate Access dataBase in tables using VB.net

Question

1.00/5 (1 vote)

See more:

hi all
can some pls help for following issue
i want prevent a user for see error massage and customize other massage for error Duplicate Values in the index,primary key or relationship ..and also i want code to check duplicated data
Please Noted I Used Vb.net 2008 with AccessData Basee

VB

Imports System.Data
Imports System.Data.OleDb

Public Class SpecialColor

    Public CON As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\db1.mdb")
    Public DS As New DataSet
    Public SQLstr As String = "SELECT * FROM SpecialColor WHERE OrderNo LIKE '%" & PrintDetails.TextBox1.Text & "%'"

    Sub DataBaseUpdate()
        Try
            CON.Open()
            Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, CON)
            DataAdapter1.Fill(DS, "SpecialColor")
            CON.Close()
            AddDataContent()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.MsgBoxRight + MsgBoxStyle.Critical, " خطأ في الإتصال")
        End Try
    End Sub

    Public Sub updatTheDataBase()
        Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, CON)
        DS.Clear()
        DataAdapter1.Fill(DS, "SpecialColor")
    End Sub

    Sub AddDataContent()

        On Error Resume Next
        TextBox3.DataBindings.Add("Text", DS, "SpecialColor.PrintOn")
        TextBox5.DataBindings.Add("Text", DS, "SpecialColor.OrderNo")
        TextBox2.DataBindings.Add("Text", DS, "SpecialColor.SpecialColor")
        TextBox4.DataBindings.Add("Text", DS, "SpecialColor.Color")
        TextBox1.DataBindings.Add("Text", DS, "SpecialColor.Ref")
    End Sub

    Sub InsertData()
        Try

            Dim SavInto1 As New OleDb.OleDbCommand
            SavInto1.Connection = CON
            SavInto1.CommandType = CommandType.Text
            SavInto1.CommandText = "Update SpecialColor set Color=@P1,Ref=@P2 where OrderNo=@P3 and PrintOn=@P4 and SpecialColor =@P5"

            With SavInto1.Parameters
                .AddWithValue("@P1", TextBox4.Text)
                .AddWithValue("@P2", TextBox1.Text)
                .AddWithValue("@P3", TextBox5.Text)
                .AddWithValue("@P4", TextBox3.Text)
                .AddWithValue("@P5", TextBox2.Text)
            End With
            CON.Open()
            SavInto1.ExecuteNonQuery()
            CON.Close()
            MsgBox("تمت عملية الاضافة والحفظ في قاعدة البيانات بنجاح", MsgBoxStyle.Information, "تمت العملية بنجاح")
        Catch ex As Exception When SQLstr.Count > 0
            MsgBox("This Record allreay updated", MsgBoxStyle.Critical, "Error")
            CON.Close()
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        InsertData()
        If Me.BindingContext(DS, "SpecialColor").Position = Me.BindingContext(DS, "SpecialColor").Count - 1 Then
            MyBase.Close()
        Else
            Me.BindingContext(DS, "SpecialColor").Position += 1
        End If
    End Sub

    Private Sub SpecialColor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Text = "Print Details Of Order Number " & PrintDetails.TextBox1.Text
        TextBox3.ReadOnly = True
        TextBox2.ReadOnly = True
        TextBox5.ReadOnly = True
        DataBaseUpdate()
        If Me.BindingContext(DS, "SpecialColor").Count >= 2 Then
            Button3.Enabled = True
            Button4.Enabled = True
        Else
            Button3.Enabled = False
            Button4.Enabled = False
        End If



    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.BindingContext(DS, "SpecialColor").Position -= 1
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        InsertData()
        MyBase.Close()
    End Sub

End Class

Posted 3-Oct-13 23:41pm

Ahmed Shafea

Updated 4-Oct-13 3:43am

v2

Add a Solution

Comments

Thanks7872 4-Oct-13 5:54am

And where is the question? What have you tried so far?

Ahmed Shafea 4-Oct-13 7:56am

There 3 question not one
1- How to avoid inserting duplicate Access dataBase using VB.net ?
2-please advise which exception responsible for appear error Duplicate Values in the index,primary key or relationship for i can customize special massage to user
3- I want code to check the duplicated datas before insert or updated ?
I haven't enough experince for this issue for advised you my tries

ZurdoDev 4-Oct-13 7:27am

So, check for it. Where's the issue?

Ahmed Shafea 4-Oct-13 7:57am

I meant answer my question pls as below
1- How to avoid inserting duplicate Access dataBase using VB.net ?
2-please advise which exception responsible for appear error Duplicate Values in the index,primary key or relationship for i can customize special massage to user
3- I want code to check the duplicated datas before insert or updated ?
I haven't enough experince for this issue for advised you my tries

ZurdoDev 4-Oct-13 7:58am

1. Check for it. Use IF EXISTS() in SQL
2. This is so easy to test for. Just catch a SqlException and do it. You'll see what the exception is.
3. IF EXISTS() UPDATE otherwise INSERT.

Ahmed Shafea 4-Oct-13 8:22am

sorry i just a Beginner in vb.net ...can you pls advise me clear how i can use IF Exists ()
i use Access database not sql Database so sqlexception not viald ..

Thanks7872 4-Oct-13 8:43am

RyanDev wrote
Check for it. Use IF EXISTS() in SQL?

Ahmed Shafea 4-Oct-13 8:45am

so that's meant no solution for Access Data base..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Answer 1 · 2013-10-04T04:06:00

Most SQL Queries will work with an Access Database, as it happens the Access front end uses mainly SQL queries to processes its data, so yes should be able to use IF EXISTS when working with the Access database. (General info link: Access and SQL[^])

In your VB code, you should implement Try Catch Finally blocks so that you can do correct error handling.

The general format is something like:

VB

Try
    ..Code...    
Catch exception As type 
 ....Code to work with expected exception...
Catch exception as system.exception
  ....code to handle unexpected exceptions....
Finally
   ... clean up code....
End Try

Further details on this can be found from Google and from MSDN[^]

the ADO.Net objects (in your code example the OLEDB.... objects) well throw specific error types which you can catch. You can then look for keywords/phrases in the exception message(s) and present user friendly messages back to the user.

In addition, if you were to implement the IF EXISTS method for inserting new records, you could return -1 from it if it already exists or return the assigned ID after the insert if it didn't exists. That way you don't have to have a catch to look for a duplicate, instead just process the result of the query.

There is a lot of available resources on the internet that give examples of all of these concepts, hence not going into detail but highlighting possible approaches which should help you find examples you can use in your code.

Ahmed Shafea · Answer 2 · 2013-10-05T01:34:00

I was work for solved my problem here and i get this code below but its not save any data when there duplicate data can you pls look on it and try to help

VB

Sub InsertData()
        Try
            Dim SQLstr As String = "SELECT * FROM SpecialColor WHERE OrderNo LIKE '%" & PrintDetails.TextBox1.Text & "%'"
            Dim selectdb As String = "SELECT Ref FROM SpecialColor WHERE OrderNo = '" & TextBox5.Text & "' and PrintOn = '" & TextBox3.Text & "' and SpecialColor = '" & TextBox2.Text & "' Color = '" & TextBox4.Text & "' '"
            Dim DataAdapter1 As New OleDbDataAdapter(selectdb, CON)
            TextBox6.DataBindings.Clear()
            TextBox6.DataBindings.Add("Text", DS, "SpecialColor.Ref")
            If TextBox6.Text <> TextBox1.Text Then
                Dim SavInto1 As New OleDb.OleDbCommand
                SavInto1.Connection = CON
                SavInto1.CommandType = CommandType.Text
                SavInto1.CommandText = "Update SpecialColor set Color=@P1,Ref=@P2 where OrderNo=@P3 and PrintOn=@P4 and SpecialColor=@P5 "
                With SavInto1.Parameters
                    .AddWithValue("@P1", TextBox4.Text)
                    .AddWithValue("@P2", TextBox1.Text)
                    .AddWithValue("@P3", TextBox5.Text)
                    .AddWithValue("@P4", TextBox3.Text)
                    .AddWithValue("@P5", TextBox2.Text)
                End With
                CON.Open()
                SavInto1.ExecuteNonQuery()
                CON.Close()
                MsgBox("تمت عملية الاضافة والحفظ في قاعدة البيانات بنجاح", MsgBoxStyle.Information, "تمت العملية بنجاح")
            ElseIf TextBox6.Text = TextBox1.Text Then
                MsgBox("This order allreay updated", MsgBoxStyle.Critical, "Error")
                TextBox1.Clear()
                TextBox4.Clear()
                CON.Close()
            End If
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

How to avoid inserting duplicate Access dataBase in tables using VB.net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0