cannot find solution to my problem. Currently working on console app for making room reservation. I would like my program to not insert another record into my database, if there is already one with either arrival or departure date between already existing or if arrival and departure date are "outside" already taken dates.
Dates I'm insterting into table are in format "YYYY-mm-dd", same as in MySQL date format.
Insert only is working fine (tested), the problem is with WHERE NOT EXISTS condition.
When I'm trying to execute Query i receive error:
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM 'rezerwacje' WHERE (2017-07-15 BETWEEN ArrivalDa' at line 1
public static void AddReservation(string conn, string tableName, string arrivalDate, string departureDate, string name, string surname)
{
MySqlConnection connection = new MySqlConnection(conn);
string dbQuery = $"INSERT INTO {tableName} (Arrivaldate, DepartureDate, Name, Surname) " +
$"VALUES ({arrivalDate}, {departureDate}, '{name}', '{surname}') " +
$"WHERE NOT EXISTS (SELECT * FROM {tableName} " +
$"WHERE ({arrivalDate} BETWEEN ArrivalDate AND DepartureDate)" +
$"OR ({departureDate} BETWEEN ArrivalDate AND DepartureDate) " +
$"OR ({arrivalDate} < ArrivalDate AND {departureDate} > DepartureDate)) LIMIT 1";
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(dbQuery, connection);
int checkIfExists = cmd.ExecuteNonQuery();
connection.Close();
if (checkIfExists > 0)
{
Console.WriteLine($"Your reservation from '{arrivalDate}' to '{departureDate}' has been succesful.");
return;
}
else
{
Console.WriteLine($"Unfortunately the date you would like to reserve is already taken. Please choose another date");
return;
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
WHERE ...
clause can only be included if you are using INSERT INTO ... SELECT FROM
construct which is not the case for you and thus the error
User contributions licensed under CC BY-SA 3.0