Getting sum in asp.net page

[David C]

I have the following code in a asp.net web page that should always return a
single value (sum). Is there a better way to do this so I just get the
result and not have to check for HasRows, etc.? Also, I am thinking of
making this a stored proc when we go into production. If so, would the
"read" syntax be the same? Thanks.

David

'Get cost of parts sold this month for jobs completed
strSQL = "SELECT SUM(PartsCost) As TotalPartsCost" & _
" FROM dbo.vw_RORatesByPC" & _
" WHERE YEAR(RepairCompleteDate) = " & intYear.ToString & _
" AND MONTH(RepairCompleteDate) = " & intMonth.ToString
cmdSelect = New SqlCommand(strSQL, conData)
dtr = cmdSelect.ExecuteReader()
If dtr.HasRows Then
While dtr.Read()
If Not IsDBNull(dtr("TotalPartsCost")) Then
dblTotalCost = dtr("TotalPartsCost")
End If
Exit While
End While
End If
dtr.Close()

 

[Mark Rae [MVP]]

I have the following code in a asp.net web page that should always return a
single value (sum). Is there a better way to do this so I just get the
result and not have to check for HasRows, etc.?

Firstly, there should be no data reason to check for HasRows, since the SQL
would return zero...

Anyway:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(vs.80).aspx

 

[David C]

Thank you.

David

Firstly, there should be no data reason to check for HasRows, since the
SQL would return zero...

Anyway:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(vs.80).aspx

 

[Sam]

If you expect just a single value return, use ExecuteScalar instead of
ExecuteReader

 

[bruce barker]

dblTotalCost = cmdSelect.ExecuteScaler();

note: this will throw an error if no rows.

-- bruce (sqlwork.com)

 

[David C]

Yes, I found that out. How do you get around that?

David

 

[Mark Rae [MVP]]

Yes, I found that out. How do you get around that?

SELECT
ISNULL(SUM(MyField),0) AS SumOfValues
FROM
MyTable
WHERE
MyOtherField > 20