Prevent Duplicate Record Entry

[Naveed_786]

Dear All,

How Can i prevent duplicate record entry in vb.net. Please help

[Oxiegen]

It depends.

If you're talking about storing a new record into a database, then that could be accomplished by a simple SQL query.

INSERT INTO table (field1, field2, field3) VALUES (value1, value2, value3) WHERE NOT EXISTS (SELECT field1, field2, field3 FROM table WHERE name_field = 'name')

If you're talking about a datagrid, all you have to do is loop through the rows and compare the cells with what you want to store.

[Naveed_786]

i want to populate a message like "record already exists

[Oxiegen]

You can do that in two ways.

1) First perform a normal "SELECT COUNT(*) AS numrows" query on the database, where the WHERE clause contains those fields that makes the record unique.
And if the query returns a "numrows > 0" then the record exists.

Dim con As New SqlConnection("connectionstring")
Dim com As SqlCommand = Nothing
Dim queryResult As Integer

con.Open()
com = New SqlCommand("SELECT COUNT(*) AS numRows FROM table WHERE ...", con)
queryResult = com.ExecuteScalar()
con.Close()

If queryResult = 0 Then
   con.Open()
   com = New SqlCommand("INSERT INTO table (field1, field2, field3) VALUES (value1, value2, value3)", con)
   com.ExecuteNonQuery()
   con.Close()
Else
   MessageBox.Show("Record already exists", "Existing record", MessageBoxButtons.OK, MessageBoxIcon.Error)   
End If

2) Using the INSERT query I provided the SqlCommand.ExecuteNonQuery method will return the number of affected rows.
If the INSERT failed, then the return is 0, and if the INSERT succeded then the return is 1.

Dim con As New SqlConnection("connectionstring")
Dim com As SqlCommand = Nothing
Dim queryResult As Integer

con.Open
com = New SqlCommand("INSERT QUERY", con)
queryResult = com.ExecuteNonQuery
con.Close()

If queryResult = 0 Then
   MessageBox.Show("Record already exists", "Existing record", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

[Naveed_786]

I want to check it on keypress event

[Oxiegen]

Just put the code in the keypress event.

[Naveed_786]

I am using this code please can you modify it to check duplicate record.

Try
            'Set up connection string
            Dim cnString As String

            cnString = ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\Payment.mdb;")

            'Dim sqlQRY As String = "SELECT * FROM CustomerInformation WHERE CustomerName = '" & TextBox5.Text & "'"

            'Create connection
            Dim conn As OleDbConnection = New OleDbConnection(cnString)
            'Try
            ' Open connection
            conn.Open()
            Dim sqlQRY As String = "SELECT * FROM CustomerInformation WHERE CustomerName = '" & TextBox1.Text & "'"

            'create data adapter
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)

            'create dataset
            Dim ds As DataSet = New DataSet

            'fill dataset
            da.Fill(ds, "CustomerInformation")

            'get data table
            Dim dt As DataTable = ds.Tables("CustomerInformation")
            'display data
            'Dim row As DataRow

            'For Each row In dt.Rows
            'TextBox1.Text = row("CustomerName")
            'Next row
            If ds.Tables("CustomerInformation").Rows.Count = 0 Then
                MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
            If Asc(e.KeyChar) = 13 Then
                TextBox2.Focus()
            End If
            ' Close connection
            conn.Close()
        Catch ex As OleDbException
            MessageBox.Show("Customer Not found" + ex.Message)
        End Try

[Oxiegen]

Here is the modified code.

Try
	'Set up connection string
	Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\Payment.mdb;"

	Dim sqlQRY As String = "SELECT COUNT(*) AS numRows FROM CustomerInformation WHERE CustomerName = '" & TextBox1.Text & "'"
	Dim queryResult As Integer

	'Create connection
	Dim conn As OleDbConnection = New OleDbConnection(cnString)

	' Open connection
	conn.Open()

	' Query the database
	Dim com As New OleDbCommand(sqlQRY, conn)
	queryResult = com.ExecuteScalar()
	
	' Close connection
	conn.Close()
	
	If queryResult > 0 Then
		MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
	End If
Catch ex As OleDbException
	MessageBox.Show("Customer Not found" + ex.Message)
End Try

[Naveed_786]

Thanks it is working.

[spider2vb]

If class1.sqlcon.state = ConnectionState.Open Then
    class1.sqlcon.close()
End If
class1.sqlcon.open()

Dim cmd As New sqlcommand
Dim Qresault As Integer

cmd = new sqlcommand (" SELECT COUNT (*) FROM suppcust WHERE ID='" & TxtID.Text & "' , class1.sqlcon)
Qresault = cmd.executescalar

If Qresault = 0 Then
    Dim SaveOk As Boolean = winclass.SQLExicute(" Insert Into suppcust (supcust_Code, supcust_Name) VALUES ('" & Txtsupcust_code.text & "' , '" & Txtsupcust_Name.text & "')")
    If SaveOk = True Then
        MsgBox("Save OK")
        TxtID.Text = ""
        Txtsupcust_code.Text = ""
        Txtsupcust_Name.Text = ""
        FillDataGrid()
    End If

    MsgBox("Record already exists")
Else
    If IsUpdate = True Then

        Dim cmd1 As New sqlcommand(" SELECT supcust_Code FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Code = '" & Txtsupcust_code.text & "' ", class1.sqlcon)
        Dim dr As SqlDataReader = cmd1.ExcuteReader()
        If dr.HasRows Then
            MsgBox("The Code already exists")
        End If
        Txtsupcust_code.Focus()

        Dim cmd2 As New sqlcommand(" SELECT supcust_Name FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Name = '" & Txtsupcust_Name.text & "' ", class1.sqlcon)
        Dim dr1 As SqlDataReader = cmd2.ExcuteReader()
        If dr1.HasRows Then
            MsgBox("The Name already exists")
        End If
        Txtsupcust_Name.Focus()

        Dim UpdateOk As Boolean = winclass.SQLExicute(" UPDATE suppcust SET supcust_Code = '" & Txtsupcust_code.text & "' , supcust_Name = '" & Txtsupcust_Name.text & "' WHERE ID=" & TxtID.Text & "")
        If UpdateOk = True Then
            MsgBox("Updated OK")
            TxtID.Text = ""
            Txtsupcust_code.Text = ""
            Txtsupcust_Name.Text = ""
            FillDataGrid()
        End If
    End If