How i can get max value with a condition in within datatable VB.NT

Question

I have datatable named (Dt) Has 2 column ("Type","value"). How i can get max value where tybe = textbox1.text ____ in (datatable)ADO.net not SQL server Thank you

Answer 1

Ok, so lets assume two buttons.

First button to load the data table form SQL server (that you suggest/note you already have the data table).

next, we have a text box for user to enter the column name.

And then a button get max, which will use the text box with column name.

After that, we have a text box to show the results.

So, we have this code:

Dim rstData As New DataTable

Private Sub cmdLoadData_Click(sender As Object, e As EventArgs) Handles cmdLoadData.Click

    Using conn As New SqlConnection(My.Settings.TEST4)

        Dim strSQL As String =
            "SELECT * FROM tblhotelsA ORDER BY HotelName"

        Using cmdSQL = New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)

            MsgBox("Data loaded", MsgBoxStyle.Information)
        End Using

    End Using

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    If rstData.Rows.Count > 0 Then

        ' find max colum based on colum name in 
        Dim MyResult As Object

        MyResult = rstData.Compute($"MAX({txtColumn.Text})", "")
        Debug.Print(MyResult.ToString())
        txtMax.Text = $"Max value for colum {txtColumn.Text} = {MyResult.ToString()}
                        -- colum data type = {MyResult.GetType.ToString()}"


    End If

End Sub

and the results are now this:

Edit: User wants to build a sql query to get the max value

Ok, so the question is NOT that we have a data table (dt) in code, and we wish to pull/get/find the max value from that vb.net dt.

our question is that we want to query the database for a given column, and PUT THE RESULTS into that vb.net data table.

So, we want to QUERY SQL server to get the one result.

So, a very different goal.

This would be a "more common" question, and thus amounts to a plain jane SQL query.

this also of course will perform MUCH faster, since we don't pull nor have that vb.net "dt" or datatable, but we are to put the results of the sql query into that dt.

So, our form can look the same, but now our code for the "Get max" button would be this:

We do NOT require the first button to "load data" anymore.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Using conn As New SqlConnection(My.Settings.TEST4)

        Dim strSQL As String =
            $"SELECT MAX(@P1) FROM tblhotelsA"

        Using cmdSQL = New SqlCommand(strSQL, conn)
            conn.Open()
            cmdSQL.Parameters.Add("@P1", SqlDbType.NVarChar).Value = txtColumn.Text
            rstData.Load(cmdSQL.ExecuteReader)
            txtMax.Text = $"Max value for colum {txtColumn.Text} = {rstData.Rows(0)(0).ToString()}"

        End Using
    End Using

Now, say we want to "ask" the user for a given city, and then return the max found age for that user supplied city.

Then this:

    Using conn As New SqlConnection(My.Settings.TEST4)

        Dim strSQL As String =
            $"SELECT MAX(Age) FROM tblhotelsA WHERE City = @City"

        Using cmdSQL = New SqlCommand(strSQL, conn)
            conn.Open()
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = txtWhatCity.Text
            rstData.Load(cmdSQL.ExecuteReader)
            txtMax.Text = $"Max age found for city = {txtcity.Text} = {rstData.Rows(0)(0).ToString()}"

        End Using
    End Using