European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MySql :: What does the Max("Field") returns from a database if it is empty?
By: thas	Date: 07/12/2002 00:00:00	Points: 100	Status: Answered Quality : Excellent
I have a question regarding the select command in sql. I need to find the maximum value in a colum. Please look at the code below. I am having and Employee database (EmployeeDB). I need to find the largest employee ID given in the database. this code below gives me the nextID that I could put in the EmployeeDB. It works if the Database has at least one row. Its not working if the database is empty, i.e attempting to saving the first Employee ID. I just want to know, what does the RecordCount would be if the database is empty. When it is empty my code is still going to the If Not tmp.RecordCount = 0 Then block. I am wondering why. if the database is empty, I just want to give the starting ID as 1. If anyone knows the solution to this please let me know. Thanks I didn't put the code that is saving the employee ID into the database as it is working properly. ////Code Set tmp = sdbase1.OpenRecordset("select max(EmployeeID) as maxEmployeeID from EmployeeDB") If Not tmp.RecordCount = 0 Then Dim NextID As Integer NextID = tmp.Fields("maxEmplID") + 1 txtEmployeeNo.Text = NextID txtEmployeeNo.Enabled = False Else txtEmployeeNo.Text = 1 txtEmployeeNo.Enabled = False End If
By: dilligaffuq	Date: 07/12/2002 15:48:00		Type : Answer
Well, My test with that query gets a recordcount of 1 with no records. Not sure why. But, you could do this: DMax(Expr, Domain, Criteria) NextID = Nz(DMax("[EmployeeID]", "EmployeeDB"),0) + 1 That takes the max EmployeeID and adds 1 to it. The Nz function says if the MaxID is null, use 0 instead. dill

By: dilligaffuq	Date: 07/12/2002 15:51:00		Type : Comment
I realized I may have answered an incorrect question realizing that I am in the MySQL section. Then I looked at your code again and it looks like Access/VB to me. So, I may just be totally confused. dill

By: VGR	Date: 08/12/2002 07:08:00		Type : Assist
that's correct (what you wrote) select max(id) from tablename; DOES return the allocated maximum ID, so if it's empty (NULL in mySql)... the table is empty... and if it's not... you have it. It's faster than this : select id from tablename order by id desc limit 1; (but produces the same result) regards

By: Letus	Date: 09/12/2002 23:14:00		Type : Assist
Dont use If Not tmp.RecordCount = 0 Then but better If not tmp.Eof and not tmp.Bof then to check, wheter if the db contains at least one record ... But, this will not solve your problem, the query will return always one row, if using MAX statement, so simply check the size of returned field (if Len(tmp.Fields("maxEmplID")) >0 then ) or numeric value (if IsNumeric(tmp.Fields("maxEmplID")) then ) I hope this will help :) Radek

By: zuwe	Date: 09/12/2002 23:44:00		Type : Comment
Hi first, use count to see if records, the base further codeing on that Set tmp = sdbase1.OpenRecordset("select Count(*) as maxEmployeeID from EmployeeDB") If Not tmp(0) = 0 Then Dim NextID As Integer NextID = 1 txtEmployeeNo.Text = NextID txtEmployeeNo.Enabled = False Else Set tmp = sdbase1.OpenRecordset("select max(EmployeeID) as maxEmployeeID from EmployeeDB") Dim NextID As Integer NextID = tmp.Fields("maxEmplID") + 1 txtEmployeeNo.Text = NextID txtEmployeeNo.Enabled = False End If Uwe

By: zuwe	Date: 09/12/2002 23:45:00		Type : Assist
Sorry little error first, use count to see if records, the base further codeing on that Set tmp = sdbase1.OpenRecordset("select Count(*) as maxEmployeeID from EmployeeDB") If tmp(0) = 0 Then Dim NextID As Integer NextID = 1 txtEmployeeNo.Text = NextID txtEmployeeNo.Enabled = False Else Set tmp = sdbase1.OpenRecordset("select max(EmployeeID) as maxEmployeeID from EmployeeDB") Dim NextID As Integer NextID = tmp.Fields("maxEmplID") + 1 txtEmployeeNo.Text = NextID txtEmployeeNo.Enabled = False End If Uwe

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 241.042140 milliseconds

Why Google AdSense ads ?

New Improved Search!