Sql table check

Question

I have a table with the following fields ID IDuser IDpc FROM TO. obviously a PC cannot be used during the same time period by more than one user. how do i place a constraint on entries so that incorrect entries are prevented? I use sql Server 2016 (management studio) with asp.net

Answer 1

ok, as noted, I though the part about multiple users using the same PC had to do with multi-user databases!

I now see that you are booking a PC to be used, and you don't want booking collisions.

Ok, there is a VERY neat condition to test/check for a booking collision.

It looks like this: A collision occurs when:

RequestStartDate <= EndDate 
and 
RequestEndDate >= StartDate 

And if the values include date + time, then the above still works just fine.

The above condition will find ANY kind of overlap (so a date/time in the middle) or any parts that overlap.

As I suggested in comments? You could get/have the data base not allow you to add that row (you would have to use a table trigger).

However, then what?

What this REALLY suggests? You don't write out the record and expect a database failue. Worse yet, you really want to give the user some nice feed back.

So, your booking page would ask for the room, and then the start/end time (with date). You use the above condition, and if record(s) are returned, then you tell the user they can't book the room. However, if no matches occur, then you add that row to the database.

This kind of problem actually seems quite difficult, but it turns out with the above simple condition, is is remarkable simple.

Lets do this simple example as a asp.net webforms.

So, drop in a list box, two text boxes (start/end) and a book button.

So, the markup looks like this:

    <div style="margin-left:40px">
        <h2>Book a work station</h2>
        <div style="float:left">
            <h3>Select a work station</h3>
            <asp:ListBox ID="lstCompter" runat="server" 
                DataTextField="Computer" DataValueField="ID" Height="151px" Width="294px"></asp:ListBox>
        </div>

        <div style="float:left;margin-left:20px">
            <div style="float:left">
                <h3>Start Time</h3>
                <asp:TextBox ID="txtStart" runat="server" TextMode="DateTimeLocal"></asp:TextBox>
            </div>
            <div style="float:left;margin-left:20px">
                <h3>End Time</h3>
                <asp:TextBox ID="txtEnd" runat="server" TextMode="DateTimeLocal"></asp:TextBox>
            </div>
            <div style="clear:both;float:left;margin-top:40px">
                <asp:Button ID="cmdBook" runat="server" Text="Book Room" />
            </div>
            <div style="clear:both;float:left">
                <br />
                <asp:Label ID="lblMsg" runat="server" Text=""></asp:Label>
            </div>
        </div>
    </div>

I tossed in a few divs to lay this out.

Ok, now the code to load up the listbox was this:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()
    End If

End Sub

Sub LoadData()

    Using cmdSQL As New SqlCommand("SELECT ID, Computer from Computers ORDER BY Computer",
                    New SqlConnection(My.Settings.TEST4))
        cmdSQL.Connection.Open()
        lstCompter.DataSource = cmdSQL.ExecuteReader
        lstCompter.DataBind()
    End Using

End Sub

And now we get this:

Note that if you drop in that textbox and in the property sheet choose DateTimeLocal as the format, then without any extra code, you get that way cool date = time picker for free.

Now, lets write the code to check for if we can book.

The user selects a room, and then the start/end times (that could be for 1 hour, or one 1 week - it don't matter.

So, now our book button code looks like this:

Protected Sub cmdBook_Click(sender As Object, e As EventArgs) Handles cmdBook.Click

    Dim strSQL As String
    strSQL = "SELECT * FROM Bookings WHERE IDPc = @IDpc " &
             "AND @RequestStart <= [TO] " &
             "AND @RequestEnd >= [From] "

    Using cmdSQL As New SqlCommand(strSQL, New SqlConnection(My.Settings.TEST4))

        cmdSQL.Parameters.Add("IDpc", SqlDbType.Int).Value = lstCompter.SelectedItem.Value
        cmdSQL.Parameters.Add("@RequestStart", SqlDbType.DateTime).Value = txtStart.Text
        cmdSQL.Parameters.Add("@RequestEnd", SqlDbType.DateTime).Value = txtEnd.Text

        cmdSQL.Connection.Open()
        Dim rstBooking As New DataTable
        rstBooking.Load(cmdSQL.ExecuteReader)

        If rstBooking.Rows.Count > 0 Then
            ' booking not allowed - show message
            lblMsg.Text = "Computer station already booked - try differnt date/time"
        Else
            ' add this booking
            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daupdate As New SqlCommandBuilder(da)

            Dim OneRow As DataRow = rstBooking.Rows.Add
            OneRow("IDpc") = lstCompter.SelectedValue
            OneRow("IDUser") = LogOnID
            OneRow("From") = txtStart.Text
            OneRow("To") = txtEnd.Text
            da.Update(rstBooking)

            lblMsg.Text = "Room booked!"
        End If

    End Using


End Sub

Note how simple this becomes. In about the SAME time it took me to write this post? I in fact have a real working booking page. It would need more love an care then a quick dirty example like above, but all in all, it is remarkable that the above works.

our Computers (table) to book for the list box was this:

And then the booking table of course is this:

And that is quite much it. You can see we query the database, and if we find a match (collision), then we NEVER even try to add the row, and we give a user that message.

But, if now rows are found, then we add the row to the database.

So, it will look like this:

It is times like this that one realizes how amazing simple this was in asp.net.

Enjoy!

FYI: both "to" and "from" are SQL words - you have to use [To] and [From] (brackets around) those words, since SQL server will get confused - those column names are what we call reserved words - and "FROM" is part of regular sql syntax, so just remember to use those [] around the sql.