Solved

ASP. NET / SQL Server Insert problem

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Name Space error VS2015 1 38
Find unused columns in a table 12 77
Build a string of emails from a gridview 2 20
ASP.NET data base connection 35 45
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…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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