Query and variables help.

[Angelina]

Hi,

I wanted to know how i can pass varibales from my vb.net
interface to my Access xp database??

What i want to do is use the date selected by the user
from 2 dtpickers and use these in my OLEDataAdapter query.
(I dont know what to type in in the OLEDataAdapter query
builder).

what do i write in my query to represent these variables
in my query??

what i want to write a query that selects the rooms that
are not booked withing the date period specified by the
user.

I have 3 tables that i may need to use:
Room Details (a descrption of all the rooms in the hotel):
RoomNo
RoomName

Room Booking (a link table to handle the M:N
relationship):
RoomNo
BookingNo

Booking Table (Contains details of the guest and his
booking details):
BookingNo
DateOfArrival
DateOfDeparture

Can anyone plz help me with this. Iv been stuck for
ages (

 

[Hussein Abuthuraya[MSFT]]

Angelina,

Here is a code that executes the "Sales by year" Stored procedure in Access Northwind's database. It takes 2 date parameters and returns all orders that falls between
the dates. The value of the dates are coming from 2 DatetiemPicker controls.

I hope this helps!

cnOledb = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\AccessDB\nwind2k.mdb")

Dim cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = "[Sales By Year]"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cnOledb

Dim p1 As New OleDbParameter
p1.OleDbType = OleDbType.Date
p1.Value = DateTimePicker1.Value.Date
Dim p2 As New OleDbParameter
p2.OleDbType = OleDbType.Date
p2.Value = DateTimePicker2.Value.Date

cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
daOledb = New OleDbDataAdapter(cmd)
Dim ds As New DataSet

daOledb.Fill(ds, "OrdersTable")
DataGrid1.DataSource = ds.Tables("OrdersTable")


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Angelina]

That is great, Thx Hussein. )

I didnt know u could create stored procedures in Access
XP. In the older versions of access u couldnt.
Do i create the query in access then?? The example u gave
me below doent contain the query, so im alittle uncertain
as to where i have to wirte it.

What symbol do i use to represent a parameter in the
query?

Many thx

-----Original Message-----
Angelina,

Here is a code that executes the "Sales by year" Stored

procedure in Access Northwind's database. It takes 2
date parameters and returns all orders that falls between

the dates. The value of the dates are coming from 2 DatetiemPicker controls.

I hope this helps!

cnOledb = New OleDbConnection

("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=D:\AccessDB\nwind2k.mdb")

Dim cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = "[Sales By Year]"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cnOledb

Dim p1 As New OleDbParameter
p1.OleDbType = OleDbType.Date
p1.Value = DateTimePicker1.Value.Date
Dim p2 As New OleDbParameter
p2.OleDbType = OleDbType.Date
p2.Value = DateTimePicker2.Value.Date

cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
daOledb = New OleDbDataAdapter(cmd)
Dim ds As New DataSet

daOledb.Fill(ds, "OrdersTable")
DataGrid1.DataSource = ds.Tables("OrdersTable")


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft

Strategic Technology Protection Program and to order your
FREE Security Tool Kit, please visit

 

[William \(Bill\) Vaughn]

Well, the "stored procedures" in JET are really querydefs--no more. Calling
them stored procedures is confusing but it made the JET folks happier.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

That is great, Thx Hussein. )

I didnt know u could create stored procedures in Access
XP. In the older versions of access u couldnt.
Do i create the query in access then?? The example u gave
me below doent contain the query, so im alittle uncertain
as to where i have to wirte it.

What symbol do i use to represent a parameter in the
query?

Many thx

-----Original Message-----
Angelina,

Here is a code that executes the "Sales by year" Stored

procedure in Access Northwind's database. It takes 2
date parameters and returns all orders that falls between

the dates. The value of the dates are coming from 2 DatetiemPicker controls.

I hope this helps!

cnOledb = New OleDbConnection

("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=D:\AccessDB\nwind2k.mdb")

Dim cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = "[Sales By Year]"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cnOledb

Dim p1 As New OleDbParameter
p1.OleDbType = OleDbType.Date
p1.Value = DateTimePicker1.Value.Date
Dim p2 As New OleDbParameter
p2.OleDbType = OleDbType.Date
p2.Value = DateTimePicker2.Value.Date

cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
daOledb = New OleDbDataAdapter(cmd)
Dim ds As New DataSet

daOledb.Fill(ds, "OrdersTable")
DataGrid1.DataSource = ds.Tables("OrdersTable")


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft

Strategic Technology Protection Program and to order your
FREE Security Tool Kit, please visit

http://www.microsoft.com/security.


.

 

[Hussein Abuthuraya[MSFT]]

You are welcome!

If you have Northwind.mdb, then you should have the "Sales By Year" built-in query. The name "stored procedure" is just similar to what other Databases use but in fact it is a
"stored query". Just in case, here is the query syntax:

PARAMETERS [[Forms]![Sales by Year Dialog]![BeginningDate]] DateTime, [[Forms]![Sales by Year Dialog]![EndingDate]] DateTime;
SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Format([ShippedDate],"yyyy") AS [Year]
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]!
[EndingDate]));

As you see, the syntax is little different than SQL Server Stored procedure or Oracle but you don't need to worry about the syntax. Use Access to create the query using the
GUI and it should generate the script for you!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.