Showing highest value from acdb column in form_load

[Sevyt]

Is there any way to show the highest value from a access db column in a textbox which you put in the load of a form?

Found something by searching around, but did not get it to work at all.
Maybe because i cannot open a connection in the load and later on again for filtering the database.

This is what i was trying
http://stackoverflow.com/questions/16317479/largest-value-in-a-column-of-ms-access-and-display-in-a-text-box-vb-net

This is my code for filtering

   Private Sub BtnZoek_Click(sender As Object, e As EventArgs) Handles BtnZoek.Click
        Dim StrNaam As String
        Dim StrVoornaam As String
        Dim StrFamNr As String
        Dim StrArchKast As String
        Dim StrOpmerking As String


        StrNaam = TxtNaam.Text
        StrVoornaam = TxtVoornaam.Text
        StrFamNr = TxtFamNr.Text
        StrArchKast = TxtArchiefKast.Text
        StrOpmerking = TxtOpmerking.Text

        Zoeken("C:\FB2.accdb", StrNaam, StrVoornaam, StrFamNr, StrArchKast, StrOpmerking)
    End Sub

    Public Sub Zoeken(ByVal DBase As String, ByVal Naam As String, ByVal Voornaam As String, ByVal FamNr As String, ByVal ArchKast As String, ByVal Opmerking As String)

        Dim sql, Z1, Z2, Z3, Z4, Z5 As String
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim command As OleDbCommand
        Dim ds As New DataSet
        Dim oledbadapter As New OleDbDataAdapter

        If Naam = "" Then
            Z1 = ""
        Else
            Z1 = "Naam LIKE '" & Naam & "%'"
        End If

        If Voornaam = "" Then
            Z2 = ""
        Else
            If Not Z1 = "" Then
                Z2 = " AND Voornaam LIKE '" & Voornaam & "%'"
            Else
                Z2 = "Voornaam LIKE '" & Voornaam & "%'"
            End If
        End If

        If FamNr = "" Then
            Z3 = ""
        Else
            If Not Z1 = "" Or Not Z2 = "" Then
                Z3 = " AND FamilieNummer LIKE '" & FamNr & "%'"
            Else
                Z3 = "FamilieNummer LIKE '" & FamNr & "%'"
            End If
        End If

        If ArchKast = "" Then
            Z4 = ""
        Else
            If Not Z1 = "" Or Not Z2 = "" Or Not Z3 = "" Then
                Z4 = " AND ArchiefKast LIKE '" & ArchKast & "%'"
            Else
                Z4 = "ArchiefKast LIKE '" & ArchKast & "%'"
            End If
        End If
        If Opmerking = "" Then
            Z5 = ""
        Else
            If Not Z1 = "" Or Not Z2 = "" Or Not Z3 = "" Or Not Z4 = "" Then
                Z5 = " And Opmerking Like '" & Opmerking & "%'"
            Else
                Z5 = "Opmerking Like '" & Opmerking & "%'"
            End If
        End If

        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & DBase
        sql = "Select * from FamBestand WHERE " & Z1 & Z2 & Z3 & Z4 & Z5
        connection = New OleDbConnection(connetionString)



        Try
            connection.Open()
            ds.Clear()

            command = New OleDbCommand(sql, connection)
            oledbadapter.SelectCommand = command
            oledbadapter.Fill(ds, "Create DataView")
            oledbadapter.Dispose()
            command.Dispose()
            connection.Close()
            FamBestandDataGridView.DataSource = ds.Tables(0).DefaultView
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

[Begginnerdev]

Something like:

"SELECT MAX(ColumnName) As MaxValue From Table "

Then pass that query to a datatable and pull into a textbox

Dim da As New SQLDataAdapter("SELECT MAX(ColumnName) As MaxValue From Table ",MySQLConnection)
Dim ds As New DataSet

da.fill(ds,"Max")

If Not IsNothing(ds.Tables("Max")) And ds.Tables("Max").Rows.Count > 0 Then
    TextBox1.Text = ds.Tables("Max").Rows(0)("MaxValue")
End If

[Sevyt]

Alright going to give that a try.
I assume you posted a mysql example? since you got
Dim da As New SQLDataAdapter("SELECT MAX(ColumnName) As MaxValue From Table ",MySQLConnection)

[Begginnerdev]

It would depend on the database you are using. (Looks like Access)

Data.SQLClient is microsoft's library for Access and SQLServer.

You can use OLEDB or one of numereous others.

[Sevyt]

Oh right now i got it, was not thinking straight.

[Sevyt]

Sorry for a late addition, was unable to work on it for a bit due to health issue's.

I am trying this

        Dim connetionString As String
        Dim connection As OleDbConnection
        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\ArchiefApp\FB.accdb"

        connection = New OleDbConnection(connetionString)

        Dim da As New OleDbDataAdapter("SELECT MAX(FamilieNummer) As MaxValue From FamBestand ", connection)
        Dim ds As New DataSet
        da.Fill(ds, "Max")
        If Not IsNothing(ds.Tables("FamilieNummer")) And ds.Tables("Max").Rows.Count > 0 Then
            TextBox1.Text = ds.Tables("Max").Rows(0)("MaxValue")
        End If

But i am not getting any returns or errors.
So i am guessing i did something wrong.

FamilieNummer = column name
FamBestand = table name

[Begginnerdev]

Change this:

ds.Tables("FamilieNummer")

To This:

ds.Tables("Max")

You are filling a table called "Max" but searching for a table called FamilieNummer.
Remember that the data set is in memory, not in database.

I am sorry to hear about health problems. I hope everything is okay!

[Sevyt]

Ah stupid mistake, cannot believe i missed that one.
Thanks for pointing that, working as it should now.

Health is getting better again, just takes a bit of time to heal up.

Thanks again for the help.