-
Oct 16th, 2014, 02:35 PM
#1
Thread Starter
Hyperactive Member
Access PIVOT Query with DataTable
I am trying to use a PIVOT query to get information based on Events in my Log table per Hour(DateChanged)
I get the column names (StartStatus, EndStatus, TOTAL) but I do not get the Hour pivot columns (0, 1, 2, 3 etc) and I do not get any of the data that should be showing
I ran the output commandtext in access with filled parameters and it returns values exactly how it should with the hour columns
I guess my questions are:
1. Am I doing this correctly?
2. Could my parameters not be filling into the query? How can I fix this?
2. Can I even use this method to fill a datatable with a Pivot query?
Here is my code:
vb .net Code:
Public Function GetUserDetail(ByVal user As String, ByVal ndate As Date) As DataTable Dim dt As New DataTable Using conn As New OleDbConnection(accessConn) Dim cmdtext As String = vbNullString cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfCtrl " cmdtext = cmdtext + "SELECT Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL " cmdtext = cmdtext + "FROM(Log) " cmdtext = cmdtext + "WHERE (((Log.User) = ?) AND ((DateValue([DateChanged])) = ?)) " cmdtext = cmdtext + "GROUP BY Log.StartStatus, Log.EndStatus " cmdtext = cmdtext + "PIVOT Hour(Format([DateChanged],'Short Time'));" Dim cmd As New OleDbCommand(cmdtext, conn) cmd.Parameters.AddWithValue("user", user) cmd.Parameters.AddWithValue("date", ndate.ToString("MM-dd-yyyy")) conn.Open() Dim rdr As OleDbDataReader = cmd.ExecuteReader dt.Load(rdr) conn.Close() Return dt End Using End Function
EDIT:
I tried to do
vb .net Code:
Do While rdr.Read Dim dr As DataRow = dt.NewRow For i = 0 To rdr.FieldCount - 1 dr.Item(i) = rdr.Item(i) Next dt.Rows.Add(dr) Loop
instead of dt.load, the Do While rdr.Read never executes it just skips on to return the datatable
So the DataReader doesnt read, but somehow gets me the column names ?
Last edited by thebuffalo; Oct 16th, 2014 at 02:48 PM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|