CODEWITHSUNDEEP
c#mysql.netsql
YosiFZ
YosiFZ

Reputation: 7900

C# Get insert id with Auto Increment

I am using this method to insert a row into a table:

            MySqlConnection connect = new MySqlConnection(connectionStringMySql);
            MySqlCommand cmd = new MySqlCommand();

            cmd.Connection = connect;
            cmd.Connection.Open();

            string commandLine = @"INSERT INTO Wanted (clientid,userid,startdate,enddate) VALUES" +
                "(@clientid, @userid, @startdate, @enddate);";
            cmd.CommandText = commandLine;

            cmd.Parameters.AddWithValue("@clientid", userId);
            cmd.Parameters.AddWithValue("@userid", "");
            cmd.Parameters.AddWithValue("@startdate", start);
            cmd.Parameters.AddWithValue("@enddate", end);

            cmd.ExecuteNonQuery();
            cmd.Connection.Close();

I hav also id column that have Auto Increment . And i want to know if it possible to get the id that is created when i insert a new row.

Upvotes: 16

Views: 34258

Answers (3)

Tim
Tim

Reputation: 1239

MySqlConnection connect = new MySqlConnection(connectionStringMySql);
MySqlCommand cmd = new MySqlCommand();

cmd.Connection = connect;
cmd.Connection.Open();

string commandLine = @"INSERT INTO Wanted (clientid,userid,startdate,enddate) "
    + "VALUES(@clientid, @userid, @startdate, @enddate);";
cmd.CommandText = commandLine;

cmd.Parameters.AddWithValue("@clientid", userId);
**cmd.Parameters["@clientid"].Direction = ParameterDirection.Output;**
cmd.Parameters.AddWithValue("@userid", "");
cmd.Parameters.AddWithValue("@startdate", start);
cmd.Parameters.AddWithValue("@enddate", end);

cmd.ExecuteNonQuery();
cmd.Connection.Close();

Upvotes: 1

dugas
dugas

Reputation: 12433

You can access the MySqlCommand LastInsertedId property.

cmd.ExecuteNonQuery();
long id = cmd.LastInsertedId;

Upvotes: 34

pero
pero

Reputation: 4259

Basically you should add this to end of your CommandText:

SET @newPK = LAST_INSERT_ID();

and add another ADO.NET parameter "newPK". After command is executed it will contain new ID.

Upvotes: 0

Related Questions