Semi-complex Query In Vb .net - Topic

Member

Posts: 15,717

Joined: Aug 20 2007

Gold: 481.00

Oct 16 2014 08:55am

So my query works in access but not when i run it through my application

I get this error consistently, no matter how much i change things around "The Microsoft Office Access database engine does not recognize '@user' as a valid field name or expression."

This is where I run the query

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

This is where i feed the parameters/function values

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

ty m8s for any help

not used to running queries like this in my applications, but it needs to be done this time

This post was edited by t9x on Oct 16 2014 08:56am

carteblanche

Member

Posts: 32,925

Joined: Jul 23 2006

Gold: 3,804.50

Oct 16 2014 05:21pm

Quote

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:
SELECT * FROM Customers WHERE CustomerID = ?

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

have you tried that?

t9x

Member

Posts: 15,717

Joined: Aug 20 2007

Gold: 481.00

Oct 17 2014 08:37am

Quote (carteblanche @ Oct 16 2014 07:21pm)

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

have you tried that?

Yeah I actually fixed the problem, for all of my other access queries I have used named parameters and it worked, but not this one

Now my problem is actually filling the datatable, I guess they don't like Pivot queries very much

My problem now is still the same, but for a different reason, the query works correctly and the parameters pass through, but the data is not being loaded into the DataTable

So basically my next problem is a lot more difficult

This is the new function

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 = vbNullString
cmdtext = "TRANSFORM Count(Log.ControlNumber) AS CountOfCtrl "
cmdtext = cmdtext + "SELECT 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.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) // Commented out to test other methods

'Test Area
Do While rdr.Read
Dim dr As DataRow = dt.NewRow
For i = 0 To rdr.FieldCount - 1
dr.Item(i) = rdr.Item(i)
Next
dt.Rows.Add(dr)
Loop
'End Test Area

conn.Close()
Return dt
End Using
End Function

This has helped me locate the problem, the DoWhile never initiates, it gets called, but the reader never reads.

When I use dt.Load, I still get the column names, but no Pivot columns, which should be 0 (12:00 AM), 1 (1:00 AM), 2 (2:00 AM), etc..

So I think the problem is the datareader

I am considering loading the data into an xcel file and displaying the excel file, but I really do not want to do this

Go Back To Programming & Development Topic List