CodeCharge Studio
search Register Login  

Web Reporting

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

YesSoftware Forums -> CodeCharge Studio -> ASP

 Max values for non ID fields

Print topic Send  topic

Author Message
cruisin

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 SQL
Dim SQL2
Dim RecordSet
Set Connection = New clsDBConnection1
Dim max_num
Connection.Open
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
Edd


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)   
Connection.Execute(SQL)  

Do it in one hit

_________________
Accepting and instigating change are life's challenges.

http://www.syntech.com.au
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 http://support.yessoftware.com.

PHP Reports

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

Home   |    Search   |    Members   |    Register   |    Login


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