So I am trying to monitor events in my Log database using a semi-complex query, the query works in Access perfectly fine, but when I try to run it in my application, I get the same error every time.
Code:
The Microsoft Office Access database engine does not recognize '@user' as a valid field name or expression.
I get this error when I try to name my OleDbDataReader
I have never used a query like this in VB .Net before and am clueless as to why I can't get it to work
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
cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfControlNumber "
cmdtext = cmdtext + "SELECT Log.User, Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL "
cmdtext = cmdtext + "FROM(Log) "
cmdtext = cmdtext + "WHERE (((Log.User)=@user) AND ((DateValue([DateChanged]))=@date)) "
cmdtext = cmdtext + "GROUP BY Log.User, 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
I am getting the values for "user" and "ndate" as so
VB .net Code:
Private Sub dgvGroupA_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvGroupA.CellDoubleClick
dgvUserDetail.DataSource = GetUserDetail(dgvGroupA.SelectedCells.Item(0).ToString, dtpSearchDate.Value.Date)
End Sub