ASP. NET / SQL Server Insert problem
Hello Friends,
I have a web application in ASP.Net and the back end is SQL Server. I am trying to perform a parameterized query. Can somebody help how do i make this mode work with the values that I need to take from the texboxes and assign it here.
I have a web application in ASP.Net and the back end is SQL Server. I am trying to perform a parameterized query. Can somebody help how do i make this mode work with the values that I need to take from the texboxes and assign it here.
[MapInfo("ID", "ID")]
[MapInfo("LastName", "LastName")]
[MapInfo("Branch", "Branch")]
[MapInfo("FirstName", "FirstName")]
[MapInfo("ShiftPreference", "ShiftPreference")]
[MapInfo("MiddleInitial", "MiddleInitial")]
[MapInfo("SeasonID", "SeasonID")]
[MapInfo("PhoneNumber", "PhoneNumber")]
[MapInfo("MPAccount", "MPAccount")]
[MapInfo("Email", "Email")]
[MapInfo("NoLeadership", "NoLeadership")]
[MapInfo("AlternatePhoneNumber", "AlternatePhoneNumber")]
[MapInfo("EmploymentStatus", "EmploymentStatus")]
[MapInfo("LastFour", "LastFour")]
[MapInfo("NotesNScheduleConflict", "NotesNScheduleConflict")]
[MapInfo("Major", "Major")]
[MapInfo("Spanish", "Spanish")]
[MapInfo("DiplomaOnFile", "DiplomaOnFile")]
[MapInfo("TranscriptsOnFile", "TranscriptsOnFile")]
[MapInfo("Logon", "Logon")]
[MapInfo("Math", "Math")]
[MapInfo("Reading", "Reading")]
[MapInfo("Writing", "Writing")]
[MapInfo("SocialStudies", "SocialStudies")]
[MapInfo("USHistory", "USHistory")]
[MapInfo("Science", "Science")]
[MapInfo("Physics", "Physics")]
[MapInfo("Chemistry", "Chemistry")]
[MapInfo("Biology", "Biology")]
[MapInfo("I9", "I9")]
[MapInfo("Transcripts", "Transcripts")]
[MapInfo("Reference", "Reference")]
[MapInfo("LogonID", "LogonID")]
public class Employee : GroupflowBase {
#region Employee Enumerations
public enum Hispanic { None = 0, Basic, Advanced }
public enum Education { TwoYearPlus = 1, BA, MA, PHD }
public enum EmpSts { Available = 1, Assigned, Inactive, No_Rehire }
public enum ShiftPref { Day = 1, Evening, Day_Evening, Evening_Day }
#endregion
public int ID { get; set; }
public string LastName { get; set; }
public KeyValuePair<int, string> Branch { get; set; }
public string FirstName { get; set; }
public ShiftPref ShiftPreference { get; set; }
public string MiddleInitial { get; set; }
public string SeasonID { get; set; }
public string PhoneNumber { get; set; }
public bool MPAccount { get; set; }
public string Email { get; set; }
public bool NoLeadership { get; set; }
public string AlternatePhoneNumber { get; set; }
public EmpSts EmploymentStatus { get; set; }
public string LastFour { get; set; }
public string NotesNScheduleConflict { get; set; }
public Education EducationLevel { get; set; }
public string Major { get; set; }
public Hispanic Spanish { get; set; }
public bool DiplomaOnFile { get; set; }
public bool TranscriptsOnFile { get; set; }
public string Logon { get; set; }
public bool Math { get; set; }
public bool Reading { get; set; }
public bool Writing { get; set; }
public bool SocialStudies { get; set; }
public bool USHistory { get; set; }
public bool Science { get; set; }
public bool Physics { get; set; }
public bool Chemistry { get; set; }
public bool Biology { get; set; }
public bool I9 { get; set; }
public bool Transcripts { get; set; }
public bool Reference { get; set; }
public string LogOnID { get; set; }
-----------------
public int CreateEmployee(Employee insEmp) {
SqlCommand insertCmd = new SqlCommand();
SqlParameter par = null;
try {
string sqlStr = @"
Insert into EE_Master (
LastName, Branch, FirstName, ShiftPreference, MiddleInitial,
SeasonID, PhoneNumber, MPAccount, Email, NoLeadership,
AlternatePhoneNumber, EmploymentStatus, LastFour, NotesNScheduleConflict, EducationLevel,
Major,Spanish,DiplomaOnFile,TranscriptsOnFile,Logon,
Math,Reading,Writing,SocialStudies,USHistory,Science,
Physics,Chemistry,Biology,I9,Transcripts,Reference, LogonID)
Values (
@LastName , @Branch, @FirstName, @ShiftPreference, @MiddleInitial,
@SeasonID, @PhoneNumber, @MPAccount, @Email,@NoLeadership,
@AlternatePhoneNumber, @EmploymentStatus, @LastFour,@NotesNScheduleConflict, @EducationLevel,
@Major, @Spanish, @DiplomaOnFile, @TranscriptsOnFile,@Logon,
@Math, @Reading, @Writing, @SocialStudies, @USHistory, @Science,
@Physics, @Chemistry, @Biology,@I9, @Transcripts, @Reference,@LogonID)
SET @EmployeeID = SCOPE_IDENTITY()";
insertCmd.CommandText = sqlStr;
/* Fields that needs to be updated in the EE_Master table in the StaffTracker DB */
//1 to 5
// LastName, Branch, FirstName, ShiftPreference ,MiddleInitial
par = new SqlParameter("@LastName", SqlDbType.NVarChar, 255);
par.Value = insEmp.LastName;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Branch", SqlDbType.Int, 4);
par.Value = insEmp.Branch.Key;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@FirstName", SqlDbType.NVarChar, 255);
par.Value = insEmp.FirstName;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@ShiftPreference", SqlDbType.Int, 4);
par.Value = (int)insEmp.ShiftPreference;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@MiddleInitial", SqlDbType.NVarChar, 1);
par.Value = insEmp.MiddleInitial;
insertCmd.Parameters.Add(par);
//6 to 10
//SeasonID, PhoneNumber, MPAccount, Email, NoLeadership
par = new SqlParameter("@SeasonID", SqlDbType.NVarChar, 10);
par.Value = insEmp.SeasonID;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar, 255);
par.Value = insEmp.PhoneNumber;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@MPAccount", SqlDbType.Bit, 1);
par.Value = insEmp.MPAccount;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Email", SqlDbType.NVarChar, 255);
par.Value = insEmp.Email;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@NoLeadership", SqlDbType.Bit, 1);
par.Value = insEmp.NoLeadership;
insertCmd.Parameters.Add(par);
// 11 to 15
//AlternatePhoneNumber, EmploymentStatus, LastFour, NotesNScheduleConflict, EducationLevel,
par = new SqlParameter("@AlternatePhoneNumber", SqlDbType.NVarChar, 255);
par.Value = insEmp.AlternatePhoneNumber;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@EmployeeStatus", SqlDbType.Int, 4);
par.Value = (int)insEmp.EmploymentStatus;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@LastFour", SqlDbType.NVarChar, 4);
par.Value = insEmp.LastFour;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@NotesNScheduleConflict", SqlDbType.NVarChar, 255);
par.Value = insEmp.NotesNScheduleConflict;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@EducationLevel", SqlDbType.Int, 4);
par.Value = (int)insEmp.EducationLevel;
insertCmd.Parameters.Add(par);
//16 to 20
//Major,Spanish,DiplomaOnFile,TranscriptsOnFile,Logon,
par = new SqlParameter("@Major", SqlDbType.NVarChar, 255);
par.Value = insEmp.Major;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Spanish", SqlDbType.TinyInt, 1);
par.Value = (byte)insEmp.Spanish;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@DiplomaOnFile", SqlDbType.Bit, 1);
par.Value = insEmp.DiplomaOnFile;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@TranscriptsOnFile", SqlDbType.Bit, 1);
par.Value = insEmp.TranscriptsOnFile;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Logon", SqlDbType.NVarChar, 20);
par.Value = insEmp.Logon;
insertCmd.Parameters.Add(par);
//21 to 25
//Math,Reading,Writing,SocialStudies,USHistory,
par = new SqlParameter("@Math", SqlDbType.Bit, 1);
par.Value = insEmp.Math;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Reading", SqlDbType.Bit, 1);
par.Value = insEmp.Reading;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Writing", SqlDbType.Bit, 1);
par.Value = insEmp.Writing;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@SocialStudies", SqlDbType.Bit, 1);
par.Value = insEmp.SocialStudies;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@USHistory", SqlDbType.Bit, 1);
par.Value = insEmp.USHistory;
insertCmd.Parameters.Add(par);
//26 to 33
//Science,Physics,Chemistry,Biology,I9,Transcripts,References, LogonID
par = new SqlParameter("@Science", SqlDbType.Bit, 1);
par.Value = insEmp.Science;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Physics", SqlDbType.Bit, 1);
par.Value = insEmp.Physics;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Chemistry", SqlDbType.Bit, 1);
par.Value = insEmp.Chemistry;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Biology", SqlDbType.Bit, 1);
par.Value = insEmp.Biology;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@I9", SqlDbType.Bit, 1);
par.Value = insEmp.I9;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Transcripts", SqlDbType.Bit, 1);
par.Value = insEmp.Transcripts;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@Reference", SqlDbType.Bit, 1);
par.Value = insEmp.Reference;
insertCmd.Parameters.Add(par);
par = new SqlParameter("@LogonID", SqlDbType.NVarChar, 10);
par.Value = insEmp.LogOnID;
insertCmd.Parameters.Add(par);
foreach (SqlParameter sqlPar in insertCmd.Parameters) {
sqlPar.Direction = ParameterDirection.Input;
}
par = new SqlParameter("@EmployeeID", SqlDbType.Int, 4);
par.Direction = ParameterDirection.Output;
insertCmd.Parameters.Add(par);
DataAccess myDataAccess = new DataAccess();
myDataAccess.ExecuteCommand(insertCmd);
} catch {
}
return (int)insertCmd.Parameters["@EmployeeID"].Value;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.