[SOLVED] vb.net MS Access insert record with auto number primary key column into table with SQL query

vb.net MS Access insert record with auto number primary key column into table with SQL query

I want to insert a new row into a table with a auto number column. My code is below, I can't find out how to insert auto number column simultaneously with other columns:

pth = My.Application.Info.DirectoryPath

Dim SQL = "INSERT INTO approved (id, word, approveds) VALUES (@idd, @word, @approval)"

Using Con As New OleDbConnection("Provider=Microsoft.ace.oledb.12.0; Data Source=" & pth & "\database.mdb; User Id=; Password=;")
    Dim Cmd As New OleDbCommand(SQL, Con)

    Cmd.Parameters.Add("@idd", OleDb.OleDbType.VarChar).Value = @@identity
    Cmd.Parameters.Add("@word", OleDb.OleDbType.VarChar).Value = RichTextBox1.SelectedText
    Cmd.Parameters.Add("@approval", OleDb.OleDbType.VarChar).Value = "YES"

    Con.Open()
    Cmd.ExecuteNonQuery()
End Using

The keyword @@identity does not work; what is the proper method?

Solution

If you would have set SQL column to identity auto increment you don't need to pass id parameter while inserting it will automatically work.

Remove below code:

Cmd.Parameters.Add("@idd", OleDb.OleDbType.VarChar).Value = @@identity

Addendum:

Of course, also use:

Dim SQL = "insert into approved (word, approveds) VALUES (@word, @approval)"