Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ASP. NET / SQL Server Insert problem

Posted on 2010-08-16
2
Medium Priority
?
312 Views
Last Modified: 2012-05-10
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
Comment
Question by:SubbuUSA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
Jini Jose earned 1000 total points
ID: 33449901
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
 
LVL 1

Assisted Solution

by:revteam
revteam earned 1000 total points
ID: 33453690
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question