Results 1 to 1 of 1
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012

    Unhappy 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:
    1. Public Function GetUserDetail(ByVal user As String, ByVal ndate As Date) As DataTable
    2.         Dim dt As New DataTable
    3.         Using conn As New OleDbConnection(accessConn)
    4.             Dim cmdtext As String = vbNullString
    5.             cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfCtrl "
    6.             cmdtext = cmdtext + "SELECT Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL "
    7.             cmdtext = cmdtext + "FROM(Log) "
    8.             cmdtext = cmdtext + "WHERE (((Log.User) = ?) AND ((DateValue([DateChanged])) = ?)) "
    9.             cmdtext = cmdtext + "GROUP BY Log.StartStatus, Log.EndStatus "
    10.             cmdtext = cmdtext + "PIVOT Hour(Format([DateChanged],'Short Time'));"
    11.             Dim cmd As New OleDbCommand(cmdtext, conn)
    12.             cmd.Parameters.AddWithValue("user", user)
    13.             cmd.Parameters.AddWithValue("date", ndate.ToString("MM-dd-yyyy"))
    14.             conn.Open()
    15.             Dim rdr As OleDbDataReader = cmd.ExecuteReader
    16.             dt.Load(rdr)
    17.             conn.Close()
    18.             Return dt
    19.         End Using
    20.     End Function


    I tried to do

    vb .net Code:
    1. Do While rdr.Read
    2.                 Dim dr As DataRow = dt.NewRow
    3.                 For i = 0 To rdr.FieldCount - 1
    4.                     dr.Item(i) = rdr.Item(i)
    5.                 Next
    6.                 dt.Rows.Add(dr)
    7.             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


Click Here to Expand Forum to Full Width