This is the code i created for my project to create dynamically voucher No
It will sure help u , same concept.Let me know if u feel confusion
public void GenerateCashVoucherNo(bool PR)
{
int MaxEntryMonth = 0, MinEntryMont = 0, diff = 0;
DateTime Today = DateTime.Now;
int Year = Today.Year;
int Month = Today.Month;
DateTime Yesterday = DateTime.Now.AddDays(-1);
int MonthDiff = Today.Month - Yesterday.Month;
clsBLCashBook oCash = clsBLCashBook.getObject();
oCash.EntityID = clsGlobalVar.CompanyID;
oCash.value = 4;
DataTable dt = oCash.getCBData();
string dbSeqNo = dt.Rows[0][0].ToString();
string dblastSeqNo = dt.Rows[0][1].ToString();
if (dbSeqNo != "")
{
MaxEntryMonth = Convert.ToInt16(dbSeqNo.Substring(4, 2));
MinEntryMont = Convert.ToInt16(dblastSeqNo.Substring(4, 2));
diff = MaxEntryMonth - MinEntryMont;
}
if (MonthDiff == 0)
{
if (dbSeqNo == "" || MaxEntryMonth!=Month)
{
int Seq = 1;
if (Month.ToString().Length == 1)
{
SeqNo = Year.ToString() + "0" + Month.ToString() + "000" + Seq.ToString();
}
else
{
SeqNo = Year.ToString() + Month.ToString() + "000" + Seq.ToString();
}
}
else
{
int Seq = Convert.ToInt32(dbSeqNo) + 1;
SeqNo = Seq.ToString();
}
if (PR == true)
{
txtVoucherNo.Text = "CB/P/" + SeqNo;
}
else
{
txtVoucherNo.Text = "CB/R/" + SeqNo;
}
}
else
{
if (diff == 0)
{
if (dbSeqNo == "" || dbSeqNo.Equals(null))
{
int Seq = 1;
if (Month.ToString().Length == 1)
{
SeqNo = Year.ToString() + "0" + Month.ToString() + "000" + Seq.ToString();
}
else
{
SeqNo = Year.ToString() + Month.ToString() + "000" + Seq.ToString();
}
}
else
{
int Seq = Convert.ToInt32(dbSeqNo) + 1;
SeqNo = Seq.ToString();
}
if (PR == true)
{
txtVoucherNo.Text = "CB/P/" + SeqNo;
}
else
{
txtVoucherNo.Text = "CB/R/" + SeqNo;
}
}
else
{
}
}
}
This is the Query for Value 4 from the database that is called from upper.
IF (@value=4)
BEGIN
Select MAX(SeqNo) as SeqNo,MIN(SeqNo) as LastSeqNo from tbl_Cash where SeqNo IN (Select top 2 SeqNo from tbl_Cash order by SeqNo desc)
END