Data Grid
Data Grid
Data Grid
Data Grid
Data Grid
Data Grid Data Grid Data Grid Data Grid Data Grid Data Grid Data Grid Data Grid
Data Grid Data Grid
Data Grid
Go Back  Xtreme Visual Basic Talk > > > Data Grid


Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2013, 09:04 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default Data Grid


Hi there

Currently I have a form with 2 data grids.

The first one shows the result of an SQL query calling information from a table called Booking

The second merely displays the data from the table Rooms (both tables in the same mdb file)

Both data grids will show the column "Roomnumber"

My question is, how can I get it so that if there is a result with room number 1 (for example) in the first gird from the sql, that the record from the ROOMs table with the Room number 1 in it will not show in the second data grid.

So essentially any rooms that are booked show in the first, any rooms left are shown in the second data grid

Does that make sense?
Reply With Quote
  #2  
Old 02-07-2013, 09:47 AM
DrPunk's Avatar
DrPunkData Grid DrPunk is offline
Senior Contributor

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

Hi Brown

Yeah, sorta makes sense.

It strikes me that you need a cleverer query for your second grid to display.

It shouldn't be too difficult to come up with something based on the query of the first view. If your first query returns rooms booked by date then you could query Rooms where the RoomNumber is not in that query.

For example, let's say we want to query all the bookings that are coming in on a particular day (just to make a simpler query)...
Code:
SELECT * FROM Bookings WHERE InDate = @someDate
Now you want to know the rooms, from the room's table, that don't appear in that query. That can be achieved using that query (I can't confirm that I have the syntax spot on here)...
Code:
SELECT * FROM Rooms WHERE RoomNumber NOT IN (SELECT RoomNumber FROM Bookings WHERE InDate = @someDate)
The original query returned all the rooms, this second query looks for RoomNumbers that don't appear in that query. The only change to the original query is getting it to only return the field we are interested in (NOT IN will only work on one field).

I'm not saying this is the best (most efficient) way of doing it, but it's a way of achieving it without much additional work than what you have got.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 02-08-2013, 01:06 PM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

so essentially all I need to do is take my original query and create a second running in this context:

Code:
SELECT * FROM Rooms WHERE RoomNumber NOT IN(FIRST SQL QUERY)
then call that and assign the results to a different results table ?

so then both query's will run, but display their results in different tables
Reply With Quote
  #4  
Old 02-08-2013, 01:26 PM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

Here is what I created

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
when its run it says theres a data type mismatch expression in criteria
Reply With Quote
  #5  
Old 02-09-2013, 02:37 PM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

Any suggestions?
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
Data Grid
Data Grid
Data Grid Data Grid
Data Grid
Data Grid
Data Grid Data Grid Data Grid Data Grid Data Grid Data Grid Data Grid
Data Grid
Data Grid
 
Data Grid
Data Grid
 
-->