Results 1 to 9 of 9

Thread: [RESOLVED] OleDB Error - Semi-Complex query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Resolved [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:
    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
    5.             cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfControlNumber "
    6.             cmdtext = cmdtext + "SELECT Log.User, Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL "
    7.             cmdtext = cmdtext + "FROM(Log) "
    8.             cmdtext = cmdtext + "WHERE (((Log.User)=@user) AND ((DateValue([DateChanged]))=@date)) "
    9.             cmdtext = cmdtext + "GROUP BY Log.User, 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 am getting the values for "user" and "ndate" as so

    VB .net Code:
    1. Private Sub dgvGroupA_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvGroupA.CellDoubleClick
    2.         dgvUserDetail.DataSource = GetUserDetail(dgvGroupA.SelectedCells.Item(0).ToString, dtpSearchDate.Value.Date)
    3.     End Sub

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: OleDB Error - Semi-Complex query

    @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.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Re: OleDB Error - Semi-Complex query

    vb .net Code:
    1. cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfControlNumber "
    2.             cmdtext = cmdtext + "SELECT Log.User, Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL "
    3.             cmdtext = cmdtext + "FROM(Log) "
    4.             cmdtext = cmdtext + "WHERE (((Log.User)=?user) AND ((DateValue([DateChanged]))=?date)) "
    5.             cmdtext = cmdtext + "GROUP BY Log.User, Log.StartStatus, Log.EndStatus "
    6.             cmdtext = cmdtext + "PIVOT Hour(Format([DateChanged],'Short Time'));"
    7.             Dim cmd As New OleDbCommand(cmdtext, conn)
    8.             cmd.Parameters.AddWithValue("user", user)
    9.             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:
    1. Using conn As New OleDbConnection(accessConn)
    2.             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)
    3.             cmd.Parameters.AddWithValue("user", user)
    4.             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?

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: OleDB Error - Semi-Complex query

    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.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Re: OleDB Error - Semi-Complex query

    vb .net Code:
    1. cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfControlNumber "
    2.             cmdtext = cmdtext + "SELECT Log.User, Log.StartStatus, Log.EndStatus, Count(Log.ControlNumber) AS TOTAL "
    3.             cmdtext = cmdtext + "FROM(Log) "
    4.             cmdtext = cmdtext + "WHERE (((Log.User) = ?) AND ((DateValue([DateChanged])) = ?)) "
    5.             cmdtext = cmdtext + "GROUP BY Log.User, Log.StartStatus, Log.EndStatus "
    6.             cmdtext = cmdtext + "PIVOT Hour(Format([DateChanged],'Short Time'));"
    7.             Dim cmd As New OleDbCommand(cmdtext, conn)
    8.             cmd.Parameters.AddWithValue("user", user)
    9.             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
    Last edited by thebuffalo; Oct 16th, 2014 at 10:57 AM.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] OleDB Error - Semi-Complex query

    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.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Re: [RESOLVED] OleDB Error - Semi-Complex 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:
    1. Dim selectedUser As String = ""
    2.         Dim FirstValue As Boolean = True
    3.         Dim cell As DataGridViewCell
    4.         For Each cell In dgvUserDetail.SelectedCells
    5.             If Not FirstValue Then
    6.                 selectedUser += ", "
    7.             End If
    8.             selectedUser += cell.Value.ToString()
    9.             FirstValue = False
    10.         Next
    11.         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

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] OleDB Error - Semi-Complex query

    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
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Re: [RESOLVED] OleDB Error - Semi-Complex query

    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:
    1. Public Function GetGroup(ByVal group As String, ByVal ndate As Date) As DataTable
    2.         Dim dt As New DataTable
    3.         dt.Columns.Add("User")
    4.         dt.Columns.Add("Points")
    5.         Using conn As New MySqlConnection(mysqlConn)
    6.             Dim cmd As New MySqlCommand("SELECT id, username FROM users WHERE groupid = @groupid", conn)
    7.             cmd.Parameters.AddWithValue("groupid", group)
    8.             conn.Open()
    9.             Dim rdr As MySqlDataReader = cmd.ExecuteReader()
    10.             Do While rdr.Read
    11.                 Dim dr As DataRow
    12.                 dr = dt.NewRow
    13.                 dr.Item("User") = rdr.Item("username").ToString
    14.                 dr.Item("Points") = GetPoints(rdr.Item("username").ToString, ndate)
    15.                 dt.Rows.Add(dr)
    16.             Loop
    17.             Return dt
    18.         End Using
    19.     End Function

    This is called via Search buttion with

    vb .net Code:
    1. Dim selectedGroup As String = TryCast(cbGroups.SelectedItem, ComboboxItem).Value.ToString
    2.         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
    Last edited by thebuffalo; Oct 16th, 2014 at 11:53 AM.

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