CODEWITHSUNDEEP
c#asp.netmysql
G Gr
G Gr

Reputation: 6080

convert commandtext to string problem in answered question

Question Related to this post, I think he is offline though so I can't get any feedback atm:

MySql and inserting last ID problem remains

I'm not sure if I'm being completely blind but in this code:

// run the insert using a non query call
command.CommandText = cmd.ToString();
command.ExecuteNonQuery();

/* now we want to make a second call to MYSQL to get the new index 
   value it created for the primary key.  This is called using scalar so it will
    return the value of the SQL  statement.  We convert that to an int for later use.*/
command.CommandText = "select last_insert_id();";
id = Convert.ToInt32(command.ExecuteScalar());

//------- the name id does not exist in the current context

// Commit the transaction.
transaction.Commit();
}
catch (Exception ex) {
  Label10.Text = ": " + ex.Message;

  try {
    // Attempt to roll back the transaction.
    transaction.Rollback();
  } catch {
    // Do nothing here; transaction is not active.
  }
}
}

ID isn't set to anything and I'm not sure how he was trying to set it to the last inserted id?

**EDIT: **

int id = Convert.ToInt32(cmd.ExecuteScalar());
Label10.Text = Convert.ToString(id);

Upvotes: 0

Views: 949

Answers (1)

BrokenGlass
BrokenGlass

Reputation: 160892

You should be able to return the id of the inserted record using select @@IDENTITY; without having to use a second query:

 OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email) VALUES ('[email protected]'); select @@IDENTITY;"
 int id = Convert.ToInt32(cmd.ExecuteScalar());

After reviewing your old question, this should work the same:

 OdbcCommand cmd = new OdbcCommand("INSERT INTO User (Email) VALUES ('[email protected]'); SELECT LAST_INSERT_ID();"
 int id = Convert.ToInt32(cmd.ExecuteScalar());

Upvotes: 1

Related Questions