Solved

ASP. NET / SQL Server Insert problem

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
User Authentication using Digital Certificate 2 47
compare between entities in entity framework 8 73
Hey!!!! 1 22
C# LINQ ForEach() question 6 28
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

809 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