Records that need to be different

Question

I am creating a database for a project. In this project there is a table called 'Bookings' this table has 2 fields called DateOfBooking (which is data type DateAndTime) and TimeOfBooking (which is data type DateAndTime also). In a UI form on the same project I have created a date and time picker that corresponds to the DateOfBooking field and a text box that corresponds to the TimeOfBooking field. For my system to be as robust as possible, I would like there to be no double bookings. So I need to create a way to check that the specific dates and times haven't already been entered to the database. I can only find ways that allow for one field to be checked, which is not what I would like. I need to check both fields. Here is an attempt of what I have done:

//To Check that there is no double bookings
        

using(SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["SimpsonsConnection"].ConnectionString))
            {
                sc.Open();
                SqlCommand commandToCheckDoubleBookings = new SqlCommand("SELECT DateOfBooking, TimeOfBooking FROM Bookings WHERE DateOfBooking= '" + dtp1.Text + "' AND TimeOfBooking= '" + txtTimeOfBooking.Text + "'", sc);
                string db = (string)commandToCheckDoubleBookings.ExecuteScalar();
                sc.Close();

                if(db == dtp1.Text )
                {
                    MessageBox.Show("This booking slot has already been taken, please choose another!");
                }
                else if(db ==)
            }

In this code, I am getting problems with the if statements, as they aren't checking the two fields at the same time. Only one field is being checked at a time. If there is any way you could help, it would be highly appreciated. If you need any extra info about the problem let me know! (My code is c#)

This is not a scalar query, so you should be using a dataset reader. — 500 - Internal Server Error, Mar 01 '23 at 22:33
[ExecuteScalar](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-7.0) _Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored._ — quaabaam, Mar 01 '23 at 22:34
Does this answer your question? [Check if a record exists in the database](https://stackoverflow.com/questions/21302244/check-if-a-record-exists-in-the-database) — quaabaam, Mar 02 '23 at 00:24

chrislewis · Answer 1 · 2023-03-02T00:08:08.567

In this case you could concatenate the values and compare the concatenated value
The SQL would be:

SELECT CONCAT(DateOfBooking, TimeOfBooking) FROM Bookings WHERE DateOfBooking= '" + dtp1.Text + "' AND TimeOfBooking= '" + txtTimeOfBooking.Text + "'"

Then your C# if statement would be:

if(db == dtp1.Text + txtTimeOfBooking.Text)

You just need to make sure the DateAndTime format returned from the database is the same as the format in the text from the form.

Records that need to be different

1 Answers1