Retrieving last INSERT ID - C#/MySql

.NET
1

So there is a handful of info on the net regarding how to access the last INSERT ID if you are using vb and sql or other products. cant find anything regarding my two products of choice. C# and MySql.

I have done this with PHP, but i do not know the command for it using C# asp.net.

Basically, i have a form that will submit into the db, but i have another INSERT statment that will run imediately following the first INSERT, but the catch is, i need to insert the row ID of INSERT statement 1 into INSERT statement 2.

MySqlConnection conn;
			MySqlCommand cmd;
			string myConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			conn = new MySqlConnection(myConnectionString);
			cmd = new MySqlCommand();
			cmd.Connection=conn;
			cmd.CommandText=strQuery;
			
			try
			{
				conn.Open();
				cmd.ExecuteNonQuery();
				conn.Close();
			}

this is what i have for the first INSERT if it helps at all. If you need to see the sql stmt I can post that too.

Thanks
Ras

2

Add this to the end of your query:


;SELECT SCOPE_IDENTITY()

and change your code to be like this:


using (SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
				{
					using (SqlCommand cmd = new SqlCommand(strQuery, con))
					{
						cmd.Connection.Open();
						rint = (int)cmd.ExecuteScalar();
					}
				}
3

He’s using MySQL. LAST_INSERT_ID() :wink:

4

Sorccu,

isn’t that the php function? I can’t seem to find any asp.net info on that function.

Is it actually a MySql stmt? that i have to query seprately?

Ras

5
  1. yes
  2. maybe, perhaps ado.net (think that’s what its called?) supports somehow but I really don’t know, you’d have to ask a .NET guy (and yes I know this is the .NET forum :))
6

So i have this then:

MySqlConnection conn;
			MySqlCommand cmd;
			string myConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			conn = new MySqlConnection(myConnectionString);
			cmd = new MySqlCommand();
			cmd.Connection=conn;
			cmd.CommandText=strQuery;

			MySqlCommand lastId;
			MySqlDataReader lid = null;
			lastId = new MySqlCommand();
			lastId.Connection=conn;
			lastId.CommandText=("SELECT LAST_INSERT_ID()");
			
			try
			{
				conn.Open();
				cmd.ExecuteNonQuery();
				lid = lastId.ExecuteReader(CommandBehavior.CloseConnection);
				conn.Close();
			}

now how would i reference the data from that lid query?

something like lid.ID maybe

Ras

7

took a different approach than what i just listed previously and it seems to be working properly.

instead of creating a third query, i just added the LAST_INSERT_ID function into the second INSERT query. much simpler.

thanks for the help guys.
Ras

8

what your best off doing is inserting your data, then selecting the LAST_INSERT_ID or what ever your using.

Then use

lid = lastId.ExecuteScalor();

that will return the data without the need to execute a reader.