getting the max number from a string field

[CSdjohnson]

i have a database with a column called custnum of type char. if i have the following customer numbers in the database:
1
2
3
4
5
6
7
8
9
10

then it is stored like this in the table:
1
10
2
3
4
5
6
7
8
9

i am trying to get the highest custnum so far so that when a user is adding a new customer i can suggest the next highest number based on the numbers allready in use. the customer number can be alphanumeric. here is the code i am using:

If objPrefs.getPrefSettingDef("promptcust", "0&quot <> 0 And g_iCustMode = 3 Then
Screen.MousePointer = vbHourglass
strSQL = "SELECT MAX(custnum) AS newnum FROM" & Space$(1) & g_oDBIM.DBPfx(primarydb).PfxCusts & "customers"
strSQL = strSQL & Space$(1) & " WHERE type_cd = '" & strCustType & "'"
strSQL = strSQL & Space$(1) & " AND ISNUMERIC(custnum) <> 0"
OpendbDB
CS.OpenRecordset rstRS, strSQL, dbDB, csOpenForwardOnly, csLockReadOnly
If rstRS.EOF Or IsNull(rstRS("newnum&quot) = True Then
strSQL = "SELECT MAX(custnum) AS newnum FROM " & Space$(1) & g_oDBIM.DBPfx(primarydb).PfxCusts & "customers"
strSQL = strSQL & Space$(1) & " WHERE type_cd = '" & strCustType & "'"
CS.OpenRecordset rstRS, strSQL, dbDB, csOpenForwardOnly, csLockReadOnly
End If

If Not rstRS.BOF And Not rstRS.EOF And Not IsNull(rstRS("newnum&quot) Then
strNewNum = rstRS("newnum&quot
intNewNumLen = Len(strNewNum)
If Not IsNumeric(strNewNum) Then
For intCtr = 1 To Len(strNewNum)
If Not IsNumeric(Right$(strNewNum, intCtr)) Then
Exit For
End If
Next intCtr
If intCtr > 1 Then
lngMyNum = CLng(Right$(strNewNum, intCtr - 1)) + 1
Else
lngMyNum = 1
End If
strNewNum = Left$(strNewNum, Len(strNewNum) - (intCtr - 1))
If Len(strNewNum) + Len(lngMyNum) < Len(rstRS("newnum&quot) Then
strNewNum = strNewNum & "0"
End If
strNewNum = strNewNum & CStr(PadX(lngMyNum, intCtr - 1))
Else
strNewNum = PadX(CLng(strNewNum) + 1, intNewNumLen)
End If
Else
strNewNum = 1
End If
txtTabFour(0).Text = strNewNum
End If


how do i get the correct max custnum so that the correct sugguestion is made to the user?

 

[JeffTullin]

How about
SELECT MAX(Val(custnum)) from THETABLE

Val('10') is greater than VAL('2')
Val('Fred') should be 0, so you don't bother with ISNUMERIC

You could even do the whole thing in one..

SELECT MAX(Val(custnum))+1 from THETABLE

This assumes that the database you are querying supports Val(). (Access does, for instance, but I have a feeling that you need to 'group by' to get Max to work. I could be wrong there...)

 

[CSdjohnson]

that works great with access, but not so good with sql server. any ideas on how to do it with sql server?

 

[zemp]

Try,

SELECT MAX(CAST(custnum as int))+1 from THETABLE

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[MichaelRed]

On the other hand, the entire process is questionalble. In a multiuser environment, the value may easily be retrieved by more than one user, thus creating duplications. Most database engines include aome facillity for creating a "guarnted" unique value (@@Identity for SQL Server) which should be used as the key value for items like this. If you want 'your' value, retrieve the record with the MAx "identity", Lock the record, retrieve your value from the record, do the increment and insert of your record. Unfortunatly, you need to make SURE that all access to the recordset for these purposes use the same procedure / mechanisim to generate new values. - Oh and release the record lock when you are done ...






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[LostInCode]

Yes, very questionable method on a unique field.
MichaelRed hit it on the nail with this professional comment.

This cannot work properly in a multi-user situation unless the number is created on the server (either through a Identity field, or a stored proceedure) AND THEN the new record is being Immediately! added (as with an Identity/Auto number field) so the next user doesn't get the same number (unless of course the table is locked between getting the next number and until the user adds the new record).
The "empty" record with the new value needs to be created at the same time, on or through the server, in order to minimize conflicts, or as an absolute minimun, as mentioned by MichaelRed, the table is locked, the next value retieved, and the record is then added using and the lock released (a stored proceedure will be faster though).