[RESOLVED] OleDB Error - Semi-Complex query

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

@user would be a parameter, but Access databases don't do named parameters. For them, all parameters are just ?, and you supply the values in the order that they are found in the query. Therefore, you can leave everything you have done the same, except that in the query string, replace all @whatever with ?. The fact that you later use the name when adding the parameter won't matter, because the engine will ignore the names you supply for the parameter. In SQL Server, the engine would match the names of the parameters with the names in the query, so you can supply the parameters in whatever order you feel like. For Access, you have to supply them in the order they are found in the query.

vb .net Code:

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"))

I might have misunderstood you, but is this what you meant?

It still throws an error

Code:

---

Syntax error (missing operator) in query expression '(((Log.User)=?user) AND ((DateValue([DateChanged]))=?date))'.

---

I use @whatever with another access query that works fine, so your response kind of confused me, and I have only had one cup of coffee

vb .net Code:

Using conn As New OleDbConnection(accessConn)
            Dim cmd As New OleDbCommand("SELECT StartStatus, EndStatus, COUNT(id) AS CountOfid FROM log WHERE User = @user AND DateChanged > @date GROUP BY StartStatus, EndStatus", conn)
            cmd.Parameters.AddWithValue("user", user)
            cmd.Parameters.AddWithValue("date", ndate.ToString("MM-dd-yyyy"))

This query works without any errors what is the difference that I can't use @whatever in this one, and when I use ?whatever I still get an error?

Not ?user, just plain ?

However, I'd have to say that I'm surprised that the other query works. There may be different flavors of Access database engines. I was under the impression that you couldn't use a named parameter in ANY Access query, but it looks kind of like you can. There is a difference between the two examples, though. In the first post, you used @user, then added a parameter named @user. In the snippet that you say works, you use @user in the query and just plain user as the name of the parameter. ?user certainly won't work, as that wasn't what I meant anyways, so the fact that that doesn't work means nothing.

What I would suggest trying is to go back to your original code and change it to match the code you say works. If @user in the query and just plain "user" in the parameter name works, I'd find that interesting.

vb .net Code:

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) = ?) AND ((DateValue([DateChanged])) = ?)) "
            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"))

with it like this the reader will read and the DGV will load, but it does not load any information to the DGV, so is the query working and DGV doesnt support Pivot? or is the query still not working

I have tested this query in access and it returns a full table User,StartStatus,EndStatus,Total, Hours 0-24 depending on if criteria was met for that hour

edit: what i meant when the said the DGV will load, i mean it gets the all the column information except the Hour columns


Okay, it seems that the query is working perfectly i am just not giving it the correct parameters now


dgvGroupA.SelectedCells.Item(0).ToString is returning "DataGridViewTextBoxCell { ColumnIndex=0, RowIndex=0 }" instead of the actual item in the selected cell

I will have to find a way to do this but the topic of this thread has been solved

Thanks for your help shaggy, you have consistently saved me over the last 2 years

I've never used PIVOT, and am not even sure what it does. It's quite possible that the DGV doesn't support some aspect of that.

The query you show is clearly Access, because it has WAY too many parentheses scattered through it. When you say that you tested the query in access, did you put a breakpoint on this line (or either of the lines after it):

Dim cmd As New OleDbCommand(cmdtext, conn)

then take the cmd.CommandText and copy it into a query in Access to see if it ran? You'd have to copy the parameters over manually, but that wouldn't be too hard. The point I'm getting at is that without doing it like that, there could be some subtle difference between what you are doing in Access and what you are actually doing in the code, so I'm asking about a test where you use the exact same SQL in both places. You may have already done something like that.

After further considering what you added as an edit, I'm not sure whether that test would even gain you anything. It sounds like you are essentially getting everything you expect in the query with a brief exception. PIVOT sounds like pivot tables, and it looks like that is roughly what it is meant for. The DGV doesn't do pivoting, as far as I know, so I've used 3rd party controls to do things like that ($$$, and I'm glad I wasn't paying), so it may be that it is simply dropping that part off the query.

I think the Pivot will work for the DGV because essentially it is just adding columns to my DataTable that are 0 ( 12:00AM ), 1 (1:00 AM), 2 (2:00 AM), etc

The problem is currently my ByVal user is not getting anything so therefore my query is looking for where Log.User = "" and "" is not a person.

This is my first time using a DGV because I don't like them, I am just going to have to figure out how to extract the Cell I need from my other DGV to feed my query

vb .net Code:

Dim selectedUser As String = ""
        Dim FirstValue As Boolean = True
        Dim cell As DataGridViewCell
        For Each cell In dgvUserDetail.SelectedCells
            If Not FirstValue Then
                selectedUser += ", "
            End If
            selectedUser += cell.Value.ToString()
            FirstValue = False
        Next
        dgvUserDetail.DataSource = GetUserDetail(selectedUser, dtpSearchDate.Value.Date)

This is giving me "" for some reason even though I have this being called off of a CellDoubleClick event, so there is a currently selected Cell

dgvGroupA.SelectedCells.Item(0).ToString returns "DataGridViewTextBoxCell { ColumnIndex=0, RowIndex=0 }"

so my where clause is basically WHERE User = "" AND DateChanged = "10/10/2014" which will return nothing, because nothing matches that, therefore my Pivot will do nothing and load no columns

I need to figure out how to retrieve the Text value of the current selected cell of dgvGroupA


User StartStatus EndStatus TOTAL 12 13 14 15 16 17 18 19 20 21 22 23
ahale Ready 9Typing Rejects 25 4 2 1 7 4 3 3 1
ahale Ready Waiting Payment 140 23 16 6 10 3 10 12 8 14 23 15

This is what the table looks like after running the query in Access when it is given the correct parameters

Is the other DGV coming from a datatable? If so, I'd be using the underlying datatable rather than the cell in the DGV.

However, I'd also stay away from selected cells. The CellDoubleClick event gives you a DataGridViewCellEventArgs as the e argument, which has a RowIndex and ColumnIndex property. If the DGV is bound to a datatable, then the RowIndex would be the RowIndex in the datasource (and you would already know which column you wanted). If the DGV isn't bound, then getting the info from the DGV based on the RowIndex and ColumnIndex would be more direct. I don't trust selected stuff in DGV anyways. Here's a link with an example:

http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx

I think either way I would have to check the selected cell because the cell contains the "user"

The other DGV datasource is a datatable but it is declared inside of a function

vb .net Code:

Public Function GetGroup(ByVal group As String, ByVal ndate As Date) As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("User")
        dt.Columns.Add("Points")
        Using conn As New MySqlConnection(mysqlConn)
            Dim cmd As New MySqlCommand("SELECT id, username FROM users WHERE groupid = @groupid", conn)
            cmd.Parameters.AddWithValue("groupid", group)
            conn.Open()
            Dim rdr As MySqlDataReader = cmd.ExecuteReader()
            Do While rdr.Read
                Dim dr As DataRow
                dr = dt.NewRow
                dr.Item("User") = rdr.Item("username").ToString
                dr.Item("Points") = GetPoints(rdr.Item("username").ToString, ndate)
                dt.Rows.Add(dr)
            Loop
            Return dt
        End Using
    End Function

This is called via Search buttion with

vb .net Code:

Dim selectedGroup As String = TryCast(cbGroups.SelectedItem, ComboboxItem).Value.ToString
        dgvGroupA.DataSource = GetGroup(selectedGroup, dtpSearchDate.Value.Date)

I need this second DGV to be loaded based on the user they DoubleClick inside of the first DGV, and the second DGV will show the details of their Log by the hour

Im just having trouble now getting the user they selected into my query for the second DGV

So any way that I can retrieve the user they double clicked will work, I just don't know how to go about this

I guess these are my options:
1. Keep previous DataTable in memory to get username from and verify against dgv selectedcell?
2. Get actual selected cell information?
3. Program A.I. that writes programs for me and fixes my mistakes


EDIT: ok well I got my value but the DGV still doesnt load

dgvGroupA.CurrentRow.Cells(0).Value.ToString ; This will get you the first cell of the selected row

Unfortunately I am thinking that my PIVOT is ruining my data retrieval because the parameters are now correct, the query is running, but the reader is returning nothing

Going to play around with error handling and see if theres something being hidden from me

Im really breaking the topic of this thread now so I'll just have to figure this one out on my own

When I take the CommandText and put it directly in Access with filled parameters, it runs perfect and returns everything I need

So something bewteen the Reader and DataTable.Load is removing all of the data