Как получить значение последнего вставленного вычисляемого столбца на сервере sql и показать пользователю после отправки формы в asp.net
Я пробовал еще нет ответа. У меня есть таблица с вычисляемым столбцом, известным как ApplicationNo, который имеет префикс 'CHV18' с 000000, а затем к нему прикрепляется значение столбца идентификаторов, например, CHV180000001, CHV180000002 и т. Д. Теперь я хочу получить это значение и показать его пользователю. например, после отправки их данных, оно будет выглядеть следующим образом: "Данные успешно отправлены! Ваш номер заявки: 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;
}
1 ответ
Не похоже, что у вас есть какое-либо значение, которое будет возвращено. Чтобы получить PK последней вставленной записи, используйте Scope_Identity. В конце оператора вставки добавьте точку с запятой, чтобы обозначить конец оператора, следующим образом.
@Bank); SELECT SCOPE_IDENTITY();", con);
Теперь у вас есть оператор Select, возвращающий одно значение, так что com.ExecuteScalar() должно сработать. Не конвертируйте его в Integer, если это строка. Если это число, которое вам нужно будет преобразовать в строку, заполните число нулями и объедините CHV18 с вставленным идентификатором.