|
| |
|
|
karthik
Posts: 41
Joined: 8/16/2005
Status: offline
 |
CROSS tab query using asp and sql - 4/12/2007 14:09:44
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
|
|
|
|
BeTheBall
Posts: 6380
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: CROSS tab query using asp and sql - 4/12/2007 14:52:14
There shouldn't be quotes around the alias name and I wouldn't use spaces in the alias name either:
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"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
karthik
Posts: 41
Joined: 8/16/2005
Status: offline
|
RE: CROSS tab query using asp and sql - 4/12/2007 15:10:22
Thanks very much.
I still get the same error.
i use the following code
strSQL="TRANSFORM Count(order_no) AS CountOforderno SELECT Orders.duedate, status, Count (order_no) AS TotalOforderno FROM dbo_Orders WHERE (((status)='I'))GROUP BY duedate, status PIVOT country"
this is the error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'order_no'.
order_no is the field in the database.
plz help
| |
|
|
|
karthik
Posts: 41
Joined: 8/16/2005
Status: offline
|
RE: CROSS tab query using asp and sql - 4/13/2007 14:30:09
Hi,
can u plz help me on this error.
itz working fine when i use access and asp
but i get a error when i use SQL and asp
plz
| |
|
|
|
BeTheBall
Posts: 6380
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: CROSS tab query using asp and sql - 4/13/2007 14:35:17
I think you are facing a pretty steep learning curve to do it in SQL. Personally, I have never done so. Here is an article that talks about it:
http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
karthik
Posts: 41
Joined: 8/16/2005
Status: offline
|
RE: CROSS tab query using asp and sql - 4/13/2007 15:24:06
Thanks.
As an alternative
I have linked the access to SQL server thru link tables in access.
but when try to access the MS access linked table the error i get is connection failed.The code works fine when i make the table offline.
any suggestions plz
| |
|
|
|
BeTheBall
Posts: 6380
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: CROSS tab query using asp and sql - 4/13/2007 15:26:54
I have no good news here. I have never successfully linked an asp page to a linked table.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
