Trying to retrieve last id inserted into booking table but getting "Subquery returns more than 1 row" error message

Question

I am trying to retrieve the last id inserted into a particular table so I can use it in another, but keeping getting the following error message:

Update 1

Additional information: 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 last_insert_id() FROM booking)', '0', 'MK12 5DR,162,Windsor Street,Wolver' at line 1

// Open connection and insert booking
string query =
    "INSERT INTO booking (operator_id, plot_id, postcode, datetime, stops, mileage, price, passengers, name, note, phone, status) VALUES ('" +
    _operatorId + "', '" +
    _plotId + "', '" + _postcode + "', '" + _datetime + "', '" + _stops + "', '" + _mileage + "', '" +
    _price + "', '" + _passengers + "', '" + _name + "', '" + _note + "', '" + _phone + "', '" + Status +
    "');";
for (int i = 0; i < _waypointList.Count; i++)
{
    query +=
        "INSERT INTO waypoint (booking_id, sequence, address, lat, lng) VALUES (select last_insert_id() FROM booking)" +
        "', '" +
        i + "', '" + _waypointList[i] + "', '" + _lat + "', '" + _lng + "');";
}
var dbObject = new DbConnect();
dbObject.InsertBooking(query);

Update 2

Made a few changes:

// Open connection and insert booking
string query =
    "INSERT INTO booking (operator_id, plot_id, postcode, datetime, stops, mileage, price, passengers, name, note, phone, status) VALUES ('" +
    _operatorId + "', '" +
    _plotId + "', '" + _postcode + "', '" + _datetime + "', '" + _stops + "', '" + _mileage + "', '" +
    _price + "', '" + _passengers + "', '" + _name + "', '" + _note + "', '" + _phone + "', '" + Status +
    "');";
for (int i = 0; i < _waypointList.Count; i++)
{
    query +=
        "INSERT INTO waypoint (booking_id, sequence, address, lat, lng) VALUES ((select last_insert_id() FROM booking), '" + i + "', '" + _waypointList[i] + "', '" + _lat + "', '" + _lng + "');";
}
var dbObject = new DbConnect();
dbObject.InsertBooking(query);

Now I'm getting the following error message:

Subquery returns more than 1 row

Any idea how I could resolve it?

@SonerGönül - I am trying to get the id from the booking table (of the row that gets inserted in the first half of the query), and then insert it into the waypoint table (booking_id column). — methuselah, Dec 10 '14 at 15:02
@HamletHakobyan @SonerGönül - now getting the following error message: `Subquery returns more than 1 row` — methuselah, Dec 10 '14 at 15:15

score 1 · Accepted Answer · answered Dec 10 '14 at 15:15

Try this:

// Open connection and insert booking
string query =
    "INSERT INTO booking (operator_id, plot_id, postcode, datetime, stops, mileage, price, passengers, name, note, phone, status) VALUES ('" +
    _operatorId + "', '" +
    _plotId + "', '" + _postcode + "', '" + _datetime + "', '" + _stops + "', '" + _mileage + "', '" +
    _price + "', '" + _passengers + "', '" + _name + "', '" + _note + "', '" + _phone + "', '" + Status +
    "');";
for (int i = 0; i < _waypointList.Count; i++)
{
    query +=
        "INSERT INTO waypoint (booking_id, sequence, address, lat, lng) VALUES ((select MAX(last_insert_id()) FROM booking), '" + i + "', '" + _waypointList[i] + "', '" + _lat + "', '" + _lng + "');";
}
var dbObject = new DbConnect();
dbObject.InsertBooking(query);

Trying to retrieve last id inserted into booking table but getting "Subquery returns more than 1 row" error message

1 Answers1