locked
Using SUM based on DDL RRS feed

  • Question

  • User-520535568 posted

    Hi,I have a Mileage table which records Mileage, RecMileage, and Name.  The query below SUMS Mileage and RecMileage fields from Mileage table - the result is the total of all miles for all users. the query looks like this:

    New SqlCommand("Select SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage")

    I also have a Dropdown list (DDL1) which is used to select Names. What I would like to do is have the above query show Total Mileage for  the Person selected on DDL1.selectedvalue. Something like this: (which doesn't work)

    New SqlCommand("Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage WHERE [Name]=DDL.SelectedValue")

    Thanks in advance

    Monday, July 22, 2019 11:07 AM

Answers

  • User839733648 posted

    Hi wilsation,

    As the above replies mentioned, you just have to put the code into the DropDownList1_SelectedIndexChanged event and set autopostback as true.

    I've made a simple demo and hope this will be helpful to you.

    .aspx

        <form id="form1" runat="server">
            <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
                <asp:ListItem>Jack</asp:ListItem>
                <asp:ListItem>Linda</asp:ListItem>
                <asp:ListItem>Mike</asp:ListItem>
                <asp:ListItem>John</asp:ListItem>
            </asp:DropDownList>
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </form>

    Code behind.

    Class SurroundingClass
        Private constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    
        Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim con As SqlConnection = New SqlConnection(constr)
            Dim myquery As String = "SELECT Eid, Ename , age , sex FROM tb_info WHERE [Ename]=@Ename"
            Dim cmd As SqlCommand = New SqlCommand(myquery, con)
            con.Open()
            cmd.Parameters.Add(New SqlParameter("Ename", DropDownList1.SelectedValue))
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
    
            While sdr.Read()
                Label1.Text = sdr("age").ToString()
            End While
        End Sub
    End Class

    how it works:

    Reference: https://forums.asp.net/t/1372992.aspx?Get+selection+from+DropDownList+and+pass+it+to+SQL+query

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 9:41 AM
  • User475983607 posted

    so what goes in the event handler on the page?

    The SUM logic.

    Private constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    
    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    	    Dim con As SqlConnection = New SqlConnection(constr)
            Dim myquery As String = "Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage where [Name] = @Name"
            Dim cmd As SqlCommand = New SqlCommand(myquery, con)
            con.Open()
            cmd.Parameters.Add(New SqlParameter("[Name]", DDL1.SelectedValue))
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
    
            While sdr.Read()
                TotalLabel.Text = sdr("TotalMileageTot").ToString()
            End While
    End Sub
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 2:05 PM

All replies

  • User-520535568 posted

    Thanks for the reply but I'm afraid the article doesn't make a lot of sense to me, I'm sure it makes perfect sense to an experienced programmer but my VB knowledge isn't great so I'm afraid I'll need a bit more guidance.

    I guess from this article that I can't just add and extra parameter to my query....

    Monday, July 22, 2019 12:23 PM
  • User475983607 posted

    A SQL parameter (variable) starts with an @; @Name.

    New SqlCommand("Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage WHERE [Name]=@Name)

    Use the Parameter property of the SqlCommand to map the parameter name to a value.   This is what the linked reference document is illustrating.

    SqlCommand.Parameters.Add("@Name", SqlDbType.VarChar)
    SqlCommand.Parameters["@Name"].Value = DDL.SelectedValue

    The rest of your code stays the same.  All you are doing is adding a parameter.

    Monday, July 22, 2019 12:49 PM
  • User-520535568 posted

    Ok, so now I understand that I need to use a parameter to get the Name but I think I must be inserting your code in the wrong place. Does it go in the Page_Load Sub or does it need a separate sub?

    Here is my code as it is without the Parameters

       Protected Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs)
    
            If IsPostBack Then
    
                'Define Data Objects for Total Miles
                Dim ConnTot As SqlConnection
                Dim CommTot As SqlCommand
                Dim ReaderTot As SqlDataReader
    
                'Initialise Connection
    
                ConnTot = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
                'Create Command
    
                CommTot = New SqlCommand("Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage WHERE [Name]=@Name")
    
    
                Try
                    'Open Connection
                    ConnTot.Open()
                    'Execute Command
    
                    ReaderTot = CommTot.ExecuteReader()
                    While ReaderTot.Read()
                        TotalLabel.Text &= ReaderTot.Item("TotalMileageTot")
                    End While
    
                    'Close Data Reader
                    ReaderTot.Close()
                Catch
                    'Display Error Message
                    Response.Write("Error Retrieving Data - Something is Broken")
                Finally
                    'Close Connection
                    ConnTot.Close()
                End Try
    End Sub

    Monday, July 22, 2019 2:44 PM
  • User475983607 posted

    Ok, so now I understand that I need to use a parameter to get the Name but I think I must be inserting your code in the wrong place. Does it go in the Page_Load Sub or does it need a separate sub?

    I don't know how your search page is designed. I assume since you are using a select input (dropdownlist), the filter logic would be in a button click handler or dropdownlist change even handler.

    Monday, July 22, 2019 3:01 PM
  • User753101303 posted

    You still need :

    CommTot.Parameters.AddWithValue("@Name",DDL.SelectedValue)

    which defines the value that should be used for the @Name parameters. You should have an exception about that (always tell which error happens when showing code that doesn't work as expected).

    Not directly related but I would suggest to just drop your current try/catch/finally.  try/finally with dispose can be replaced by https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement and you have better ways to handle errors (for now the hosting or dev team will be unable to even know that an exception happened).

    Monday, July 22, 2019 3:15 PM
  • User-520535568 posted

    Hi, the design is very simple - the page loads and the user selects their name from drop down (DDL1) which is set to Autopostback=True. When the page postsback the query runs to calculate total mileage.

    What I need to know is where to put the code you suggested, it looks simple enough but I just can't work it out. Do I create a new sub to handle it or does it go in my existing sub?

    Thanks

    Also, thanks to Patrice for comments.

    Tuesday, July 23, 2019 7:33 AM
  • User753101303 posted

    Yes, it should be part of the OnSelectedIndexChanged handler so that it happens when another value is selected. See sample code at  https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.dropdownlist?view=netframework-4.8#examples (inline scripting for this is basically the same for code behind).

    Tuesday, July 23, 2019 8:07 AM
  • User839733648 posted

    Hi wilsation,

    As the above replies mentioned, you just have to put the code into the DropDownList1_SelectedIndexChanged event and set autopostback as true.

    I've made a simple demo and hope this will be helpful to you.

    .aspx

        <form id="form1" runat="server">
            <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
                <asp:ListItem>Jack</asp:ListItem>
                <asp:ListItem>Linda</asp:ListItem>
                <asp:ListItem>Mike</asp:ListItem>
                <asp:ListItem>John</asp:ListItem>
            </asp:DropDownList>
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </form>

    Code behind.

    Class SurroundingClass
        Private constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    
        Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim con As SqlConnection = New SqlConnection(constr)
            Dim myquery As String = "SELECT Eid, Ename , age , sex FROM tb_info WHERE [Ename]=@Ename"
            Dim cmd As SqlCommand = New SqlCommand(myquery, con)
            con.Open()
            cmd.Parameters.Add(New SqlParameter("Ename", DropDownList1.SelectedValue))
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
    
            While sdr.Read()
                Label1.Text = sdr("age").ToString()
            End While
        End Sub
    End Class

    how it works:

    Reference: https://forums.asp.net/t/1372992.aspx?Get+selection+from+DropDownList+and+pass+it+to+SQL+query

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 9:41 AM
  • User-520535568 posted

    Hi Jenifer, thanks for your response, I've added this code to Code Behind (in file MyMiles.vb)

    Class SurroundingClass
        Private constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    
        Protected Sub DDL1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim con As SqlConnection = New SqlConnection(constr)
            Dim myquery As String = "Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage where [Name] = @Name"
            Dim cmd As SqlCommand = New SqlCommand(myquery, con)
            con.Open()
            cmd.Parameters.Add(New SqlParameter("[Name]", DDL1.SelectedValue))
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
    
            While sdr.Read()
                TotalLabel.Text = sdr("TotalMileageTot").ToString()
            End While
        End Sub
    End Class

    And added this code to my DropDown List (DDL1) in mMyMiles.aspx

    <asp:DropDownList ID="DDL1" runat="server" AutoPostBack="True" Width="236px" OnSelectedIndexChanged="DDL1_SelectedIndexChanged" >

    When page loads I get this error:

    DDL1_SelectedIndexChanged' is not a member of 'ASP.mymiles_aspx'

    Where am I going wrong?

    Tuesday, July 23, 2019 1:17 PM
  • User475983607 posted

    When page loads I get this error:

    DDL1_SelectedIndexChanged' is not a member of 'ASP.mymiles_aspx'

    Where am I going wrong?

    You forgot to add the event handler.

    Tuesday, July 23, 2019 1:29 PM
  • User-520535568 posted

    The event handler is in the Code Behind

     Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

    so what goes in the event handler on the page?

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) 

    ???????

    End Sub

    Tuesday, July 23, 2019 1:40 PM
  • User-520535568 posted

    also, I'm not sure the aspx page is reading the code behind file. This is how I am referencing it

    <%@ Page Title="My Miles" Language="VB" AutoEventWireup="true" CodeBehind="MyMiles.vb" %>

    Tuesday, July 23, 2019 2:03 PM
  • User475983607 posted

    so what goes in the event handler on the page?

    The SUM logic.

    Private constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    
    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    	    Dim con As SqlConnection = New SqlConnection(constr)
            Dim myquery As String = "Select [Name], SUM(RecMileage)+SUM(Mileage) AS TotalMileageTot From mileage where [Name] = @Name"
            Dim cmd As SqlCommand = New SqlCommand(myquery, con)
            con.Open()
            cmd.Parameters.Add(New SqlParameter("[Name]", DDL1.SelectedValue))
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
    
            While sdr.Read()
                TotalLabel.Text = sdr("TotalMileageTot").ToString()
            End While
    End Sub
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 2:05 PM
  • User-520535568 posted

    Thanks you all very much for your help, everything is working as it should.

    laughing

    Tuesday, July 23, 2019 2:46 PM