none
Using Aggregate functions in ADO.Net RRS feed

  • Question

  • Hello again,

    Now that I have gotten the basics of ADO.NET, I would like to use aggregate functions within ado.net string commands.

    How would I use aggregate functions to its simplest potential?

    For example:

       Public Sub BindData()
    
            Dim connection As New OleDb.OleDbConnection(strDBFileX)
            Dim dataadapter As New OleDb.OleDbDataAdapter("SELECT DISTINCT MAX(EmailAddresses.EmailAddressID)+1 as NewEMailAddressID , EmailAddresses.EmailAddress, EmailAddresses.AddressID FROM EmailAddresses WHERE EmailAddresses.AddressID=" & AddressIDTextBox.Text.ToString, connection)
            Dim ds As New DataSet()
            connection.Open()
            dataadapter.Fill(ds, "EmailAddresses")
            connection.Close()
            EmailAddressesDataGridView.DataSource = ds
            EmailAddressesDataGridView.DataMember = "EmailAddresses"
    
        End Sub

    Any user is welcome to improve this routine at their convenience.

    Note that An aggregate function has to be declared.

    What the user wants to accomplish is to add a new record by providing a Max aggregate function plus one; thus, a new record 

    or row would be created.

    Regards,

    JohnDBCTX


    jp

    Monday, July 30, 2018 4:26 AM

All replies

  • https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    It's where you should post.

    Monday, July 30, 2018 11:06 PM
  • Hi JohnDBCTX,

    According to your description, I create a demo and reproduce the issue on my side, it is a SQL statement issue, please refer to the following code.

    Public Sub BindData()
           
            Dim connection As New OleDb.OleDbConnection(strDBFileX)
            Dim dataadapter As New OleDb.OleDbDataAdapter("select * from (
                    SELECT Distinct  MAX(EmailAddresses.EmailAddressID)+1 as NewEMailAddressID, EmailAddresses.EmailAddress, EmailAddresses.AddressID FROM EmailAddresses group by EmailAddress,AddressID
                    )
                    where AddressID =" & AddressIDTextBox.Text.ToString, connection)
            Dim ds As New DataSet()
            connection.Open()
            dataadapter.Fill(ds, "EmailAddresses")
            connection.Close()
            EmailAddressesDataGridView.DataSource = ds
            EmailAddressesDataGridView.DataMember = "EmailAddresses"
    
        End Sub



    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 31, 2018 3:30 AM
    Moderator