C# odbc - last inserted id output parameter problem - help needed please

i am trying to retrieve the id of the last inserted row in my database using c# and odbc. i am adding an output parameter to my query but when it runs the following error is returned:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘; SELECT NULL = @@IDENTITY’ at line 1


            //setup parameters for insert query
            OdbcParameter paramJobTitle = new OdbcParameter("@jobTitle", OdbcType.VarChar, 255);
            paramJobTitle.Value = jobTitle;
            OdbcParameter paramRole = new OdbcParameter("@role", OdbcType.VarChar, 255);
            paramRole.Value = role;
            OdbcParameter paramDescription = new OdbcParameter("@description", OdbcType.Text);
            paramDescription.Value = description;
            OdbcParameter outputVacancyID = new OdbcParameter("@vacancyID", OdbcType.Int, 4);
            outputVacancyID.Direction = ParameterDirection.Output;

            using (OdbcConnection myConnection = new OdbcConnection(ConfigurationManager.AppSettings["ConnectionString_test"]))
            {
                //setup command using parameterised query
                string strSQLCommand = "INSERT INTO dtblVacancy (date, jobTitle, role, description) VALUES (NOW(), ?, ?, ?); SELECT ? = @@IDENTITY";
                OdbcCommand myCommand = new OdbcCommand(strSQLCommand, myConnection);
                myCommand.CommandType = CommandType.Text;
                myCommand.Parameters.Add(paramJobTitle);
                myCommand.Parameters.Add(paramRole);
                myCommand.Parameters.Add(paramDescription);
                myCommand.Parameters.Add(outputVacancyID);

                //open connection and insert vacancy
                myConnection.Open();
                vacancyID = Convert.ToInt32(myCommand.ExecuteScalar());
            }

any help is much appreciated :slight_smile:

@@IDENTITY is a [bad; use SCOPE_IDENTITY() instead] Sql Server construct. You need to use the MySql equivalent, which I happen to be blanking on at the moment.

if i run this in mysql myadmin it happily returns the last id, but when used in c# it is still erroring with 'check the manual that corresponds to your MySQL server version for the right syntax to use near ‘; select last_insert_id()’


INSERT INTO dtblVacancy (date, jobTitle, role, description) VALUES (NOW(), 'test', 'test', 'test'); select last_insert_id()

there must be something up in my c# code but i cant see it :confused:

using (OdbcConnection myConnection = new OdbcConnection(ConfigurationManager.AppSettings["ConnectionString_test"]))
            {
                //setup command using parameterised query
                string strSQLCommand = "INSERT INTO dtblVacancy (date, jobTitle, role, description) VALUES (NOW(), ?, ?, ?); select last_insert_id()";
                OdbcCommand myCommand = new OdbcCommand(strSQLCommand, myConnection);
                myCommand.CommandType = CommandType.Text;
                myCommand.Parameters.Add(paramJobTitle);
                myCommand.Parameters.Add(paramRole);
                myCommand.Parameters.Add(paramDescription);
                myCommand.Parameters.Add(outputVacancyID);

                //open connection and insert vacancy
                myConnection.Open();
                vacancyID = Convert.ToInt32(myCommand.ExecuteScalar());
            }

for anyone who cares - the .net command object doesnt accept multiple sql statements separated by ;

break the statements out and wrap in an odbc transaction and all works fine.