problems with union query

[zerkat]

I am converting an access application to .net. We are keeping the date in access - I am not all that familiar with access but I am fumbling my way through it.

Any way, I have a query that I am using to populate a gridview in .net. Since gridviews will not display a blank row if there are no records I added a union to my stored query in access to select a blank row from the database. This worked when we did it in SQL but in access every time I add the union into my select I receive IErrorInfo.GetDescription failed with e_fail.

PARAMETERS intEmployeeKey Short, TaskDate DateTime;
SELECT TaskTime.Pkey, TaskTime.TaskDate, TaskTime.Hours, TaskTime.Notes, Task.TaskName
FROM Employee INNER JOIN (Task INNER JOIN ([Position] INNER JOIN (EmployeePosition INNER JOIN TaskTime ON EmployeePosition.Pkey=TaskTime.EmployeePositionKey) ON Position.Pkey=EmployeePosition.PositionKey) ON Task.Pkey=TaskTime.TaskKey) ON Employee.Pkey=EmployeePosition.Employee
WHERE (((Employee.Pkey)=[intEmployeeKey]) AND ((TaskTime.TaskDate)=[TaskDate]))

UNION

SELECT '', '', '', '', ''
FROM Employee INNER JOIN (Task INNER JOIN ([Position] INNER JOIN (EmployeePosition INNER JOIN TaskTime ON EmployeePosition.Pkey=TaskTime.EmployeePositionKey) ON Position.Pkey=EmployeePosition.PositionKey) ON Task.Pkey=TaskTime.TaskKey) ON Employee.Pkey=EmployeePosition.Employee
WHERE (((Employee.Pkey)=[intEmployeeKey]) AND ((TaskTime.TaskDate)=[TaskDate]));

The parameters will be coming from the .net app. I debugged the application and they are being set.

Is the above query not viable in access? They run fine separately.

 

[HarleyQuinn]

As a query in access this should work fine:

SELECT TaskTime.Pkey, TaskTime.TaskDate, TaskTime.Hours, TaskTime.Notes, Task.TaskName
FROM Employee INNER JOIN (Task INNER JOIN ([Position] INNER JOIN (EmployeePosition INNER JOIN TaskTime ON EmployeePosition.Pkey=TaskTime.EmployeePositionKey) ON Position.Pkey=EmployeePosition.PositionKey) ON Task.Pkey=TaskTime.TaskKey) ON Employee.Pkey=EmployeePosition.Employee
WHERE (((Employee.Pkey)=[intEmployeeKey]) AND ((TaskTime.TaskDate)=[TaskDate]))

UNION

SELECT '', '', '', '', ''
FROM Employee;

You should be prompted for the paramter values if you run it in Access.

How are you creating, passing the params and running the query in the .Net app?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[PHV]

I highly doubt that '' is a valid DateTime ...

 

[HarleyQuinn]

From my testing (as I also thought that), you'd be suprised...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[PHV]

you'd be suprised[/!]
ms-access keep surprise me ...
Note: look at my profile to better understand the above joke.

 

[zerkat]

It's seems like it is a problem with the Jet OleDb provider not liking the UNION. I can run the query fine in Access and I get prompted for my params and it comes back with results but when I run it through my .net app I get the error.

I am using Data Access Layers and Business Entity Layers to manage all my SQL statements. The function that I am using to call this query goes something like this:

Public Function FunctionA(ByVal intEmployeeKey As Integer, ByVal TaskDate As Date) As list(of classA)

Dim variableA As New List(Of classA)

Using conn As New OledbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringName").ConnectionString
Using com As New OledbCommand("qryName", conn)
com.Parameters.Add(New OledbParameter("intEmployeeKey", OledbType.Integer)).Value = intEmployeeKey
com.Paramaters.Add(New OledbParameter("TaskDate", OledbType.Date)).Value = TaskDate
com.Commandtype = Date.CommandType.StoredProcedure
conn.Open()
Using dr As OleDbReader = com.ExecuteReader()
While dr.Read()
Dim variableB As New classA
variableB = New classA(dr("columnA"), dr("columnB"), dr("columnC"))
variableA.Add(variableB)
End While
End using
End using
Return variableA
End Function

In my code behind, I call this function and pass in the variables I want to use - FunctionA(txtEmployee.text, txtDate.Text).

I have also tried using a query string instead of a stored query in the function and receive the same error. I did read that this error is common when there are duplicate column names, reserved words in the access db and I did change my column names so that they were unique and changed anything that might be a reserved word but still get the error. That's when I tried to remove the union from the query and then it worked. I have seen online in various places that there are issues with unions and the OLE DB but these were supposedly fixed with 4.0 which is what I am using.

 

[HarleyQuinn]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[HarleyQuinn]

zerkat - I'm sorry to say, that seems we've ruled out this being a problem with Access, it might be better to continue with your post in the ASP.NET forum with this thread referenced as to why you've bounced the question back there again. Sorry mate, but might be worth seeing if they know anything useful.

N.B - the smile in my previous post was at PHV's joke, not zerkat's code as it appears to be

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[zerkat]

that's ok. I do wonder how picky the OLE Db is with reserved words - do you think that the column names TaskDate or table Name TaskTime is not unique enough for it as Time and Date are reserved words - maybe it is still confused as to what I am asking.

 

[HarleyQuinn]

It should be fine with those names, it generally only causes a problem when the names are exactly the same as the reserved words.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[zerkat]

ok good. I am tired of changing all my column and table names then changing all names in queries - what a pain in a**

 

[PHV]

Why not simply add an empty List(Of classA) to variableA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zerkat]

Didn't know that was an option - I am having trouble visualizing that code. Do you have an example?

 

[zerkat]

actually - now that I think of it I am not sure if an empty list would work. since the gridview is a databound control and the columns are bound to columns from the datasource. How would you bind the empty list to the columns in the gridview?

The reason why I was using union is because we are selecting the same columns but in the second query returning them empty. We need those column names for the gridview.