|
Subject:
|
Transform query
|
|
Posted By:
|
tulincim
|
Post Date:
|
9/12/2005 5:40:08 PM
|
Hello everyone,
Do you know if ASP supports Transform query?
Here is the my code:
strMonth = "TRANSFORM Count([PrimaryKey]) AS CountOfPrimaryKey SELECT (Format([Received Date],'MMM')) AS sMonth FROM [2005 Received Records] WHERE SUPPLIER = '" &Supp& "' GROUP BY (Year([Received Date])*12+Month([Received Date])-1), (Format([Received Date],'MMM')) PIVOT [SubLate]"
When I first run the same query, successfully return the expected data. After that the recordset eventually has began to come back empty all the time.
Any help would be appreciated...
Thanks,
|
|
Reply By:
|
arimakidd
|
Reply Date:
|
9/13/2005 5:11:12 PM
|
Some how I think your leaving out some of the code. Are you querying an Access or MYSQL Datastore? Usually when I get this kind of problem where the query works fine the first time and then returns an error or comes up empty its because instead of de-referencing my recordsets I just use the Resposne.End. So instead of:
vaObj.Close
Set vaObj = Nothing
I just use
Response.End
Give it a try, I don't have the technical explanation as to why this works with me now but will post it later.
|
|
Reply By:
|
tulincim
|
Reply Date:
|
10/4/2005 8:50:44 AM
|
Hi,
I am querying Access datastore. I used Response.End instead of
rsMonth.Close()
set rsMonth=Nothing
but it didn't work. This problem is making me crazy. I don't get any error, I just get empty recordset all the time. The query works fine in Access environment.
This is my code:
strMonth = "TRANSFORM Count([2005 Received Records].PrimaryKey) AS CountOfPrimaryKey SELECT (Format([Received Date],'mmm')) AS [Month] FROM [2005 Received Records] GROUP BY (Year([Received Date])*12+Month([Received Date])-1), (Format([Received Date],'mmm')) PIVOT [2005 Received Records].SubLate"
set rsMonth= Server.CreateObject("ADODB.Recordset")
rsMonth.Open strMonth, objConn
<tr>
<td class="TitleName"><font size=2><b>Month</b></font></td>
<td class="TitleName"><font size=2><b>Late#</b></font></td>
<td class="TitleName"><font size=2><b>Total#</b></font></td>
</tr>
<%Do While Not rsMonth.EOF%>
<tr>
<td><font size=2><center><b><%=rsMonth("Month")%></b></center></font></td>
<td><font size=2><center><b>><%=rsMonth("Late")%></b></center></font></td>
<td><font size=2><center><b>><%=rsMonth("On Time")%>
</tr>
<%rsMonth.MoveNext
Loop%>
rsMonth.Close()
set rsMonth=Nothing
Thank you for your help...
|
