I have created crosstab query in access and pasted the same code into asp.The code is working fine when i use access but does not work when i use SQL.Following is the code
<%@ LANGUAGE="VBSCRIPT" %>
<%
Dim objConn
Dim objRS
Dim strSQL
Dim strConnection
Dim i
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
const c_databaseType = "SQL"
const c_odbcBridge = "E2E"
const c_dbUID = "amse2e"
const c_dbPwd = "amse2e"
objConn.Open "DSN=" & c_odbcBridge, c_dbUID, c_dbPwd
strSQL ="TRANSFORM Count(order_no) AS 'CountOforder_no' SELECT Orders.duedate, status, Count (order_no) AS 'Total Of order_no'FROM dbo_Orders WHERE (((status)='I'))GROUP BY duedate, status PIVOT country"
set objRS = objConn.Execute (strSQL)
if (objRS.BOF and objRS.EOF) then
response.write "No records found"
response.end
End if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
<%
Response.Write "<TR BGCOLOR=""#CCCCCC"">"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TH><FONT FACE=""ARIAL"" SIZE=""2"">" & objRS.Fields(i).Name & "</FONT></TH>"
Next
Response.write "</TR>"
' -- Now output the contents of the Recordset
objRS.MoveFirst
Do While Not objRS.EOF
' -- output the contents
Response.Write "<TR>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & objRS.Fields(i) & "</FONT></TD>"
Next
Response.write "</TR>"
' -- move to the next record
objRS.MoveNext
Loop
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>
I get the following error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'order_no'.
please help me
plz
dBforums
>
CROSS tab query using asp and sql
04-13-07, 17:09
CROSS tab query using asp and sql
Linear Mode
