Total count of selected columns

Question

I am trying to get a total count of 43 out of 45 columns. In the table they are defined as an integer.

   Dim connStr, cmdStr As String
   connStr = ConfigurationManager.ConnectionStrings("astsql").ConnectionString
   cmdStr = "SELECT (SUM([index]),SUM([01]),SUM([02]),SUM([03]),SUM([04]),SUM([05]),SUM([06]),SUM([07]),SUM([08]),SUM([09]),SUM([10]),SUM([11]),SUM([12]),SUM([13]),SUM([14]),SUM([15]),SUM([16]),SUM([17]),SUM([18]),SUM([19]),SUM([20]),SUM([aa]),SUM([ab]),SUM([ac]),SUM([ad]),SUM([ae]),SUM([af]),SUM([ag]),SUM([ah]),SUM([ai]),SUM([aj]),SUM([ak]),SUM([al]),SUM([am]),SUM([an]),SUM([ao]),SUM([ap]),SUM([aq]),SUM([ar]),SUM([as]),SUM([at]),SUM([grsc]),SUM([grkr]),SUM([grkn])) FROM [count];"
    Dim totalcount As Integer = 0
    Dim n As Integer = 0
    Try
        Using conn As New SqlConnection(connStr)
            Using cmd As New SqlCommand(cmdStr, conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                Using myreader = cmd.ExecuteReader()
                    While myreader.Read()
                        totalcount = totalcount + Convert.ToInt32(myreader(n))
                        n = n + 1
                    End While
                End Using
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception
        Label5.Text = ex.ToString()
    End Try

Here is my error code:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion

Sergey Kalinichenko Sergey Kalinichenko · Accepted Answer · 2014-12-06T18:00:09

You should not enclose the SUMs in parentheses unless you add them up. Replacing commas with pluses will fix the problem:

cmdStr = "SELECT (SUM([index])+SUM([01])+SUM([02])+SUM([03])+SUM([04])...) FROM [count];"

Now your query returns a single number, so you can use ExecuteScalar instead of ExecuteReader:

conn.Open()
totalcount = Convert.ToInt32(cmd.ExecuteScalar())
conn.Close()

Total count of selected columns

1 Answers