how to get the value of a last inserted calculated column in sql server and show to a user after submitting form in asp.net

Question

I have tried yet no answer. I have a table with a calculated column known as ApplicationNo that has prefix 'CHV18' with 000000 and then the identity column values is attached to it form something like CHV180000001, CHV180000002 etc. Now i want to retrieve that value and show it to a user for example after submitting their data it will read thus: "Data submitted successfully! Your Application No is: CHV180000001"

public bool InsertRegistration()

   {
        // Determine the currently logged on user's UserId

            MembershipUser currentUser = Membership.GetUser();
            Guid currentUserId = (Guid)currentUser.ProviderUserKey;

            //Start of Upload 1
            string filename1 = Path.GetFileName(AdmissionUpload.PostedFile.FileName);
            string contentType1 = AdmissionUpload.PostedFile.ContentType;
            using (Stream fs1 = AdmissionUpload.PostedFile.InputStream)
            {
                using (BinaryReader br1 = new BinaryReader(fs1))
                {
                    byte[] bytes1 = br1.ReadBytes((Int32)fs1.Length);


                    string filename2 = Path.GetFileName(StudentIDUpload.PostedFile.FileName);
                    string contentType2 = StudentIDUpload.PostedFile.ContentType;
                    using (Stream fs2 = StudentIDUpload.PostedFile.InputStream)
                    {
                        using (BinaryReader br2 = new BinaryReader(fs2))
                        {
                            byte[] bytes2 = br2.ReadBytes((Int32)fs2.Length);


                            string filename3 = Path.GetFileName(TranscriptUpload.PostedFile.FileName);
                            string contentType3 = TranscriptUpload.PostedFile.ContentType;
                            using (Stream fs3 = TranscriptUpload.PostedFile.InputStream)
                            {
                                using (BinaryReader br3 = new BinaryReader(fs3))
                                {
                                    byte[] bytes3 = br3.ReadBytes((Int32)fs3.Length);


                                    string filename4 = Path.GetFileName(PassportUpload.PostedFile.FileName);
                                    string contentType4 = PassportUpload.PostedFile.ContentType;
                                    using (Stream fs4 = PassportUpload.PostedFile.InputStream)
                                    {
                                        using (BinaryReader br4 = new BinaryReader(fs4))
                                        {
                                            byte[] bytes4 = br4.ReadBytes((Int32)fs4.Length);

                                            //SqlDateTime sqldatenull;

                                            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) OUTPUT INSERTED.ApplicationNo 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 insertedID = Convert.ToInt32(com.ExecuteScalar());

                                            if (rows > 0)
                                            {
                                                return true;
                                            }
                                            else
                                            {
                                                return false;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

    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 = "Data submitted successfully! Your Application No is:";
                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", "*****");

                if (InsertRegistration())
                {
                    // Only run if inserted correctly
                    smtpClient.Send(msg);
                    lblMessage.Text = "Application submitted successfully! Please copy the Application No below and also check your email for confirmation message.";
                    lblMessage.ForeColor = System.Drawing.Color.Green;
                }
                else
                {
                    lblMessage.Text = "Error submitting application";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                }
                lblMessage.Visible = true;
            }

Answer 1

It doesn't seem that you have any value to be returned. To get the PK of the last inserted record, use Scope_Identity. At the end of Insert statement add a semicolon to indicate the end of the statement then as follows.

 @Bank); SELECT SCOPE_IDENTITY();", con);

Now you have a Select statement returning a single value so, com.ExecuteScalar() should work. Don't convert it to an Integer if it is a string. If it is a number you will have to convert to a string, pad the number with zeros and concatenate the CHV18 to the insertedID.