Purchase Order No. should be restart on every financial year(april 2012-march 2013)

Question

Hi friends i have a requirement in which i have a Field name PO No. & its format is like(DSCL-mm-yy / Kit 01) in which DSCL is fixed mm-stands for month yy-stands for year kit is the name of itemit is fixed 01 is the PO Number which will get incremented by 1 as the PO are generated. my query is how to restart the PO number once the Current financial year(april 2012-march 2013) gets close and new begins. how to get the following pattern pl reply! Regards Aamir

Rahul Bhatt · Answer

Hi Your words some thing like DSCL-01-2012 / Kit 01 DSCL-02-2012 / Kit 02 DSCL-03-2012 / Kit 03 DSCL-07-2012/ Kit 01 So you need to frst take month and year from above words After then make date using get month and year  Check with financial year start and end date If Data exist then increment no 1 Other wise reset counter Make your logic as following way DECLARE @intCOunt as INT = 0 select @intCOunt = COUNT (*) from Words where CAST ( SUBSTRING ( Word  6  2 )+ '/01/' + SUBSTRING ( Word  9  4 ) as DateTime ) BETWEEN CAST ( '04/01/' + CAST ( YEAR ( GETDATE ()) as varchar ) as DateTime ) AND CAST ( '03/31/' + CAST (( YEAR ( GETDATE ()) + 1 ) as varchar ) as DateTime ) IF ( @intCOunt = 0 ) BEGIN INSERT INTO Words ( Word ) values ( 'DSCL-' + CONVERT ( char ( 2 ) GetDate () 101 ) + '-' + cast ( year ( getdate ()) as varchar ) + '/ Kit ' + '01' ) END ELSE BEGIN INSERT INTO Words ( Word ) values ( 'DSCL-' + CONVERT ( char ( 2 ) GetDate () 101 ) + '-' + cast ( year ( getdate ()) as varchar ) + '/ Kit ' + CASE WHEN LEN ( @intCOunt + 1 ) = 1 THEN '0' + CAST (( @intCOunt + 1 ) as varchar ) ELSE CAST (( @intCOunt + 1 ) as varchar ) END ) END

Rahul Bhatt · Answer

Hi you need to make one function in asp.net C# page to retrive Purchase Number After then this purchase number direct store into DB table If value gettting then get maximum value and find number field and auto increment value int numbervalue = Convert .ToInt32(result.Substring(result.IndexOf( Kit ) + 3 result.Length - (result.IndexOf( Kit ) + 3))); numbervalue = numbervalue + 1; strPurchaseNumber = result.Substring(0 result.IndexOf( Kit ) + 3) +   + numbervalue.ToString( 00 ); Other wise insert new value strPurchaseNumber = DSCL- + DateTime .Now.Month.ToString( 00 ) + - + DateTime .Now.Year.ToString() + / Kit 01 ; Full Logic public string GetPurchaseOrderNumber() { string strPurchaseNumber=  ; string strConnection = ConfigurationManager .ConnectionStrings[ MyDbConn ].ConnectionString; string strSelect = @select ISNULL(MAX(Word) '') from Words where CAST ( SUBSTRING(Word62)+ '/01/'+ SUBSTRING(Word94) as DateTime) BETWEEN CAST ('04/01/'+ CAST(YEAR(GETDATE()) as varchar) as DateTime) AND CAST ('03/31/'+ CAST((YEAR(GETDATE()) + 1) as varchar) as DateTime)  ; SqlConnection con = new SqlConnection (strConnection); SqlCommand cmd = new SqlCommand (); cmd.Connection = con; cmd.CommandType = CommandType .Text; cmd.CommandText = strSelect; con.Open(); string result = ( string )cmd.ExecuteScalar(); con.Close(); if (result !=  ) { int numbervalue = Convert .ToInt32(result.Substring(result.IndexOf( Kit ) + 3 result.Length - (result.IndexOf( Kit ) + 3))); numbervalue = numbervalue + 1; strPurchaseNumber = result.Substring(0 result.IndexOf( Kit ) + 3) +   + numbervalue.ToString( 00 ); } else { strPurchaseNumber = DSCL- + DateTime .Now.Month.ToString( 00 ) + - + DateTime .Now.Year.ToString() + / Kit 01 ; } return strPurchaseNumber; }

Aamir Khan · Answer

Hi C D thanks for Ur reply its working according to my requirement....but i have a question how to show the NUmber or say series generated.....by ur given reply it insert into the table directly....i have to show the format on my Web Page Textbox on page load....for this wht can be done? Pl reply aamir