i have a web form for users to fill in their details. I want a situation where by after submitting the form they will get an email having their auto-generated application id sent to them in this format: Application No: XXXXXX
Whenever the mail sends i am able to successfully capture the data they filled in the form fields and send them to their email but i cannot get the autogenerated application no that is only generated when the form is submitted to send it to their email stating thus Application No: XXXXXX
I have tried with the code below but no result.
SqlCommand com = new SqlCommand("INSERT INTO Candidates(FirstName, MiddleName, Surname, DateOfBirth, Phone, Email, DateApplied, CurrentLevel, MatricNo, JAMBNo, UTMEScore, YearOfAdmission, ExpectedYearOfGraduation, NIN, StudyMode, EntryMode, NextOfKin, NextOfKinEmail, NextOfKinPhone, RelationToNextOfKin, AcademicReferee, AcademicRefereeMobile, RelationWithAcademicReferee, DirectEntryRegNo, DirectEntryGrade, CurrentGPA, Courseid, Institution, HeadOfDept, HODPhone, HODEmail, RelatedToGovtOfficial, GovtOfficialName, PositionOfGovtOfficial, OnScholarship, ScholarshipName, YearOfScholarship, StateID, LGID, Community, AccountNo, SortCode, UType, AdmissionLetter, AdmissionLetterFileName, AdmissionImageType, StudentID, StudentIDFileName, StudentImageType, Transcript, TranscriptFileName, TranscriptImageType, Passport, PassportFileName, PassportImageType, Maths, Eng, Subject3, Subject4, Subject5, Subject6, Subject7, Address, FacultyID, GradeSubject3, GradeSubject4, GradeSubject5, GradeSubject6, GradeSubject7, Location, UserId, StateOfResidence, Gender, Bank) VALUES (@FirstName, @MiddleName, @Surname, @DateOfBirth, @Phone, @Email, @DateApplied, @CurrentLevel, @MatricNo, @JAMBNo, @UTMEScore, @YearOfAdmission, @ExpectedYearOfGraduation, @NIN, @StudyMode, @EntryMode, @NextOfKin, @NextOfKinEmail, @NextOfKinPhone, @RelationToNextOfKin, @AcademicReferee, @AcademicRefereeMobile, @RelationWithAcademicReferee, @DirectEntryRegNo, @DirectEntryGrade, @CurrentGPA, @Courseid, @Institution, @HeadOfDept, @HODPhone, @HODEmail, @RelatedToGovtOfficial, @GovtOfficialName, @PositionOfGovtOfficial, @OnScholarship, @ScholarshipName, @YearOfScholarship, @StateID, @LGID, @Community, @AccountNo, @SortCode, @UType, @AdmissionLetter, @AdmissionLetterFileName, @AdmissionImageType, @StudentID, @StudentIDFileName, @StudentImageType, @Transcript, @TranscriptFileName, @TranscriptImageType, @Passport, @PassportFileName, @PassportImageType, @Maths, @Eng, @Subject3, @Subject4, @Subject5, @Subject6, @Subject7, @Address, @FacultyID, @GradeSubject3, @GradeSubject4, @GradeSubject5, @GradeSubject6, @GradeSubject7, @Location, @UserId, @StateOfResidence, @Gender, @Bank)", con);
com.Parameters.AddWithValue("@FirstName", txtFN.Text);
com.Parameters.AddWithValue("@MiddleName", txtMN.Text);
com.Parameters.AddWithValue("@Surname", txtLN.Text);
com.Parameters.AddWithValue("@DateOfBirth", txtdob.Text);
//sqldatenull = SqlDateTime.Null;
// if (txtdob.Text == "")
// {
// com.Parameters["@DateOfBirth"].Value = sqldatenull;
//cmd.Parameters["@Date"].Value = DBNull.Value;
//}
//else
//{
// com.Parameters["@DateOfBirth"].Value = DateTime.Parse(txtdob.Text);
// }
com.Parameters.AddWithValue("@Phone", txtphone.Text);
com.Parameters.AddWithValue("@Email", txtemail.Text);
com.Parameters.AddWithValue("@DateApplied", txtdap.Text);
//sqldatenull = SqlDateTime.Null;
//if (txtdap.Text == "")
//{
//com.Parameters["@DateApplied"].Value = sqldatenull;
//cmd.Parameters["@Date"].Value = DBNull.Value;
//}
//else
//{
//com.Parameters["@DateApplied"].Value = DateTime.Parse(txtdap.Text);
// }
com.Parameters.AddWithValue("@CurrentLevel", ddlclevel.SelectedItem.Text);
com.Parameters.AddWithValue("@MatricNo", txtmatric.Text);
com.Parameters.AddWithValue("@JAMBNo", txtjamb.Text);
com.Parameters.AddWithValue("@UTMEScore", txtutme.Text);
com.Parameters.AddWithValue("@YearOfAdmission", ddlyear.SelectedItem.Text);
com.Parameters.AddWithValue("@ExpectedYearOfGraduation", ddlgraduation.SelectedItem.Text);
com.Parameters.AddWithValue("@NIN", txtnin.Text);
com.Parameters.AddWithValue("@StudyMode", ddlstudytime.SelectedItem.Text);
com.Parameters.AddWithValue("@EntryMode", ddlentrymode.SelectedItem.Text);
com.Parameters.AddWithValue("@NextOfKin", txtkin.Text);
com.Parameters.AddWithValue("@NextOfKinEmail", txtkinemail.Text);
com.Parameters.AddWithValue("@NextOfKinPhone", txtkinphone.Text);
com.Parameters.AddWithValue("@RelationToNextOfKin", txtkinrelation.Text);
com.Parameters.AddWithValue("@AcademicReferee", txtacademicreferee.Text);
com.Parameters.AddWithValue("@AcademicRefereeMobile", txtacadmobile.Text);
com.Parameters.AddWithValue("@RelationWithAcademicReferee", txtacadrelation.Text);
com.Parameters.AddWithValue("@DirectEntryRegNo", txtdirectentry.Text);
com.Parameters.AddWithValue("@DirectEntryGrade", txtentrygrade.Text);
com.Parameters.AddWithValue("@CurrentGPA", txtgpa.Text);
com.Parameters.AddWithValue("@Courseid", ddlcourse.SelectedItem.Value);
com.Parameters["@Courseid"].Value = ddlcourse.SelectedItem.Value;
com.Parameters.AddWithValue("@Institution", ddlUniversity.SelectedItem.Value);
com.Parameters["@Institution"].Value = ddlUniversity.SelectedItem.Value;
com.Parameters.AddWithValue("@HeadOfDept", txthod.Text);
com.Parameters.AddWithValue("@HODPhone", txthodphone.Text);
com.Parameters.AddWithValue("@HODEmail", txthodemail.Text);
com.Parameters.AddWithValue("@RelatedToGovtOfficial", ddlrgovtoff.SelectedItem.Text);
com.Parameters.AddWithValue("@GovtOfficialName", txtgovtofficial.Text);
com.Parameters.AddWithValue("@PositionOfGovtOfficial", txtposgovt.Text);
com.Parameters.AddWithValue("@OnScholarship", ddlsch.SelectedItem.Text);
com.Parameters.AddWithValue("@ScholarshipName", txtschname.Text);
com.Parameters.AddWithValue("@YearOfScholarship", ddlschyear.SelectedItem.Text);
com.Parameters.AddWithValue("@StateID", ddlState.SelectedItem.Value);
com.Parameters["@StateID"].Value = ddlState.SelectedItem.Value;
com.Parameters.AddWithValue("@LGID", ddllga.SelectedItem.Value);
com.Parameters["@LGID"].Value = ddllga.SelectedItem.Value;
com.Parameters.AddWithValue("@Community", txtcommunity.Text);
com.Parameters.AddWithValue("@AccountNo", txtaccno.Text);
com.Parameters.AddWithValue("@SortCode", txtsortcode.Text);
com.Parameters.AddWithValue("@UType", ddlUType.SelectedItem.Value);
com.Parameters["@UType"].Value = ddlUType.SelectedItem.Value;
com.Parameters.AddWithValue("@AdmissionLetter", bytes1);
com.Parameters.AddWithValue("@AdmissionLetterFileName", filename1);
com.Parameters.AddWithValue("@AdmissionImageType", contentType1);
com.Parameters.AddWithValue("@StudentID", bytes2);
com.Parameters.AddWithValue("@StudentIDFileName", filename2);
com.Parameters.AddWithValue("@StudentImageType", contentType2);
//com.Parameters.AddWithValue("@CourtAffidavit", bytes3);
//com.Parameters.AddWithValue("@CourtAffidavitFileName", filename3);
//com.Parameters.AddWithValue("@CourtAffidavitImageType", contentType3);
com.Parameters.AddWithValue("@Transcript", bytes3);
com.Parameters.AddWithValue("@TranscriptFileName", filename3);
com.Parameters.AddWithValue("@TranscriptImageType", contentType2);
com.Parameters.AddWithValue("@Passport", bytes4);
com.Parameters.AddWithValue("@PassportFileName", filename4);
com.Parameters.AddWithValue("@PassportImageType", contentType4);
com.Parameters.AddWithValue("@Maths", ddlgrademaths.SelectedItem.Text);
com.Parameters.AddWithValue("@Eng", ddlgradeeng.SelectedItem.Text);
com.Parameters.AddWithValue("@Subject3", txtsubject3.Text);
com.Parameters.AddWithValue("@Subject4", txtsubject4.Text);
com.Parameters.AddWithValue("@Subject5", txtsubject5.Text);
com.Parameters.AddWithValue("@Subject6", txtsubject6.Text);
com.Parameters.AddWithValue("@Subject7", txtsubject7.Text);
com.Parameters.AddWithValue("@Address", txtaddress.Text);
com.Parameters.AddWithValue("@FacultyID", ddlfaculty.SelectedItem.Value);
com.Parameters["@FacultyID"].Value = ddlfaculty.SelectedItem.Value;
com.Parameters.AddWithValue("@GradeSubject3", ddlgradsub3.SelectedItem.Text);
com.Parameters.AddWithValue("@GradeSubject4", ddlgradesub4.SelectedItem.Text);
com.Parameters.AddWithValue("@GradeSubject5", ddlgradesub5.SelectedItem.Text);
com.Parameters.AddWithValue("@GradeSubject6", ddlgradesub6.SelectedItem.Text);
com.Parameters.AddWithValue("@GradeSubject7", ddlgradesub7.SelectedItem.Text);
com.Parameters.AddWithValue("@Location", ddllocation.SelectedItem.Text);
com.Parameters.AddWithValue("@UserId", currentUserId);
com.Parameters.AddWithValue("@StateOfResidence", ddlstateofresidence.SelectedItem.Text);
com.Parameters.AddWithValue("@Gender", ddlgender.SelectedItem.Text);
com.Parameters.AddWithValue("@Bank", ddlbankname.SelectedItem.Text);
con.Open();
// open connection here, just before executing
// return the true/false for whether a row was inserted
int rows = com.ExecuteNonQuery();
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
}
}
}
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (User.Identity.IsAuthenticated)
{
txtemail.Text = Membership.GetUser().Email;
}
//if (User.Identity.IsAuthenticated)
//{
// string userid = Membership.GetUser().ProviderUserKey.ToString();
// txtuserid.Text = userid;
//}
lblMessage.Visible = false;
//txtFN.Text = Session["FirstName"].ToString();
//txtMN.Text = Session["MiddleName"].ToString();
//txtLN.Text = Session["Surname"].ToString();
//txtphone.Text = Session["Phone"].ToString();
//txtcommunity.Text = Session["Community"].ToString();
//txtnin.Text = Session["NIN"].ToString();
//txtdob.Text = Session["DateOfBirth"].ToString();
//txtaddress.Text = Session["Address"].ToString();
}
protected void btnsub_Click(object sender, EventArgs e)
{
//Start of Send Mail Region
//Fetching Email Body Text from EmailTemplate File.
string MailText = string.Empty;
//using streamreader for reading my htmltemplate
using (StreamReader reader = new StreamReader(Server.MapPath("~/Account/RegMessage.html")))
{
MailText = reader.ReadToEnd();
//Repalce [userdetails] = user details
MailText = MailText.Replace("[ApplicationID]", reg.ApplicationID.ToString());
MailText = MailText.Replace("[FirstName]", txtFN.Text.Trim());
MailText = MailText.Replace("[MiddleName]", txtMN.Text.Trim());
MailText = MailText.Replace("[Surname]", txtLN.Text.Trim());
MailText = MailText.Replace("[MatricNo]", txtmatric.Text.Trim());
MailText = MailText.Replace("[DateApplied]", txtdap.Text.Trim());
MailMessage msg = new MailMessage();
msg.To.Add(txtemail.Text.ToString());
MailAddress from = new MailAddress("scholarships@orm-ng.com", "CHEVRON Scholarships");
msg.From = from;
msg.Subject = "Successful Application Confirmation";
msg.IsBodyHtml = true;
msg.Body = MailText;
SmtpClient smtpClient = new SmtpClient("smtp.1and1.com", 587);
smtpClient.EnableSsl = true;
smtpClient.Credentials = new System.Net.NetworkCredential("scholarships@orm-ng.com", "Optimuma@123");
if (InsertRegistration())
{
// Only run if inserted correctly
smtpClient.Send(msg);
lblMessage.Text = "Application submitted successfully! Check your email for further instructions and details.";
lblMessage.ForeColor = System.Drawing.Color.Green;
}
else
{
lblMessage.Text = "Error submitting application";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
lblMessage.Visible = true;
}
//End of Send Mail Region
