Solved

ASP. NET / SQL Server Insert problem

Posted on 2010-08-16
2
236 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
2 Comments
 
LVL 10

Accepted Solution

by:
Jini Jose earned 250 total points
Comment Utility
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 250 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now