Saturday, January 31, 2015

@reference0 must be defined error message when creating parameters in for loop before SQL insert query

I need to parameterise the query in the for loop, but VS2013 keeps telling me that @reference0 must be defined.



Any reason why this keeps happening?



var dbConnect = new DbConnect();
var cmd = new MySqlCommand();
dbConnect.OpenConnection();

var query =
"INSERT INTO booking (operator_id, plot_id, postcode, datetime, stops, " +
"mileage, price, passengers, name, note, phone, status, reference) " +
"VALUES (@operator_id, @plot_id, @postcode, @datetime, @stops, " +
"@mileage, @price, @passengers, @name, @note, @phone, @status, @reference);";

for (var i = 0; i < _waypointList.Count; i++)
{
query +=
@"INSERT INTO waypoint
(booking_id, sequence, address, lat, lng, reference)
VALUES
((select id FROM booking WHERE reference=@reference" + i + @"),
@sequence" + i + @",
@address" + i + @",
@lat" + i + @",
@lng" + i + @",
@reference" + i + ")";
cmd.Parameters.AddWithValue(("@reference" + i), _reference);
cmd.Parameters.AddWithValue(("@sequence" + i), i);
cmd.Parameters.AddWithValue(("@address" + i), _waypointList[i]);
cmd.Parameters.AddWithValue(("@lat" + i), _lat);
cmd.Parameters.AddWithValue(("@lng" + i), _lng);
}

Console.WriteLine(query);
cmd = new MySqlCommand(query, DbConnect.Connection);
cmd.Parameters.AddWithValue(("@operator_id"), _operatorId);
cmd.Parameters.AddWithValue(("@plot_id"), _plotId);
cmd.Parameters.AddWithValue(("@postcode"), _postcode);
cmd.Parameters.AddWithValue(("@datetime"), _datetime);
cmd.Parameters.AddWithValue(("@stops"), _stops);
cmd.Parameters.AddWithValue(("@mileage"), _mileage);
cmd.Parameters.AddWithValue(("@price"), _price);
cmd.Parameters.AddWithValue(("@passengers"), _passengers);
cmd.Parameters.AddWithValue(("@name"), _name);
cmd.Parameters.AddWithValue(("@note"), _note);
cmd.Parameters.AddWithValue(("@phone"), _phone);
cmd.Parameters.AddWithValue(("@status"), Status);
cmd.Parameters.AddWithValue(("@reference"), _reference);

cmd.ExecuteNonQuery();
dbConnect.CloseConnection();

No comments:

Post a Comment