[RESOLVED] Obtain the MAX number in SQL ?

[VBStewart]

This SQL statement should select the maximum number in the Auto Numbering column ' JOB_no' in my SQL database.

strSQL = "SELECT MAX(JOB_no)"
        strSQL = strSQL & " FROM JOBS"
        strSQL = strSQL & " WHERE myusername = '" & Nowuser & " '"
 
        Call SQL_SELECTconnection()

How do I actually 'use' the result in VB.net 2005 ?
EG MSGBOX (the result of my query?)


I tried - MsgBox(mydataset.Tables(0).Rows(0)("job_No"))
(Which works for a normal select statement), but errors when using 'MAX' - stating job_no is not a valid column of table 'table'.

[jmcilhinney]

It isn't. Your query is returning a single value, not a column. If you use an aggregate function to get a value you have to specify its name if you want to use it as a column, e.g.

SELECT Max(JobNo) AS MaxJobNo FROM Jobs

Having said that, if you're just getting a single value from a database then there shouldn't be any DataSets or DataTables involved:

Dim myConnection As New SqlConnection("connection string here")
Dim myCommand As New SqlCommand("SELECT MAX(job_no) FROM Jobs WHERE myusername = @myusername", myConnection)
 
myCommand.Parameters.AddWithValue("@myusername", Nowuser)
myConnection.Open()
 
Dim result As Object = myCommand.ExecuteScalar()
 
If result Is Nothing Then
    MessageBox.Show("No jobs for specified user.")
Else
    Dim maxJobNo As Integer = CInt(result)
 
    MessageBox.Show("Max job no: " & maxJobNo)
End If
 
myConnection.Close()

[VBStewart]

Great - Thanks