CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.

YesSoftware Forums -> CodeCharge Studio -> ASP

 Max values for non ID fields

Print topic Send  topic

Author Message

Posts: 31
Posted: 11/24/2009, 5:52 AM

I'm trying to create an account numbering system that mimics an older system that I converted data from. I'm trying to increment the old value which is no longer the field id and it doesn't seem to be working. This code works fine if I use the caseid field instead of max_num. Otherwise neither field gets updated. Suggestions.


Dim Connection
Dim SQL2
Dim RecordSet
Set Connection = New clsDBConnection1
Dim max_num
max_num = CCDLookup("max(casenum)","cases","",DBconnection1)
SQL2 = "update cases set casenum = max_num where caseid =" & caseid
SQL = "update cases set casecode = (STR(YEAR(createdate)) +'-'+ STR(max_num)) where caseid =" & caseid
Set RecordSet = Connection.Execute(SQL)
View profile  Send private message

Posts: 547
Posted: 11/25/2009, 4:06 PM

You wrote 2 SQL statements and only executed the second.

Try (if you are using MSSQL)
SQL = "update cases set casenum = (Select max(casenum) + 1 from cases) , CAST( DatePart(Year, createdate) as Char(4)) + '-' + CAST( (Select max(casenum) + 1 from cases) as varchar(7)) where caseid =" & Connection.ToSQL( caseid, ccsInteger)   

Do it in one hit

Accepting and instigating change are life's challenges.
View profile  Send private message

Add new topic Subscribe to topic   

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.

Home   |    Search   |    Members   |    Register   |    Login

Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by  and YesSoftware, Inc.