How to execute a Transform SQL Query?

[Hailstorm]

I try to use a data adapter to fill a dataset with a transform sql query
from ms access database.
The query string works at ms access's query area but when I run the asp.net
application, this error occures : "There is a syntax error near TRANSFORM
...." (error is in Turkish language so maybe this is not the exact
translation).


Isn't it possible to run TRANSFORM queries at ADO.Net? My querystring is :


strQuery= "TRANSFORM iif(Count(records.isPaid) is null,
0,Count(records.isPaid)) AS PaymentCount"
strQuery &= " SELECT records.paymentGroupID, records.explanation,
Count(records.paymentGroupID) AS TotalPaymentCount, MAX(paymentDate) AS
LastPaymentDate"
strQuery &= " FROM(records)"
strQuery &= " WHERE(records.paymentGroupID <> 0)"
strQuery &= " GROUP BY records.paymentGroupID, records.explanation"
strQuery &= " PIVOT records.isPaid;"

 

[Val Mazur]

Hi,

It should work in a case if Jet OLEDB provider recognized TRANSFORM as a
valid SQL syntax. I have never tried to do this from the front end, but I
think it will work. If not, then you could store your query as a stored
procedure in a Access database and call this SP. It will work