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?

Try
            '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
            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!", "MP Register", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                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

Recommended Answers

All 2 Replies

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.

can you just pl modify the code for me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.