Solved

ASP. NET / SQL Server Insert problem

Posted on 2010-08-16
2
241 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
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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 44
upgrade sql 2005 32bit to sql 2008 32 or 64bit on a server 2008 r2 box 6 53
Unable to save view in SSMS 21 57
C# Json POSt to Rest API 4 36
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

21 Experts available now in Live!

Get 1:1 Help Now