-
Oct 16th, 2014, 09:45 AM
#1
Thread Starter
Hyperactive Member
[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
-
Oct 16th, 2014, 10:30 AM
#2
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
-
Oct 16th, 2014, 10:34 AM
#3
Thread Starter
Hyperactive Member
Re: OleDB Error - Semi-Complex 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?
-
Oct 16th, 2014, 10:47 AM
#4
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
-
Oct 16th, 2014, 10:52 AM
#5
Thread Starter
Hyperactive Member
Re: OleDB Error - Semi-Complex 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) = ?) 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
Last edited by thebuffalo; Oct 16th, 2014 at 10:57 AM.
-
Oct 16th, 2014, 11:05 AM
#6
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
-
Oct 16th, 2014, 11:12 AM
#7
Thread Starter
Hyperactive Member
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:
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
-
Oct 16th, 2014, 11:19 AM
#8
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
-
Oct 16th, 2014, 11:31 AM
#9
Thread Starter
Hyperactive Member
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:
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|