Solved

ASP. NET / SQL Server Insert problem

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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