CODEWITHSUNDEEP
vb.netms-access
Thanzeem
Thanzeem

Reputation: 133

Largest value in a column of ms access and display in a text box VB.NET

How to find the largest value in a column of a access table. and displayed in a text box. I give 101 as the default value of the column and the table is empty. I try like this.. But its not working. Code is given below

Dim empid As Integer
empid=101
TXTEMPID.Text=empid
getConnect()
    Dim strSQL As String = "SELECT MAX(EMP_ID) FROM EMPLOYEE "
    Dim cmd As OleDb.OleDbCommand
    Dim Reader As OleDb.OleDbDataReader
    cmd = New OleDb.OleDbCommand(strSQL, Conn)
    Try
        Conn.Open()
        Reader = cmd.ExecuteReader()
        If Reader.Read Then
            empid = CInt(Reader("EMP_ID"))
        End If
        MessageBox.Show(empid)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        Conn.Close()
    End Try
    TXTEMPID.Text = empid + 1

Upvotes: 0

Views: 7493

Answers (3)

Thanzeem
Thanzeem

Reputation: 133

I change my code like this given below.. Its working coooool......

 getConnect()
    Conn.Open() 
    Dim str As String
    Dim newNumber As Integer
    str = "SELECT MAX(EMP_ID) AS MAXIMUM FROM EMPLOYEE"
    Dim cmd As OleDbCommand = New OleDbCommand(str, Conn)
    Dim dr As OleDbDataReader
    dr = cmd.ExecuteReader
    If dr.HasRows Then
        While dr.Read()
            TXTEMPID.Text = dr("MAXIMUM").ToString
            newNumber = CInt(Val(TXTEMPID.Text))
            If newNumber = 0 Then
                newNumber = 101
                TXTEMPID.Text = CStr(newNumber)
            Else
                newNumber = newNumber + 1
                TXTEMPID.Text = CStr(newNumber)
            End If
        End While
    End If
    Conn.Close()

Thank you all for replay and comment my question

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123409

If the table is empty then there is no "largest value" because the table contains no values at all.

Edit

Ah, okay. It sounds like you were tripping over the fact that, for an empty table, expressions like DMax("EMP_ID","YourTable") will return Null, and Null + 1 will return Null, so how do we get started? You could try something like...

Me.txtEMP_ID.Value = Nz(DMax("EMP_ID","YourTable"), 100) + 1

...in the Form Load event, although I should mention that this type of approach can cause problems is your database is (or will ever become) multi-user.

Upvotes: 1

Shafqat Masood
Shafqat Masood

Reputation: 2570

here is the query 

Select Top 1 MAX(col 2), col1 from Table1 group by col1

to get the results you have to perform 

Dim cnnOLEDB As New OleDbConnection
Dim cmdOLEDB As New OleDbCommand
Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &    System.Environment.CurrentDirectory & "\URDataBaseName.mdb"
cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()

cmdOLEDB.CommandText ="Select Top 1 MAX(col 2), col1 from Table1 group by col1"
cmdOLEDB.Connection = cnnOLEDB

txtbox.text = cmdOLEDB.ExecuteScalar().ToString()

ExecuteScalar Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored

Upvotes: 0

Related Questions