how can I get the id back from an access insert query?

Question

I have an access data base where i want to add a row in a table. but I want to get the id(colum name -> id) back.

this is the code i use to register a new group:

int id;

internal static void agregarGrupo(int grado, char grupo, String escuela, int maestro)
    {
        try
        {
            comand.Connection = conection;
            comand.CommandText = 
                "INSERT INTO Grupos " +
                "(grado, grupo, escuela, maestro) " +
                "VALUES(" + grado + ", '" + grupo + "' , '" + escuela + "', " + maestro + ")";

            conection.Open();
            id = comand.ExecuteScalar();
        }
        finally
        {
            conection.Close();
        }
    }

what am I doing wrong? how can I get the id of the new row without making a new query asking for the bigest id in the table?

https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row — zaitsman, Nov 23 '17 at 03:27
forgot to mention that `comand.ExecuteScalar()` does not return any value — Javier Fuentes, Nov 23 '17 at 03:27
`INSERT INTO Grupos ( ... ) OUTPUT INSERTED.id VALUES( ... )` gives me a sintaxis error. @zaitsman — Javier Fuentes, Nov 23 '17 at 03:34

score 0 · Accepted Answer · answered Nov 23 '17 at 03:44

You have to make another query to get the last identity value inserted because Access does not support batch commands. You need to reuse the connection and issue another command like this:

string query = "SELECT @@IDENTITY";
var cmd = new OleDbCommand(query, (OleDbConnection)command.Connection);
int newId = (int)cmd.ExecuteScalar();

Or you can simply do this after this line of code you have:

id = comand.ExecuteScalar(); // this is your code
command.CommandText = "SELECT @@IDENTITY";
int newId = (int)command.ExecuteScalar();

tnks, did not know that "Access does not support batch commands". that help a lot — Javier Fuentes, Nov 23 '17 at 05:32

how can I get the id back from an access insert query?

1 Answers1