CODEWITHSUNDEEP
asp.netsqlsql-server
Allan
Allan

Reputation: 1

problem with sql query string

I'm having a problem displaying a sum total, getting the error 

Line 1: Incorrect syntax near '('.

The query I am using is as follows;

    SELECT     
SUM(a_count) AS total

FROM
    (
    SELECT 
        TOP (100) PERCENT CONVERT(varchar, dt_created, 106) AS dt_created, 
        COUNT(*) AS a_count        
    FROM
        fulf AS a
    WHERE
        (source = '10wnt47') 
        AND (dt_created >= '01 jan 2011') 
        AND (dt_created <= '11 apr 2011')
    GROUP BY CONVERT(varchar, dt_created, 106)
    ) 
AS b

The strange thing is that this query works perfectly when simply quering the database, but when trying to use it within an aspx page it seems to fall over. I am already using the query (without the sum being generated) and it works fine. It's probably something obvious that I am missing but I can't see it...

The stack trace is as follows...

[SqlException (0x80131904): Line 1: Incorrect syntax near '('.
Incorrect syntax near the keyword 'AS'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteReader() +89
   _Default.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\amayberry\My Documents\Visual Studio 2008\WebSites\chart_test\iframe2.aspx.vb:95
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

The code im using to build and execute the query is as follows.

Using myConnection As New SqlConnection
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("VisitScotlandConnectionString").ConnectionString

            Dim mySelect As New SqlCommand
            mySelect.Connection = myConnection
            mySelect.CommandText = "SELECT SUM(a_count) AS total FROM (SELECT TOP (100) PERCENT CONVERT(varchar, dt_created, 106) AS dt_created, COUNT(*) AS a_count FROM fulf AS a WHERE (source = '10wnt47') AND (dt_created >= '01 jan 2011') AND (dt_created <= '11 apr 2011') GROUP BY CONVERT(varchar, dt_created, 106)) AS b"
            myConnection.Open()
            Dim myReader As SqlDataReader = mySelect.ExecuteReader()

            If myReader.Read() Then
                total = myReader("total").ToString()
                Response.Write(total)
            End If

            myReader.Close()
            myConnection.Close()
        End Using

It's fairly straightforward and is fired when a button on a page is clicked (after getting variables from the user (which I have replaced for illustration purposes)

Upvotes: 0

Views: 701

Answers (3)

Allan
Allan

Reputation: 1

Well i ended up scrapping that and re-writing the code and it seemed to work fine with this version of code:

SELECT 
    COUNT(*) AS Total FROM dbo.fulf 
WHERE
        (source = '" & vSource & "') 
    AND 
        (dt_created >= '" & Format(vStartDate, "dd MMM yyyy") & "') 
    AND 
        (dt_created <= '" & Format(vEndDate, "dd MMM yyyy") & "') 
ORDER BY 
    Total DESC

Upvotes: 0

Zach Green
Zach Green

Reputation: 3460

I believe you need to remove the 'AS' clauses that you put after the table names. So they should be (note, I replaced some of your variable names):

SELECT SUM(TotalCount) AS total 
FROM  
    (
        SELECT 
            TOP (100) PERCENT CONVERT(varchar, dt_created, 106) AS dt_created, 
            cnt AS TotalCount 
        FROM  
            (
                SELECT 
                    CONVERT(varchar, dt_created, 106) AS dt_created, 
                    COUNT(*) AS cnt 
                FROM 
                    dbo.fulf a  
                WHERE 
                    (source = 'thing') 
                    AND (dt_created >= 'date') 
                    AND (dt_created <= 'date')  
                GROUP BY 
                    CONVERT(varchar, dt_created, 106)
            ) a_1 
        ORDER BY 
            CONVERT(datetime, dt_created, 106)
    ) b

Upvotes: 1

Randy
Randy

Reputation: 16677

the final order by seems to be in a funny place. it is probably not required.

Upvotes: 1

Related Questions