Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Talking Unanswered: CROSS tab query using asp and sql

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    strSQL ="TRANSFORM Count(order_no) AS 'CountOforder_no' SELECT Orders.duedate, status, Count (order_no) AS 'Total Of order_no'FROM
    Is it perhaps the space in Count (order_no) ?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    3
    I get same error

    i cud not solve it plz help me

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How about changing Count(Order_No) to Count(*) ?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    3
    I have used Count(*)


    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '*'.

    this is the error i get

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •