Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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.
[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;
        }

Open in new window

0
SubbuUSA
Asked:
SubbuUSA
2 Solutions
 
Jini Jose.Net Team LeadCommented:
in the below code SQLData is the microsoft data access blocks helper class.
 SqlParameter[] oparam = new SqlParameter[1];
            oparam = new SqlParameter[1];
            oparam[0] = new SqlParameter("@Name", TextBox1.Text);
            string Query = "insert into Members Select @Name";
            SQLData.ExecuteNonQuery(Constring, CommandType.Text, Query, oparam).ToString();

Open in new window

0
 
revteamCommented:
Set the text box values to your employee object
i.e.


Employee insEmployee = new Employee ()
insEmployee. FirstName  = txtFirstname.Text;
similarily for all the fields you are inserting
then call   create employee method  by passing the initialized values.
CreateEmployee(insEmployee )


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now