public string NL()//function to generate a new LoanCode
{
string LastID = "", NewCode, FirstHalf, SecondHalf;
int SecondHalfI, NoOfZeros;
OleDbConnection Conn = new OleDbConnection(Program.ConnString);
Conn.Open();
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "SELECT Top 1 * FROM Loans ORDER BY LoanCode DESC"; //selects everything from borrowers
OleDbDataReader reader = Cmd.ExecuteReader();
if (reader.HasRows)//If a record is found display details
{
reader.Read();
LastID = Convert.ToString(reader["LoanCode"]); //gets the borrowernumber from borrowers
FirstHalf = LastID.Substring(0, 2);
SecondHalf = LastID.Substring(2, 12);
SecondHalfI = Convert.ToInt32(SecondHalf) + 1; //incrementing the borrowernumber by 1 each time a new borrower is added
SecondHalf = SecondHalfI.ToString();
NoOfZeros = 13 - SecondHalf.Length;
SecondHalf = SecondHalf.PadLeft(NoOfZeros, '0');
NewCode = FirstHalf + SecondHalf;
}
else
{
NewCode = "L1000000000001";//if it is the first loan
}
reader.Close();
Conn.Close();
return(NewCode);
}
InputValidation IV = new InputValidation(); //declaring an instance of InputValidation from SampleLibrary
public void ID() //procedure to create a new BorrowerNumber
{
OleDbConnection Conn = new OleDbConnection(Program.ConnString);
Conn.Open();
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "SELECT Top 1 * FROM Borrowers ORDER BY BorrowerNumber DESC"; //selects everything from borrowers
OleDbDataReader reader = Cmd.ExecuteReader();
if (reader.HasRows)//If a record is found display details
{
reader.Read();
LastID = Convert.ToString(reader["BorrowerNumber"]); //gets the borrowernumber from borrowers
FirstHalf = LastID.Substring(0, 2);
SecondHalf = LastID.Substring(2, 12);
SecondHalfI = Convert.ToInt32(SecondHalf) + 1; //incrementing the borrowernumber by 1 each time a new borrower is added
SecondHalf = SecondHalfI.ToString();
NoOfZeros = 13 - SecondHalf.Length;
SecondHalf = SecondHalf.PadLeft(NoOfZeros, '0');
NewID = FirstHalf + SecondHalf;
}
else
{
NewID = "D1000000000001";//first BorrowerNumber if database has just been created
}
reader.Close();
Conn.Close();
tbBorrowerNumber.Text = NewID;//displays the new BorrowerNumber in tbBorrowerNumber
}