locked
Count and display total RRS feed

  • Question

  • User810354248 posted

    In my asp.net+vb+sql web page i have a gridview and want to show the total of the each row in bottom.

    when i query in sql it gets result

    SELECT sum(cast (Breakfast as int))as Breakfast,sum(cast (Lunch as int))as Lunch,sum(cast (Dinner as int))as Dinner,sum(cast (Extra as int))as Extra
    FROM Daily_Mess where rid='261'

    i tried and didn't get through so i tried to show in  a label with code behind

     If gank.Text <> "" Or gank.Text <> "Select" Then
                Dim con As New SqlConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings("BaijuEP").ConnectionString
                con.Open()
                Dim qstr As String
                qstr = "select count sum(cast (Breakfast as int)) where YEAR(fDate) = '" + yrtxt.Text + "' and  YEAR(fDate) = '" + yrtxt.Text + "' and  YEAR(fDate) = '" + yrtxt.Text + "' "
                Dim SQLadp As New SqlDataAdapter(qstr, con)         
                Dim dt As New DataSet
                dt.Clear()
                SQLadp.Fill(dt, "Daily_Mess")           
                gridtotal.Text = dt.Tables("Daily_Mess").Rows(0).Item(0)            
            End If

    it gives error as 

    Incorrect syntax near 'cast'.

    Thursday, July 27, 2017 1:36 AM

Answers

  • User-1509636757 posted

    qstr = "select count sum(cast (Breakfast as int)) where YEAR(fDate) = '" + yrtxt.Text + "' and YEAR(fDate) = '" + yrtxt.Text + "' and YEAR(fDate) = '" + yrtxt.Text + "' "

    I guess the query is incorrect. You are missing table name as well as count keyword is not required if you are simply looking for SUM. Please check below correction; that you may need some modification to do based on your code scenario:

    qstr = "SELECT SUM(CAST(ISNULL(Breakfast,0) AS INT)) FROM Daily_Mess WHERE YEAR(fDate) = '" + yrtxt.Text + "' AND  YEAR(fDate) = '" + yrtxt.Text + "' AND  YEAR(fDate) = '" + yrtxt.Text + "' "

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 27, 2017 8:08 AM