SQL query help

[Eva]

Hi,

Im new to Vb.net and am trying to create my first
practice project. The problem iv encountered is to do
with SQL queries issued against my access DB iv created.
i want to wright a query that will pull all the caravan
details that are available at a partuclar date which will
be entered by the user. The query will have to ensure
that the caravans are not booked by another guest for
that specifed date period. the 3 tables i need are:

Guest Info table:
Booking No (PK)
Name
Addr
Date of Arrival
Date of Departure

Caravan Booking table (this table contains the caravans
the guest has booked for a particular date interval):
Caravan model No (PK, FK)
Booking No (PK, FK)

Caravan details table (this table contaisn all the
caravans held in the park):
Caravan model No (PK)
Cost/day
Beds in caravan.

Can anyone help me with this query? How do i include the
dates that the user enters on my interface using the
DateTimePicker control into this query? Im not sure how
to integrate the users input dates into this query.

thx for your time. )

 

[William Ryan]

I'm assuming that you only have one DateTimePicker and that Arrival_Date of
GuestInfo is how you are booking it. If not, you can change the logic (in
my example below you'd use Two Date parameters instead of one)

Build a Dataset filling each of the three tables... then use a DataRelation
object to link the three together
http://msdn.microsoft.com/library/d...ry/en-us/vbcon/html/vboridatasetrelations.asp

You can use DataTable.Select
http://msdn.microsoft.com/library/d.../frlrfsystemdatadatatableclassselecttopic.asp

Or create a DataView and set the rowfilter... .
http://msdn.microsoft.com/library/d...rlrfsystemdatadataviewclassrowfiltertopic.asp
to match the crieria you want.

This will allow you to look things up by customer.

Now, with your dateTimePicker on the valueChanged event, you can set the
rowfilter of a Dataview to = the value of the DateTimePicker. If you get
records back when you filter the dataview, then it's rented.

Now, this is assuming you want to do all of the math client side (which is
definitely a good way to do it). Make sure that you submit updates
immediately and that you app refershes itself b/c since you are using a
disconnected model, you don't want to go rent things that are already rented
just b/c you go lost in the lag time.

You could also use create a Stored Procedure or fire a query to hit the DB
every time the DateTimePicker changes and then determine from that if it's
booked...

SELECT COUNT(*) FROm GuestInfo where
Arrival_Date >= @ArrivalDate and Arrival_Date=< DateAdd(dd, 1, @ArrivalDate)

You can just use an ExecuteScalar and check for > 0 or use a return code
like so
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp

If this is a really volatile system, meaning that you make updates and
changes very frequently from multiple machines, then you will adjust your
refresh rate to accomodate it and you'll need to look into dealing with
Concurrency...but that is step 2...

Does this help?

 

[Eva]

Hi William,
thx for your help. Im kinda having difficulties on
understanding the procedure u outlined (sorry im a
complete beginner). Do i use the oledataadapter and enter
the query in there? i can select all three tables in
there and then choose the coloumns i need in the query
builder.
I am using 2 datetimePickers in my app. One for the
arrival date and one for the departure_date.
Is there anyway this type of query could be entered in
the OleDataAdapter query builder? Im not to sure how i
would enter the user inputs into the query thou.
i.e arrival date and departure_date.

thx

 

[William Ryan]

The Provider you use depends on the backend DB that you have. You want to
use the most specific provider available...SQLClient for SQL Server,
OracleClient for Oracle etc.

Now, it's important to identify what's happening. To get data to and from a
Database assuming you are using disconnected methodology, you'll need four
things at a minimum
1) A Connection to the Database (This is the actual Connection Object)
2) A Command (This contains the SQL Statement you want to execute coupled
with the Connection to use to execute that Statement)
3) A DataAdapter (this is what does the Heavy Lifting for you actually
moving the data back and forth)
4) A DataSet or DataTable Hold the data
<This is a bit of an oversimplification b/c there are other objects, but
this is effectively the scenario you have described>

Now, if you use a Join statement, you are going to pull over a whole lot of
data that's redundant. Since the ADO.NET basically creates a client side
representation of your Database (you could have an identical copy or just a
subset), It actually pulls over a copy of all that data. All of the
benefits of normalization are diminished if you are using a Join statement
b/c joins give you redundant data. As such, you can pull over the tables
one at a time and then Relate them using a DataRelation. If you choose this
method, you have effectively 'joined' the tables, the only difference being
that you are doing it client side and only Pulling/Pushing the data that you
need. It's much more efficient to use a DataRelation than pull all of that
data over that you don't need.

Now, you can uses three DataAdapters for instance and fire 3
DataAdapter.Fill statements. Conversely, you can use One DataAdapter ,fire
three different select statements and then use TableMappings to identify
which table is which.

Now, if you use a DataAdapterConfiguration wizard, it will write the
update/insert/select logic for you as long as your tables are keyed (and
Non-Keyed tables are the Computer Equivalent of Satan, and in your case, all
of your tables are keyed). Your next choice is to use a CommandBuilder.
CommandBuilders are rather limiting although they are very convenient for
simple Select and Update scenarios. All you need for a CommandBuilder to
work it's magic is a SELECT Statement, A DataAdapter and a connection. It
will infer the Update/Delete/Insert logic based on the SELECT statement.
Now, if you are using Stored Procedures, forget about the CommandBuilder
(after all, how can it infer Update Logic from a Stored Procedure name?).
the other way to handle it is to hand code your own update logic, but if you
are just starting out....The DataAdapter Configuration Wizard is probably
your best bet.

How would you get the dates? Ok, there are two things you may be doing in
this scenario. First you pull over all of your data and populate your
controls. now, when the user looks for a given data, you can either query
your data locally by querying the DataTable or DataView or you can requery
the Database. Clearly querying the local data is the more 'efficient' in
terms of resource usage, but since its disconnected this may not be an
option depending on how often data is updated.

You can use Parameters from the Command and assign the value of the
DateTimePicker controls to them...


I gave you an example before -- substitute @ArrivalDate with
YourDateTimePicker.Value...

If this is a commercial app, and I say this affectionately, do yourself a
favor and read up on ADO.NET. Bill Vaughn's book
http://www.betav.com/Files/Books/books.htm has saved me many times over and
so has David Sceppa's book
http://www.amazon.com/exec/obidos/t...f=pd_sim_art_elt/104-9506987-0039901?v=glance
is another must have. Buy one or both of them and take a day or two to just
work with the examples. If you are going to put this into production, it's
critical that you really understand what you are doing. You can use the
Wizards and put this thing together, but there are issues related to
concurrency (what happens if users get the same record, make different
changes and submit them at different times? You need to have a strategy to
determine who wins), performance, security that really need to be
understood.

There are some good examples on MSDN and all over the internet, so you can
just search google on the terms DataAdapter, DataView, RowFilter,
CommandBuilder and the other examples mentioned below and you'll get a good
handle on things.

HTH,

Bill

 

[Eva]

Hi William,

Thank u so much for your exlanation. It cleared alot of
questions that i had......it was excellently explained )

I will definatly purchase those books u recommended.
Sounds like i will def need them!!
The system i am developing is a standalone system for a
single user so i will hopefully not have to worry to much
about concurrency issues with regard to my database.
I wanted to ask if i could write the sql queries into my
actually code page for, say an onclick event of a button?
If this is poss how to i integrate it into the exiting
code.

thx once again for ytour time and patience. )

-----Original Message-----
The Provider you use depends on the backend DB that you have. You want to
use the most specific provider available...SQLClient for SQL Server,
OracleClient for Oracle etc.

Now, it's important to identify what's happening. To get data to and from a
Database assuming you are using disconnected methodology, you'll need four
things at a minimum
1) A Connection to the Database (This is the actual Connection Object)
2) A Command (This contains the SQL Statement you want to execute coupled
with the Connection to use to execute that Statement)
3) A DataAdapter (this is what does the Heavy Lifting for you actually
moving the data back and forth)
4) A DataSet or DataTable Hold the data
<This is a bit of an oversimplification b/c there are other objects, but
this is effectively the scenario you have described>

Now, if you use a Join statement, you are going to pull over a whole lot of
data that's redundant. Since the ADO.NET basically creates a client side
representation of your Database (you could have an identical copy or just a
subset), It actually pulls over a copy of all that data. All of the
benefits of normalization are diminished if you are using a Join statement
b/c joins give you redundant data. As such, you can pull over the tables
one at a time and then Relate them using a

DataRelation. If you choose this

method, you have effectively 'joined' the tables, the only difference being
that you are doing it client side and only

Pulling/Pushing the data that you