SELECT MAX Statement

[brucelim80]

Does any one know how to excute a select max statement?

Please kindly provide me an example.
My code below cannot work.
It always show make sure that
1.the maximum index on a list is less than the list size
2. make sure data column names are correct (the column is correct")


Dim datacheck As OleDbDataReader = strsql1.ExecuteReader
If datacheck.HasRows Then
myConnects.Close()
myConnects.Open()
Dim reader As OleDbDataReader = strsql.ExecuteReader
If reader.HasRows Then
lastserialworkid = reader.Item("workid").ToString
lastserialworkid2 = CLng(lastserialworkid) + 1
myConnects.Close()
End If
Does anyone know to solve this problem?

Thank you.

 

[alander]

Dim sqlMax = "SELECT Max (databaseColumn) FROM [DataTable]"

Ur code that does not fit the purpose

Dim datacheck As OleDbDataReader = strsql1.ExecuteReader
If datacheck.HasRows Then'<- if the data table has rows
myConnects.Close() 'close connection..
myConnects.Open() 'open connection again? oO
Dim reader As OleDbDataReader = strsql.ExecuteReader ' a reader is declared
If reader.HasRows Then 'the reader juz returns values of ur query
lastserialworkid = reader.Item("workid").ToString 'u keep on over writing the value of this variable until the last record of the data table
lastserialworkid2 = CLng(lastserialworkid) + 1  'u keep on over writing the value of this variable until the last record of the data table
myConnects.Close()'u close ur connection
End If

Some help

Dim con As New OleDb.OleDbConnection(strConn) 'set connection
Dim cmdMax As IDbCommand = con.CreateCommand() 'create a new command
Dim sqlMax as String = "SELECT MAX(ID) FROM employees" 'Create Select max query
cmdMax.CommandText = sqlMax 'bind the command to execute the max query
con.Open() 'Open connection 
Dim nextId As long = cType((cmdMax.ExecuteScalar() + 1), Long)
'Execute query returning the max ID (which also means the last 1) + 1 
'and convert it to Long integer saving the next ID
con.Close() 'Close connection

What u are doing is reading values, as u are using executeReader, and i dun understand y u close ur connection and open again..

executeScalar is used for scalar querires, not executereader as reader only returns values from the data table

 

[cjard]

Does any one know how to excute a select max statement?

You seem to be asking two different questions!

SELECT MAX(counter) FROM a_table


[quote]
1.the  maximum index on a list is less than the list size
[/quote]
This isnt guaranteed either. If you have 10 rows, but delete the first half of them, you have 5 rows but the last row has id of 10, but the list size is 5. The id will never be less than the list size

[quote]
2. make sure data column names are correct (the column is correct")
[/quote]
I dont understand, sorry

 

[cjard]

Some help

I wouldnt actually ever, ever (never ever) select the ID into the client app, wait for the user to write some stuff against it, then save it back to the database.. it's the best way to guarantee that 2 users get the same ID, and conflict with details

ID should be calculated by the database, never by the client. If the database in use doesnt support autonumbers or sequences or stored functions/procedures it should be swapped for a database that does

Simplistically though, this is far better:

INSERT INTO table (id, a, b, c)
SELECT MAX(id) + 1, 'a value', 'b value', 'c value' FROM table

But again.. DONT USE IT if you have a DB that has autonumber or sequences!