Hi, I have an vb.net application linked to Oledb database.
here is my code in Button_Click event to prevent duplicate entry with same ID, ID is not the primary key in database.
Although it shows "Record Already" Exists", but I am not able to save data with new ID, to the database.
So what to do?

            'Set up connection string
            Dim cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Pathology.accdb"
            Dim sqlQRY As String = "SELECT COUNT(*) AS numRows FROM MPRecordIDSP WHERE ID = '" & IDtxt.Text & "'"
            Dim queryResult As Integer

            'Create connection
            Dim conn As OleDbConnection = New OleDbConnection(cnString)
            ' Open connection
            ' Query the database
            Dim com As New OleDbCommand(sqlQRY, conn)
            queryResult = com.ExecuteScalar()

            ' Close connection

            If queryResult > 0 Then

                MessageBox.Show("Already Exists!", "MP Register", MessageBoxButtons.OK, MessageBoxIcon.Information)
                com = New OleDbCommand("insert into MPRecordIDSP (Dt,ID,PatientName,Ward,RegNo,Age,Sex,Result,ReportedBy) values ('" & Dttxt.Text & "','" & IDtxt.Text & "','" & PatientNametxt.Text & "','" & Wardtxt.Text & "','" & RegNotxt.Text & "','" & Agetxt.Text & "','" & Sextxt.Text & "','" & Resulttxt.Text & "','" & ReportedBytxt.Text & "') ", conn)
            End If

        Catch ex As OleDbException
            MessageBox.Show("Customer Not found" + ex.Message)
        End Try

In the Else part of the if..end if you are only setting the com, you are not executing it. Try executing the command you've just set.

This article has been dead for over six months. Start a new discussion instead.