Dear All,
How Can i prevent duplicate record entry in vb.net. Please help
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.
i want to populate a message like "record already exists
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
I want to check it on keypress event
Just put the code in the keypress event.
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
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
Thanks it is working.
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.