Cannot insert more than one Record VB 2008 professional

[wilsonchama]

Hi guys,
My application Cannot insert Record into Table more than once.

There are 3 textboxes on the form in which the user enters data to be inserted in the table. And a command button holding the code to perform the action.
What is happening is, when data is entered in the textboxes and the command button is clicked, the record in the table for the first time is created, but when I try to do it the second time, the application brings the following error message
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship". Below is the code I am using.

Public Class frmCreate
    Dim con As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand

    Private Sub btnSave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        con = New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "/sale.accdb"

        con.Open()
        cmd.Connection = con

        cmd.CommandText = "INSERT INTO tblProducts([pID], [pName], [pPrice])VALUES(@pID, @pName, @pPrice)"

        cmd.Parameters.AddWithValue("@pID", txtId.Text)
        cmd.Parameters.AddWithValue("@pName", txtName.Text)
        cmd.Parameters.AddWithValue("@pPrice", txtPrice.Text)

        cmd.ExecuteNonQuery()

        txtId.Text = ""
        txtName.Text = ""
        txtPrice.Text = ""
        MsgBox("Record Saved")

        txtId.Focus()
        con.Close()
    End Sub
End Class

I do not know what is causing this error because even when the primary key is different it is behaving the same. It is only working after the application is restarted again.

Someone to help me identitfy the cause of the problem.
Thank you.

[Santanu.Das]

You declare the Command object at Form Level, so imidiate after calling ExecuteNonQuery() method Clear the parameters and dispose the command object. Or you can declare the Command object at Procedure level. From my opinion the codes should be

Public Class frmCreate
    Dim con As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand

    Private Sub btnSave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        cmd = New OleDb.OleDbCommand

        con = New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "/sale.accdb"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO tblProducts([pID], [pName], [pPrice])VALUES(?, ?, ?)"
        cmd.Parameters.AddWithValue("@pID", txtId.Text)
        cmd.Parameters.AddWithValue("@pName", txtName.Text)
        cmd.Parameters.AddWithValue("@pPrice", txtPrice.Text)
        cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        cmd.Dispose()
        con.Close()


        txtId.Text = ""
        txtName.Text = ""
        txtPrice.Text = ""

        MsgBox("Record Saved")

        txtId.Focus()

    End Sub
End Class

Hope it can help you.

[wilsonchama]

I got it Shark,
I can't believe I could not pick that up.