Select max value in a database.

[Waldis_1]

Hi.

I want to select the max value in a column (I want to increment the numbers using code instead of autonumber) I have tried some samples from the internet but it has been useless. Does anyone of you have a working code to share with me?

Thanks in advanced.

[Santanu.Das]

How for did you go? Show your efforts and tell about problems.

[Oxiegen]

Simple SQL query: SELECT MAX(<column name>) FROM <table>
The MAX() function works with both numbers and strings.
http://msdn.microsoft.com/en-us/library/ms187751.aspx

[Waldis_1]

I am using OledConnection not SQL Server. What I really want is to select the maximum value from a column, assign it to a variable and add 1 to that variable and then assign that value to the next row in INSERT INTO Statement (I'm ok with that part).

I need an already tested and working code to do the process described above (select, assign, add), I have been stuck for several days because of that.

Thanks in advance.

[Oxiegen]

No matter what the database backend is and no matter what type of database connection you use, you can still throw SQL commands at it and it will return the data.

Dim value As Integer
Dim con As New OleDbConnection(<connectionstring>)
Dim com As OleDbCommand

Private Sub ReadValue()
    com = New OleDbCommand("SELECT MAX(<column>) FROM <table>")

    con.Open()
    Dim reader As OleDbDataReader = com.ExecuteReader()

    If reader.HasRows Then
        reader.Read()
        value = reader(0)
    End If
    reader.Close() '<-- Note! Do NOT use con.Close() here.
End Sub

Private Sub AddAndInsert()
    value += 1

    com = New OleDbCommand("INSERT INTO <table> (<column>) VALUES (" & value & ")")

    con.Open()
    com.ExecuteNonQuery()
    con.Close()
End Sub

[Waldis_1]

What's wrong with this code?

Dim cmd As OleDb.OleDbCommand
                Dim archivo As String
                archivo = Application.StartupPath
                Dim path As String
                path = archivo & "\prestamos.accdb"

                Dim connection = " Provider=Microsoft.ace.OLEDB.12.0;" & _
                           " Data Source= " & path
                Dim conn As New OleDb.OleDbConnection(connection)
                conn.Open()


                'Código para determinar el último código registrado para los préstamos
                Dim str As String
                Dim empid As Integer
                Dim newNumber As Integer
                str = "SELECT MAX(codigoprestamo) AS MAXIMUM FROM tblprestamosinteres"
                Dim cmd2 As OleDbCommand = New OleDbCommand(str, conn)
                Dim dr As OleDbDataReader
                dr = cmd2.ExecuteReader
                If dr.HasRows Then
                    While dr.Read()
                        If empid = IsDBNull(dr("MAXIMUM")) Then
                            newNumber = CInt(Val(empid)) + 1
                        End If
                        If newNumber = 0 Then
                            newNumber = 1
                            empid = CStr(newNumber)
                        Else
                            newNumber = newNumber + 1
                            empid = CStr(newNumber)
                        End If
                    End While
                End If
                Me.txtmaxvalue.Text = empid
                'Fin Código
                conn.Close()

[Oxiegen]

Try this:

If dr.HasRows Then
    dr.Read()
    If IsDBNull(dr("MAXIMUM")) Then
        empid = 1
    Else
        empid = CInt(dr("MAXIMUM")) + 1
    End If
Else
    empid = 1
End If
dr.Close()

Me.txtmaxvalue.Text = empid

That's all you need.
Because the SQL string will only return ONE record, you don't have to use a While loop. Just do a simple one-liner dr.Read().

[Santanu.Das]

Agreed with Oxiegen. This is the correct one.

[Waldis_1]

It works!!!, thanks a lot.

[necrovore]

It seems like you are trying to generate an ID, if i am guessing right. In that case wouldn't it be better if you used the Auto_Increment feature of the DBMS?

[Santanu.Das]

My point of view this is not a correct decision to use Autoincrement. ID isn't always numerical type.