Access Crosstab Query
Keith Fink (MS)
<<
<<Does anyone know if you can pass parameters to a
<<Microsoft Access crosstab query and return the results in
<<VB.net? I have a query that returns results perfectly
<<using a command object, but I have been unable to pass
<<parameters to filter the data.
<<
Yes, you must create a new Parameter object, add it to the Command object, and set its value. Here is some sample code which demonstrates
this with ADO.NET using an OleDbDataReader object.
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim parm As OleDbParameter
Dim rdr As OleDbDataReader
'Create a new OleDbConnection object using the managed
'OLEDB provider and open the Northwind database.
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb")
cn.Open()
'Create a new OleDbCommand object and set it to the
'Quarterly Orders by Product crosstab query
cmd = New OleDbCommand("[Quarterly Orders by Product]")
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
'Create a new OleDbParameter object, set its value and append
'it to the Parameters collection of the command object
parm = New OleDbParameter("Enter Beginning Date", OleDbType.Date)
parm.Value = "1/1/1997"
cmd.Parameters.Add(parm)
'Repeat the process for a second parameter
parm = New OleDbParameter("Enter Ending Date", OleDbType.Date)
parm.Value = "12/31/1997"
cmd.Parameters.Add(parm)
'Execute the query
rdr = cmd.ExecuteReader
'Loop through all rows returned by the query
'and print them to the output window
While rdr.Read
Debug.WriteLine(rdr("CustomerID"))
End While
cn.Close()
Hope this helps!
Sincerely,
Keith Fink
Microsoft Developer Support
This posting is provided “AS IS” with no warranties, and confers no rights.
Rick Wiker
this to work. When I
add input criteria to the query, it works fine when run
from Access, but
returns no values to the command object. When I remove
them, all records
are returned. I've tried setting the criteria as
parameters in the query,
but that didn't work either.
Thanks for your help.
Private Sub GetSchedData()
Dim objCmd As New OleDbCommand()
Dim SchedInfo As DataSet, vDate As Date,
vInstructor As Int16,
vDateStr As String
vDate = Format(DateValue
(WeekEndPicker.SelectedItem.Value),
"M/d/yyyy")
vInstructor = CInt
(EmployeePicker.SelectedItem.Value)
objCmd.Connection = OleDbConnection1
OleDbConnection1.Open()
objCmd = OleDbCommand1
objCmd.Parameters.Add("@WeekEnd",
OleDbType.Date).Value = vDate
objCmd.Parameters.Add("@EmployeeID",
OleDbType.Integer).Value =
vInstructor
Dim objReader As OleDbDataReader =
objCmd.ExecuteReader
DataGrid1.DataSource = objReader
DataGrid1.DataBind()
objReader.Close()
OleDbConnection1.Close()
End Sub
TRANSFORM Max(WeeklySchedule.CustomerName) AS CustName
SELECT Format([weeklyschedule].[Times],"hh:nn AM/PM") AS
Times
FROM WeeklySchedule
WHERE (((WeeklySchedule.EmployeeID)=[@EmployeeID]) AND
((WeeklySchedule.WeekEnd)= [@WeekEnd])) OR
(((WeeklySchedule.EmployeeID) Is
Null))
GROUP BY Format([weeklyschedule].[Times],"hh:nn
AM/PM"),
WeeklySchedule.Times
ORDER BY WeeklySchedule.Times
PIVOT Format([datekey],'dddd') In
('Sunday','Monday','Tuesday','Wednesday','Thursday','Frida
y','Saturday');
>.
>
Rick Wiker
Thanks for your help.
>rdrÍ{ w?!« ¬Il D¯z à_A °ì = cmd.ExecuteReader
Keith Fink (MS)
<<
<<Keith, thanks for your help. I'm still unable to get
<<this to work. When I add input criteria to the query, it works fine when run
<<from Access, but
<<returns no values to the command object. When I remove
<<them, all records
<<are returned. I've tried setting the criteria as
<<parameters in the query,
<<but that didn't work either.
<<
This should work fine as long as you're inputting valid criteria. I set this up with the Quarterly Orders by Product crosstab query in the
Northwind database and it worked fine for me. What happens if you use hard coded criteria in the query within Access(instead of
parameters)? Does that return records to the Command object? If so, then the problem has something to do with the way you're feeding the
parameters to the Command object. If it doesn't, then there is something incompatible with the query design and the way we're accessing it
through the managed OLEDB provider.
Here is the code I am using:
Sub GetSchedData()
Dim objCmd As New OleDbCommand()
Dim objReader As OleDbDataReader
OleDbConnection1.Open()
objCmd = OleDbCommand1
objCmd.Connection = OleDbConnection1
objCmd.Parameters.Add("[Enter Beginning Date]", OleDbType.Date).Value = BeginningDate.Text
objCmd.Parameters.Add("[Enter Ending Date]", OleDbType.Date).Value = EndingDate.Text
DataGrid1.DataSource= Nothing
objReader = objCmd.ExecuteReader()
DataGrid1.DataSource = objReader
DataGrid1.DataBind()
objReader.Close()
OleDbConnection1.Close()
End Sub
Here is the crosstab query in the Northwind database:
PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Sum(CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
Rick Wiker
params in the query and input into the input boxes. Could you send me
the SQL of the query you are using please?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
colin
dataset.defaultview with a .rowfilter expression
Colin
"Rick Wiker" <not...@mediaone.net> wrote in message
news:uIAiVnjuBHA.2660@tkmsftngp05...
Keith Fink (MS)
<<
<<It works fine when I input the criteria manually, or if I setup input
<<params in the query and input into the input boxes. Could you send me
<<the SQL of the query you are using please?
<<
The query I'm using is the Quarterly Orders by Product crosstab query in the sample Northwind.mdb database. Its SQL is:
PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Sum(CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
The code I used to get to it from the webform is:
Sub GetSchedData()
Dim objCmd As New OleDbCommand()
Dim objReader As OleDbDataReader
OleDbConnection1.Open() 'Uses Jet OLEDB provider and points to Northwind.mdb
objCmd = OleDbCommand1 'set to Quarterly Orders by Product, CommandType is storedprocedure
objCmd.Connection = OleDbConnection1
'Create parameters and add them to the Parameters collection
'Getting value for each parameter from textboxes on the page
objCmd.Parameters.Add("[Enter Beginning Date]", OleDbType.Date).Value = BeginningDate.Text
objCmd.Parameters.Add("[Enter Ending Date]", OleDbType.Date).Value = EndingDate.Text
'Clear out the datagrid's current DataSource
DataGrid1.DataSource= Nothing
'Execute the command and bind the datagrid to the datareader
objReader = objCmd.ExecuteReader()
DataGrid1.DataSource = objReader
DataGrid1.DataBind()
objReader.Close()
OleDbConnection1.Close()
End Sub
Hope this helps!
Rick Wiker
Thanks so much.