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

Question

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?

You don't specify the value for that column. That's the whole point of an AutoNumber: it's automatic. — jmcilhinney, Jan 02 '19 at 09:58

score 0 · Accepted Answer · edited Jan 02 '19 at 10:13

0

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)"

edited Jan 02 '19 at 10:13

Gustav

53,498
7
29
55

answered Jan 02 '19 at 09:30

Kishore Kolla

109
5

if i remove such code line it shows this error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: No value given for one or more required parameters...... remember i use .mdb database file – fahad Jan 02 '19 at 09:48
yes it worked i deleted : Remove below code: Cmd.Parameters.Add("@idd", OleDb.OleDbType.VarChar).Value = @@identity and and @idd from dim sql line – fahad Jan 02 '19 at 10:15

score 0 · Answer 2 · answered Jan 02 '19 at 10:16

this worked :

 pth = My.Application.Info.DirectoryPath



            Dim SQL = "insert into approved (word, approveds) VALUES (@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("@word", OleDb.OleDbType.VarChar).Value = RichTextBox1.SelectedText
                Cmd.Parameters.Add("@approval", OleDb.OleDbType.VarChar).Value = "YES"
                Con.Open()
                Cmd.ExecuteNonQuery()
            End Using

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

2 Answers2