SQL "Not In" Statment
SQL "Not In" Statment
SQL "Not In" Statment
SQL "Not In" Statment
SQL "Not In" Statment
SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment SQL "Not In" Statment
SQL "Not In" Statment SQL "Not In" Statment
SQL "Not In" Statment
Go Back  Xtreme Visual Basic Talk > > > SQL "Not In" Statment


Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2013, 02:19 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default SQL "Not In" Statment


Hi there

I have 2 data grids. One shows the results of this SQL Query

Code:
SearchCommand = New OleDbCommand("SELECT * FROM Booking WHERE DateIn BETWEEN @datein AND  @dateout  OR Dateout  BETWEEN  @datein AND  @dateout", searchcon)
That works perfectly.

Which essentially returns any bookings within the "hotel system" that are between the two defined dates. Meaning they are unavalible for those rooms between those dates

HOWEVER

I want another Data grid to show what rooms ARE avalible, so essentially the opposite of that SQL query

This is what I have so far.

Code:
SearchAvalibleCommand = New OleDbCommand("SELECT * FROM Rooms WHERE RoomNumber NOT IN (SELECT RoomNumber FROM Booking WHERE DateIn BETWEEN @datein AND  @dateout  OR Dateout  BETWEEN  @datein AND  @dateout)", searchavaliblecon)
That doesn't work though, Any suggestions
Thanks
Luke
Reply With Quote
  #2  
Old 02-11-2013, 02:22 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

"Data Type mismatch in criteria expression" is the error I get

Please help. This is the full code for that sub

Code:
 Sub searchavalible()

        Dim SearchAvalibleCommand As OleDbCommand = Nothing
        Dim SearchAvalibleAdapter As New OleDbDataAdapter
        Dim SearchAvalibleTable As New DataTable
        Dim recordcount As Integer


        Try
            SearchAvalibleCommand = New OleDbCommand("SELECT * FROM Rooms WHERE RoomNumber NOT IN (SELECT RoomNumber FROM Booking WHERE DateIn BETWEEN @dateIn AND  @dateOut  OR Dateout  BETWEEN  @datein AND  @dateout)", searchavaliblecon)
            SearchAvalibleCommand.Parameters.Add("@dateIn", OleDb.OleDbType.Date).Value = searchbydatein.Value
            SearchAvalibleCommand.Parameters.Add("@dateOut", OleDb.OleDbType.Date).Value = searchbydateout.Value

            'establish command object and data adapter
            SearchAvalibleAdapter.SelectCommand = SearchAvalibleCommand
            SearchAvalibleAdapter.Fill(SearchAvalibleTable)
            'bind grid view to data table
            searchavaliblegrd.DataSource = SearchAvalibleTable
            recordcount = SearchAvalibleTable.Rows.Count.ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        SearchAvalibleCommand.Dispose()
        SearchAvalibleAdapter.Dispose()
        SearchAvalibleTable.Dispose()


        If recordcount < 1 Then
            MsgBox("No Results " & recordcount)


        Else
            MsgBox("RESULT " & recordcount)
        End If

    End Sub
Reply With Quote
  #3  
Old 02-11-2013, 02:39 AM
DrPunk's Avatar
DrPunkSQL &quot;Not In&quot; Statment DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Data Type Mismatch tends to suggest that you're trying to compare one type with a different type. Like say trying to compare and Integer with a String.

So you have to look at your query and try and work out what could be wrong.

Is the RoomNumber field in both tables the same Type?
__________________
There are no computers in heaven!
Reply With Quote
  #4  
Old 02-11-2013, 02:50 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

I cant belive I missed that. Iv put the room number in "booking" as text.

So if I change this, will it cause errors in the rest of the system? or am I better of changing the rooms one to text?
Reply With Quote
  #5  
Old 02-11-2013, 03:34 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

I changed the Rooms one to text in order to solve it, as changing booking to number caused more errors.

Thanks for the help again DrPunk.

Your a life saver. There probably wil be another thread from me soon enough lol as im trying to sort some problems and understand reports at the moment

thanks
luke
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
 
SQL &quot;Not In&quot; Statment
SQL &quot;Not In&quot; Statment
 
-->