locked
An expression of non-boolean type specified in context where a condition is expected RRS feed

  • Question

  • An expression of non-boolean type specified in context where a condition is expected when I mouseover the top line:


    SELECT Bookings.Date, Bookings.Timebegan, Bookings.Timefinished, Bookings_1.Date, Bookings_1.Timebegan, Bookings_1.Timefinished,
    
                      Bookings.RoomID, Bookings_1.RoomID
    
    FROM     Bookings,
    
                      Bookings AS Bookings_1
    
    WHERE Not Nz(([Bookings_1].[Timebegan]>=[Bookings].[Timefinished]) Or
    
      ([Bookings_1].[Timefinished]<=[Bookings].[Timebegan]) Or
    
      ([Bookings].[RoomID]!=[Bookings_1].[RoomID]) Or
    
      ([Bookings].[ID]=[Bookings_1].[ID]),False)
    
    ORDER BY Bookings.ID, Bookings_1.ID;


    Does any one have any idea?


    Monday, March 11, 2013 11:44 PM

Answers

  • Why you're using Decimal(18,0) for Time Begin and Time Finish? I think you may want to either use Time or DateTime here. Time if it's recurring booking and you don't care of the actual date and datetime if it's a one time deal. Using decimal(18,0) doesn't help here.

    As I said, once the structure of the table is corrected, we need to create a stored procedure starting like this

    create procedure dbo.BookRoom (@RoomId int ,@StartTime datetime -- or time ,@EndTime datetime -- or time) AS SET NOCOUNT ON; declare @Id int, @TimeBegin datetime, @TimeFinish datetime select @Id = Id, @TimeBegin = TimeBegin, @TimeEnd = TimeFinish

    from dbo.Bookings B

    where B.RoomId = @RoomID and (@StartTime between B.TimeBegin and B.TimeFinish -- fix to be datetime OR @EndTime between B.TimeBegin and B.TimeFinish; IF @Id IS NOT NULL -- we do have an overlapping booking begin declare @ErrorMessage varchar(200); set @ErrorMessage = 'The room ' + convert(varchar(20), @RoomId) + ' can not be booked because it conflicts with the existing booking ' + convert(varchar(20), @Id) + ' that starts at ' + convert(varchar(30), @TimeBegin) + ' and end at ' + convert(varchar(30), @TimeFinish) + CHAR(13) + 'Please select another room' raiserror(@ErrorMessage, 16,1) return - 1 end MERGE INTO Bookings as Target using (select @RoomId as RoomId, @StartDate as TimeBegin, @EndDate as TimeFinish) as Source ON Target.RoomId = Source.RoomID and (Source.TimeBegin between Target.TimeBegin and Target.TimeFinish OR Source.TimeFinish between Target.TimeBegin and Target.TimeFinish) WHEN NOT MATCHED -- all is well INSERT (RoomID, TimeBegin, TimeFinish) values (source.RoomID, source, TimeBegin, source.TimeFinish) IF @@ROWCOUNT = 0 -- we didn't insert the row -- Handle this error here also -- perhaps someone just inserted a conflicting booking


    This is from the top of my head and may have errors - I am just showing you the logic as how I would approach this problem. Also, I assumed we're using SQL 2008 and above, that's why I used MERGE command. I used merge to make sure to not get into sometimes failing pattern.

    There is also a possibility of handling the above with the constraints. Here is a blog on this exact topic

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, March 12, 2013 5:03 PM
    • Marked as answer by Kalman Toth Monday, March 25, 2013 9:09 PM
    Tuesday, March 12, 2013 5:00 PM
  • NotNz(([Bookings_1].[Timebegan]>=[Bookings].[Timefinished]) Or

     
    ([Bookings_1].[Timefinished]<=[Bookings].[Timebegan]) Or

     
    ([Bookings].[RoomID]!=[Bookings_1].[RoomID]) Or

     
    ([Bookings].[ID]=[Bookings_1].[ID]),False)

    This is not T-SQL. You are mixing in a different language.  Nz? False?

    You can take a look at SQL examples at:

    http://www.sqlusa.com/bestpractices/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Monday, March 11, 2013 11:56 PM
    • Proposed as answer by Eshani Rao Tuesday, March 12, 2013 12:11 AM
    • Marked as answer by Naomi N Monday, March 25, 2013 9:37 PM
    Monday, March 11, 2013 11:56 PM

All replies

  • NotNz(([Bookings_1].[Timebegan]>=[Bookings].[Timefinished]) Or

     
    ([Bookings_1].[Timefinished]<=[Bookings].[Timebegan]) Or

     
    ([Bookings].[RoomID]!=[Bookings_1].[RoomID]) Or

     
    ([Bookings].[ID]=[Bookings_1].[ID]),False)

    This is not T-SQL. You are mixing in a different language.  Nz? False?

    You can take a look at SQL examples at:

    http://www.sqlusa.com/bestpractices/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Monday, March 11, 2013 11:56 PM
    • Proposed as answer by Eshani Rao Tuesday, March 12, 2013 12:11 AM
    • Marked as answer by Naomi N Monday, March 25, 2013 9:37 PM
    Monday, March 11, 2013 11:56 PM
  • This is how NZ function in Access defined

    http://www.techonthenet.com/access/functions/advanced/nz.php

    You're putting some strange logical expression for the first argument of that function. What exactly you're trying to do and why do you need to use that function in your query WHERE clause at all?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 12, 2013 12:22 AM
  • I wanted to use this to prevent double bookings, how would i change it so sql sever management will accept it?
    Tuesday, March 12, 2013 9:58 AM
  • If you can post DDL of your both tables, some sample data and desired result, we would be able to create a query to SQL Server. 

    Are you using SQL Server or a different backend?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 12, 2013 1:07 PM
  • here is the DDL for the whole database, the 2 tables that are used in this query are the bookings and maybe the rooms table.  i wanted to prevent double bookings form occuring.

    -- --------------------------------------------------
    -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
    -- --------------------------------------------------
    -- Date Created: 03/07/2013 20:08:48
    -- Generated from EDMX file: C:\Users\Ahmad Khattab\Google Drive\projects\wpf7\WpfApplication7\WpfApplication7\Model1.edmx
    -- --------------------------------------------------
    
    SET QUOTED_IDENTIFIER OFF;
    GO
    USE [AllensCroft];
    GO
    IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
    GO
    
    -- --------------------------------------------------
    -- Dropping existing FOREIGN KEY constraints
    -- --------------------------------------------------
    
    IF OBJECT_ID(N'[dbo].[FK_Bookings_ToTable]', 'F') IS NOT NULL
        ALTER TABLE [dbo].[Bookings] DROP CONSTRAINT [FK_Bookings_ToTable];
    GO
    IF OBJECT_ID(N'[dbo].[FK_Bookings_ToTable_1]', 'F') IS NOT NULL
        ALTER TABLE [dbo].[Bookings] DROP CONSTRAINT [FK_Bookings_ToTable_1];
    GO
    
    -- --------------------------------------------------
    -- Dropping existing tables
    -- --------------------------------------------------
    
    IF OBJECT_ID(N'[dbo].[Bookings]', 'U') IS NOT NULL
        DROP TABLE [dbo].[Bookings];
    GO
    IF OBJECT_ID(N'[dbo].[Clients]', 'U') IS NOT NULL
        DROP TABLE [dbo].[Clients];
    GO
    IF OBJECT_ID(N'[dbo].[Rooms]', 'U') IS NOT NULL
        DROP TABLE [dbo].[Rooms];
    GO
    
    -- --------------------------------------------------
    -- Creating all tables
    -- --------------------------------------------------
    
    -- Creating table 'Bookings'
    CREATE TABLE [dbo].[Bookings] (
        [Id] int IDENTITY(1,1) NOT NULL,
        [Username] nvarchar(50)  NULL,
        [Date] datetime  NULL,
        [Timebegan] [decimal](18, 0) NULL,
    	[Timefinished] [decimal](18, 0) NULL,
        [Price] decimal(19,4)  NULL,
        [Repeats] bit  NULL,
        [Rdays] int  NULL,
        [Rweeks] int  NULL,
        [Rmonths] int  NULL,
        [Occurrences] int  NOT NULL,
        [ClientID] int  NULL,
        [RoomID] int  NULL
    );
    GO
    
    -- Creating table 'Clients'
    CREATE TABLE [dbo].[Clients] (
        [ClientID] int IDENTITY(1,1) NOT NULL,
        [FirstName] nvarchar(50)  NOT NULL,
        [Surname] nvarchar(50)  NOT NULL,
        [Internal] bit  NOT NULL,
        [Organisation] nvarchar(50)  NULL,
        [LandlineNo] nvarchar(15)  NULL,
        [MobileNo] nvarchar(11)  NULL,
        [Address1] nvarchar(50)  NULL,
        [Town] nvarchar(50)  NULL,
        [Postcode] nvarchar(7)  NULL,
        [Email] nvarchar(225)  NOT NULL,
        [NHS] bit  NULL,
        [PurchaseOrderNumber] nvarchar(50)  NULL
    );
    GO
    
    -- Creating table 'Rooms'
    CREATE TABLE [dbo].[Rooms] (
        [RoomID] int IDENTITY(1,1) NOT NULL,
        [Roomname] nvarchar(50)  NOT NULL,
        [Projector] bit  NULL,
        [Capacity] int  NULL
    );
    GO
    
    -- --------------------------------------------------
    -- Creating all PRIMARY KEY constraints
    -- --------------------------------------------------
    
    -- Creating primary key on [Id] in table 'Bookings'
    ALTER TABLE [dbo].[Bookings]
    ADD CONSTRAINT [PK_Bookings]
        PRIMARY KEY CLUSTERED ([Id] ASC);
    GO
    
    -- Creating primary key on [ClientID] in table 'Clients'
    ALTER TABLE [dbo].[Clients]
    ADD CONSTRAINT [PK_Clients]
        PRIMARY KEY CLUSTERED ([ClientID] ASC);
    GO
    
    -- Creating primary key on [RoomID] in table 'Rooms'
    ALTER TABLE [dbo].[Rooms]
    ADD CONSTRAINT [PK_Rooms]
        PRIMARY KEY CLUSTERED ([RoomID] ASC);
    GO
    
    -- --------------------------------------------------
    -- Creating all FOREIGN KEY constraints
    -- --------------------------------------------------
    
    -- Creating foreign key on [ClientID] in table 'Bookings'
    ALTER TABLE [dbo].[Bookings]
    ADD CONSTRAINT [FK_Bookings_ToTable]
        FOREIGN KEY ([ClientID])
        REFERENCES [dbo].[Clients]
            ([ClientID])
        ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    -- Creating non-clustered index for FOREIGN KEY 'FK_Bookings_ToTable'
    CREATE INDEX [IX_FK_Bookings_ToTable]
    ON [dbo].[Bookings]
        ([ClientID]);
    GO
    
    -- Creating foreign key on [RoomID] in table 'Bookings'
    ALTER TABLE [dbo].[Bookings]
    ADD CONSTRAINT [FK_Bookings_ToTable_1]
        FOREIGN KEY ([RoomID])
        REFERENCES [dbo].[Rooms]
            ([RoomID])
        ON DELETE NO ACTION ON UPDATE NO ACTION;
    
    -- Creating non-clustered index for FOREIGN KEY 'FK_Bookings_ToTable_1'
    CREATE INDEX [IX_FK_Bookings_ToTable_1]
    ON [dbo].[Bookings]
        ([RoomID]);
    GO
    
    -- --------------------------------------------------
    -- Script has ended
    -- --------------------------------------------------

    Tuesday, March 12, 2013 4:30 PM
  • So, do you want to see if you already have double bookings? Or you want to create a procedure that will attempt to book a Room for a specific time? Or do you want to allow that procedure to attempt to book multiple rooms with their times (sending table valued parameter)?

    I think you may want to create a procedure that will attempt to book one room for specific times. If the room is already in the booking table for the overlapping time ranges, then you will return an error to the caller with the RAISERROR.

    Do you need help with writing that stored procedure or you want to start from identifying if you already have double booking in your table?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 12, 2013 4:37 PM
  • i am also using sql server management studio. here is some sample data in the bookings table.

    i want an error message if someone tries to have a a booking that clashes with another booking  on the same date in that room.

    Can we get a message box in C#displaying that the room is used at the date and time already

    SELECT Bookings.Date, Bookings.Timebegan, Bookings.Timefinished, Bookings_1.Date, Bookings_1.Timebegan, Bookings_1.Timefinished,
    
                      Bookings.RoomID, Bookings_1.RoomID
    
    FROM     Bookings,
    
                      Bookings AS Bookings_1
    
    WHERE Not Nz(([Bookings_1].[Timebegan]>=[Bookings].[Timefinished]) Or
    
      ([Bookings_1].[Timefinished]<=[Bookings].[Timebegan]) Or
    
      ([Bookings].[RoomID]!=[Bookings_1].[RoomID]) Or
    
      ([Bookings].[ID]=[Bookings_1].[ID]),False)
    
    ORDER BY Bookings.ID, Bookings_1.ID;

    Tuesday, March 12, 2013 4:39 PM
  • Why you're using Decimal(18,0) for Time Begin and Time Finish? I think you may want to either use Time or DateTime here. Time if it's recurring booking and you don't care of the actual date and datetime if it's a one time deal. Using decimal(18,0) doesn't help here.

    As I said, once the structure of the table is corrected, we need to create a stored procedure starting like this

    create procedure dbo.BookRoom (@RoomId int ,@StartTime datetime -- or time ,@EndTime datetime -- or time) AS SET NOCOUNT ON; declare @Id int, @TimeBegin datetime, @TimeFinish datetime select @Id = Id, @TimeBegin = TimeBegin, @TimeEnd = TimeFinish

    from dbo.Bookings B

    where B.RoomId = @RoomID and (@StartTime between B.TimeBegin and B.TimeFinish -- fix to be datetime OR @EndTime between B.TimeBegin and B.TimeFinish; IF @Id IS NOT NULL -- we do have an overlapping booking begin declare @ErrorMessage varchar(200); set @ErrorMessage = 'The room ' + convert(varchar(20), @RoomId) + ' can not be booked because it conflicts with the existing booking ' + convert(varchar(20), @Id) + ' that starts at ' + convert(varchar(30), @TimeBegin) + ' and end at ' + convert(varchar(30), @TimeFinish) + CHAR(13) + 'Please select another room' raiserror(@ErrorMessage, 16,1) return - 1 end MERGE INTO Bookings as Target using (select @RoomId as RoomId, @StartDate as TimeBegin, @EndDate as TimeFinish) as Source ON Target.RoomId = Source.RoomID and (Source.TimeBegin between Target.TimeBegin and Target.TimeFinish OR Source.TimeFinish between Target.TimeBegin and Target.TimeFinish) WHEN NOT MATCHED -- all is well INSERT (RoomID, TimeBegin, TimeFinish) values (source.RoomID, source, TimeBegin, source.TimeFinish) IF @@ROWCOUNT = 0 -- we didn't insert the row -- Handle this error here also -- perhaps someone just inserted a conflicting booking


    This is from the top of my head and may have errors - I am just showing you the logic as how I would approach this problem. Also, I assumed we're using SQL 2008 and above, that's why I used MERGE command. I used merge to make sure to not get into sometimes failing pattern.

    There is also a possibility of handling the above with the constraints. Here is a blog on this exact topic

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, March 12, 2013 5:03 PM
    • Marked as answer by Kalman Toth Monday, March 25, 2013 9:09 PM
    Tuesday, March 12, 2013 5:00 PM
  • Hi,

    Thanks for your detailed answer.  I am trying to understand what you have done, is this an SQL query and I do have the 2012 version. How would I reference this code? I have this in the save button event handler:

      try

                    {

                        //check if there are any changes prevents someone pressing save twice and you may get duplicate entries in database

                        if (this.allensCroftDataSet.HasChanges())

                        {

                            //It will then check if a row has been added:

                            if ((this.TableManager.UpdateAll(this.allensCroftDataSet) > 0))

                            {

                                //And it will then display a message box saying “saved”.

                                if (MessageBox.Show("Saved.", this.Title, MessageBoxButton.OK) == MessageBoxResult.OK)

                                {

                                    this.Close();

                                }

                            }

                        }

                    }

                    catch (Exception ex)

                    {

                        MessageBox.Show(ex.ToString());

                    }

    So if there are no errors, it will update the database, I could give you more code so you can see how the saving works as I have used datasets and data binding in WPF. I also don’t see what you did there:

    IF@@ROWCOUNT=0-- we didn't insert the row

        -- Handle this error here also -- perhaps someone just inserted a conflicting booking

    But thanks again for all you help.

    I look forward to hearing your reply.

    Tuesday, March 12, 2013 6:47 PM
  • This is a bit tricky now since you're using TableManager. Since that's the case and I am not sure how to map (if possible at all) TableManager to the stored procedure for insert, I think you for now may want to try to go with the constraints based solution for the intervals. I gave you a link to Alex article as how to construct such constraints. See if you can make it work for your scenario. If not, then you may want to ask question in Windows Forms forum (or some related forum) as how to set up TableManager to use this stored procedure for inserts (BTW, we may also want something similar for update if we allow to adjust bookings).

    -- Handle the error is just my comment for you. You will handle it similar way I showed at the beginning with the RAISERROR with the only exception that we don't know why we haven't inserted a row exactly - I assume it's because a new row was inserted right after our first check was done but before the MERGE command. So, the error message text will be a bit more vague.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 12, 2013 7:07 PM
  • Yes i will do that, thanks for all your help
    Tuesday, March 12, 2013 7:23 PM
  • where B.RoomId = @RoomID and (@StartTime between B.TimeBegin and B.TimeFinish OR @EndTime between B.TimeBegin and B.TimeFinish;

    Just wanted to mention that if the @StartTime is before B.TimeBegin and @EndTime is after B.TimeFinish, then this WHERE clause would not catch this invalid booking.

    It can be fixed and "simplified" to this:

    where B.RoomId = @RoomID
    and   @StartTime < B.TimeFinish
    and   @EndTime >= B.TimeBegin
    


    Gert-Jan

    Wednesday, March 13, 2013 9:38 PM
  • I usually consult this WiKi page for that expression

    http://wiki.lessthandot.com/index.php/Date_Range_WHERE_Clause_Simplification

    which gives exactly the same expression, but for some reason wanted to write it myself from the top of my head when answering.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, March 13, 2013 9:57 PM