Example #1
0
        private static void InsertGroup(MongoServer mongo, MongoDatabase db, SqlCommand cmd)
        {

            cmd.CommandText = "delete from  DoubanGroups";

            cmd.ExecuteNonQuery();

            using (mongo.RequestStart(db))
            {
                var collection = db.GetCollection<BsonDocument>("groupinfo_collection");

                foreach (var item in collection.FindAll())
                {

                    var list = item.RawValues.ToList();

                    cmd.CommandText = "insert into DoubanGroups values (@v1,@v2,@v3)";

                    int v2;
                    if (!int.TryParse(list[2].ToString(), out v2))
                    {
                        v2 = -1;
                    }
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@v1", list[1].ToString());
                    cmd.Parameters.AddWithValue("@v2", v2);
                    cmd.Parameters.AddWithValue("@v3", list[3].ToString());

                    cmd.ExecuteNonQuery();
                }
            }
        }
Example #2
0
    public bool UpdatePersionalDetail(UserDetails user)
    {
        try
        {
            SqlCommand com = new SqlCommand();
            com.Connection = DbManager.Con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "SP_Update_Persional_Details";



            com.Parameters.AddWithValue("@Bride_Id", user.Id);
            com.Parameters.AddWithValue("@Groom_Id", user.Id);
            com.Parameters.AddWithValue("@Height", Convert.ToInt32( user.Height));
            com.Parameters.AddWithValue("@About_Me", user.About_Me);
            com.Parameters.AddWithValue("@About_My_Patner", user.About_My_Patner);
            com.Parameters.AddWithValue("@Physical_Status", user.Physical_Status);
            com.Parameters.AddWithValue("@Weight", user.Weight);
            com.Parameters.AddWithValue("@Gender", user.Gender);

            
            DbManager.Open();
            com.ExecuteNonQuery();
            return Convert.ToBoolean(com.ExecuteNonQuery());

        }
        catch { throw; }
        finally
        {
            DbManager.Close();

        }

    }
Example #3
0
    public bool UpdateEducationlDetail(UserDetails user)
    {
        try
        {
            SqlCommand com = new SqlCommand();
            com.Connection = DbManager.Con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "SP_Update_Education_And_Profession";



            com.Parameters.AddWithValue("@Bride_Id", user.Id);
            com.Parameters.AddWithValue("@Groom_Id", user.Id);


            com.Parameters.AddWithValue("@Occupation", user.Occupation);
            com.Parameters.AddWithValue("@Profession_Id", user.Profession_Id);
            com.Parameters.AddWithValue("@Anual_Incom", user.Anual_Incom);
            com.Parameters.AddWithValue("@Educational_Qualification", user.Education);
            com.Parameters.AddWithValue("@Gender", user.Gender);

            DbManager.Open();
            com.ExecuteNonQuery();
            return Convert.ToBoolean(com.ExecuteNonQuery());

        }
        catch { throw; }
        finally
        {
            DbManager.Close();

        }

    }
Example #4
0
        protected void ButInput_Click(object sender, System.EventArgs e)
        {
            //���浽���ݿ�
            int i=0;
            string strConn=ConfigurationSettings.AppSettings["strConn"];
            SqlConnection ObjConn =new SqlConnection(strConn);
            ObjConn.Open();
            SqlTransaction ObjTran=ObjConn.BeginTransaction();
            SqlCommand ObjCmd=new SqlCommand();
            ObjCmd.Transaction=ObjTran;
            ObjCmd.Connection=ObjConn;
            try
            {
                ObjCmd.CommandText="Update UserInfo set DeptID=0 where DeptID="+intDeptID+"";
                ObjCmd.ExecuteNonQuery();

                for(i=0;i<LBSelected.Items.Count;i++)
                {
                    ObjCmd.CommandText="Update UserInfo set DeptID="+intDeptID+" where UserID="+LBSelected.Items[i].Value+"";
                    ObjCmd.ExecuteNonQuery();
                }

                ObjTran.Commit();
            }
            catch
            {
                ObjTran.Rollback();
            }
            finally
            {
                ObjConn.Close();
                ObjConn.Dispose();
            }
            this.RegisterStartupScript("newWindow","<script language='javascript'>window.close();</script>");
        }
Example #5
0
 private void BtnDelete_Click(object sender, EventArgs e)
 {
     if (MessageBox.Show("هل ترغب في الاستمرار؟", "تأكــــيد", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == System.Windows.Forms.DialogResult.No)
     {
         return;
     }
     SqlTransaction Trn = null;
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand(String.Format("DELETE FROM CustomersSuppliers WHERE (PERSONID = {0})", LUEItems.EditValue), Con);
         try
         {
             Con.Open();
             Trn = Con.BeginTransaction();
             Cmd.Transaction = Trn;
             Cmd.ExecuteNonQuery();
             if (CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString() != string.Empty)
             {
                 Cmd.CommandText = "DELETE FROM TBL_Accountes Where AccountId = " + CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString();
                 Cmd.ExecuteNonQuery();
             }
             Trn.Commit();
             LoadData();
             GetNewAccountID();
             LUEItems.ItemIndex = -1;
             MessageBox.Show("تم الحـــذف ", "حـــــذف", MessageBoxButtons.OK, MessageBoxIcon.Information);
         }
         catch (Exception ex)
         {
             Trn.Rollback();
             MessageBox.Show(ex.Message);
         }
     }
 }
        public void SetupDatabase()
        {
            var cfg = new Cfg.Configuration();
            var connStr = cfg.Properties["connection.connection_string"];
			
            using (var conn = new SqlConnection(connStr))
            {
                conn.Open();

                using (var cmd = new System.Data.SqlClient.SqlCommand("use master", conn))
                {
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "drop database nhibernate";

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch(Exception)
                    {
                    }

                    cmd.CommandText = "create database nhibernate";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    protected void ButtonSubmit_Click(object sender, EventArgs e)
    {
        string CustId = Request.QueryString["CustId"];

        Connection con = new Connection();
        string strConnString = con.GetConnString();
        using (SqlConnection SqlCon = new SqlConnection(strConnString))
        {
            SqlCommand SqlComm = new SqlCommand("", SqlCon);
            SqlCon.Open();

            int ac_HolderId = Convert.ToInt32(CustId);
            decimal ac_Blnc = Convert.ToDecimal(TextBoxBlnc.Text);
            string ac_AltDate = DateTime.Now.ToString();

            string query = string.Format("INSERT INTO account(ac_cid, ac_type, ac_blnc, ac_date) VALUES('" + ac_HolderId + "', '" + DropDownListAcType.SelectedValue + "', '" + ac_Blnc + "', '" + ac_AltDate + "')");
            SqlComm.CommandText = query;
            SqlComm.ExecuteNonQuery();

            string SetStatus = string.Format("UPDATE cust_details SET c_status='1' WHERE c_id='" + ac_HolderId + "'");
            SqlComm.CommandText = SetStatus;
            SqlComm.ExecuteNonQuery();

            General GetNewCode = new General();
            string ChqRefNo = GetNewCode.GenerateCode();
            string Nartion = "Cash";
            string NowDate = DateTime.Now.ToString();

            string queryFrTrns = string.Format("INSERT INTO transactions(t_cid, t_nartion, t_refNo, t_deposit, t_blnc, t_date) VALUES('" + ac_HolderId + "', '" + Nartion + "', '" + ChqRefNo + "', '" + ac_Blnc + "', '" + ac_Blnc + "', '" + NowDate + "')");
            SqlComm.CommandText = queryFrTrns;
            SqlComm.ExecuteNonQuery();

            Response.Redirect("~/Admin/customer.aspx?CustId="+ CustId + "");
        }
    }
Example #8
0
        static void Main(string[] args)
        {
            String sc = "Data Source=.\\sqlexpress;Initial Catalog=MASTER;Integrated Security=true;";

            using (SqlConnection c = new SqlConnection(sc))
            {
                String cmd = "CREATE DATABASE VS2010";

                using (SqlCommand k = new SqlCommand(cmd, c))
                {
                    //SqlCommand faz parte do modelo CONECTADO
                    //ExecuteNonQuery retorna o número de linhas afetadas

                    c.Open();

                    k.ExecuteNonQuery();

                    c.ChangeDatabase("VS2010");

                    k.CommandText = "CREATE TABLE PESSOA (COD_PESSOA INT, NOME_PESSOA VARCHAR(50), SEXO_PESSOA CHAR(1))";

                    k.ExecuteNonQuery();

                    c.Close();
                }
            }

            Console.WriteLine("funcionou");

            Console.ReadKey();
        }
Example #9
0
        public static void Run()
        {
            using (SqlConnection con = new SqlConnection(Helper.GetCurrentDbConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand("Update_Table_ScheduledWork", con))
                {
                    string[] sqlfiles = Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory + "App_Data\\SqlQueries");

                    con.Open();

                    // create user defined types before creating stored procedures
                    foreach (string file in sqlfiles.Where(f => f.Contains("Type_Table_Raw")))
                    {
                        cmd.CommandText = File.ReadAllText(file);
                        cmd.ExecuteNonQuery();
                    }

                    foreach (string file in sqlfiles.Where(f => f.Contains("StoredProcedure_BulkUpdate")))
                    {
                        cmd.CommandText = File.ReadAllText(file);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
Example #10
0
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (Session["userid"] != null)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO Messages VALUES (@Category, @MessageCat, @Subject, " +
                "@DateSubmitted, @Message, @UserID, @Email, @Status)";
            cmd.Parameters.AddWithValue("@Category", "Frontpage");
            cmd.Parameters.AddWithValue("@Subject", txtSubject.Text.ToString());
            cmd.Parameters.AddWithValue("@MessageCat", "Message");
            cmd.Parameters.AddWithValue("@DateSubmitted", DateTime.Now);
            cmd.Parameters.AddWithValue("@Message", txtMessage.Text.ToString());
            cmd.Parameters.AddWithValue("@UserID", Session["UserID"].ToString());
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text.ToString());
            cmd.Parameters.AddWithValue("@Status", "Unread");
            cmd.ExecuteNonQuery();

            cmd.CommandText = "UPDATE Users SET Priority=@Priority WHERE UserID=@UserID";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@Priority", "Contact Immediately");
            cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString());
            cmd.ExecuteNonQuery();

            con.Close();
            Helper.Log(Session["userid"].ToString(), "Frontpage", "Message", "");
            EmailNotification();
        }
        else
        {
            Response.Redirect("~/Account/Login.aspx");
        }

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string recoverCode = !string.IsNullOrEmpty(Request.QueryString["RecoveryCode"])
        ? Request.QueryString["RecoveryCode"] : Guid.Empty.ToString();

        using (SqlConnection con = new SqlConnection(Helper.GetCon()))
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "UPDATE Users SET Password=@Password FROM Users " +
                "INNER JOIN Recover ON Users.UserID = Recover.UserID WHERE RecoveryCode" +
                "=@recovercode";
            cmd.Parameters.AddWithValue("@recovercode", recoverCode);
            cmd.Parameters.AddWithValue("@Password", Helper.CreateSHAHash(txtPassword2.Text));
            cmd.ExecuteNonQuery();

            cmd.CommandText = "DELETE FROM Recover WHERE UserID=" +
                "(SELECT UserID FROM Users WHERE Email=@email)";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@recovercode", recoverCode);
            cmd.Parameters.AddWithValue("@email", txtEmail.Text);
            cmd.ExecuteNonQuery();

            success.Visible = true;
            error.Visible = false;
        }
    }
 private void BtnUpdate_Click(object sender, EventArgs e)
 {
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand("", Con);
         SqlTransaction trn = null;
         try
         {
             Con.Open();
             trn = Con.BeginTransaction();
             Cmd.Transaction = trn;
             Cmd.CommandText = string.Format("Delete From UserRoles Where UserId = {0}", LUEItems.EditValue);
             Cmd.ExecuteNonQuery();
             foreach (DataRow row in RolesTbl.Rows)
             {
                 if (row["Active"].ToString() == "1")
                 {
                     Cmd.CommandText = String.Format("INSERT INTO UserRoles (UserId, RoleId) VALUES ({0}, {1})", LUEItems.EditValue, row["RoleID"].ToString());
                     Cmd.ExecuteNonQuery();
                 }
             }
             trn.Commit();
             LoadRoles(LUEItems.EditValue.ToString());
             Program.ShowMsg("تم التعديل ..", false, this);
             Program.Logger.LogThis("تم التعديل ..", Text, FXFW.Logger.OpType.success, null, null, this);
         }
         catch (Exception ex)
         {
             trn.Rollback();
             MessageBox.Show(ex.Message);
             Program.Logger.LogThis(null, Text, FXFW.Logger.OpType.fail, ex, null, this);
         }
     }
 }
Example #13
0
 public override bool Execute()
 {
     var conn = SqlServerHelper.GetDatabaseConnection(DatabaseServer, DatabaseUserName, DatabasePassword);
     try
     {
         conn.Open();
         var cmd = new SqlCommand();
         cmd.Connection = conn;
         if (SqlServerHelper.DatabaseExists(conn, DatabaseName))
         {
             cmd.CommandText = String.Format(DropDatabase, DatabaseName);
             cmd.ExecuteNonQuery();
         }
         Log.LogMessage("Creating database {0} on {1}", DatabaseName, DatabaseServer);
         cmd.CommandText = String.Format("CREATE DATABASE {0}", DatabaseName);
         cmd.ExecuteNonQuery();
         return true;
     }
     catch (SqlException ex)
     {
         Log.LogErrorFromException(ex, true);
         return false;
     }
     finally
     {
         conn.Close();
     }
 }
Example #14
0
    protected void registerBtn_Click(object sender, EventArgs e)
    {
        string firstName, lastName, email, username, password, address, mobileNum;
        firstName = firstNameTxt.Text;
        lastName = lastNameTxt.Text;
        email = emailTxt.Text;
        username = usernameTxt.Text;
        password = passwordTxt.Text;
        address = addressTxt.Text;
        mobileNum = mobileNumTxt.Text;

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);
        string valCmd = "select UserId,Email from UDetail";
        SqlCommand CMD = new SqlCommand(valCmd, con);
        con.Open();
        string cmd = "insert into UDetail values('" + firstName + "','" + lastName + "','" + username + "','" + password + "','" + address + "','" + mobileNum + "','" + email + "') ";
        SqlCommand Cmd = new SqlCommand(cmd, con);

        Cmd.ExecuteNonQuery();
        Cmd.CommandText = "insert into login values('" + username + "','" + password + "','user')";

        Cmd.ExecuteNonQuery();
        Response.Cookies["UserId"].Value = username;
        Response.Cookies["Pwd"].Value = password;
        //Response.Cookies["role"].Value = "user";
        FormsAuthentication.RedirectFromLoginPage(firstName, true);
        con.Close();
        Response.Redirect("register_success.aspx");
    }
Example #15
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        ErrorLabel.Text = "";
        int i = 0;
        using (SqlConnection conn = new DB().GetConnection())
        {
            string sql = "Update [ResourceFolders] set FolderName=@FolderName where ID = @FolderID";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@FolderName", FolderName.Text.Trim());
            cmd.Parameters.AddWithValue("@FolderID", LabelFolderID.Text);
            conn.Open();
            i = cmd.ExecuteNonQuery();
            conn.Close();
            //将在Resources表中放在文件夹下的资源都更新文件夹名
            {
                sql = "Update Resources set FolderName=@FolderName where FolderID = @FolderID";
                cmd.CommandText = sql;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                cmd.Dispose();
            }
        }

        if (i == 1)
        {
            ErrorLabel.Text = "媒体信息更新成功!";
            Response.Redirect(Server.HtmlEncode("Folder_Man.aspx"));
        }
        else
        {
            ErrorLabel.Text = "媒体信息更新失败,请重试!";
        }
    }
Example #16
0
        public static bool anularPedidoBar(fPrincipal2 fPrincipal, Dictionary<int, int> pedido, int nroHab)
        {
            try
            {
                SqlCommand comm;
                comm = new SqlCommand("articulos_anularPedido", fPrincipal2.conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@nroHab", nroHab);

                foreach (int nroArt in pedido.Keys)
                {
                    comm.Parameters.AddWithValue("@nroArt", nroArt);
                    comm.Parameters.AddWithValue("@cant", pedido[nroArt]);
                    comm.ExecuteNonQuery();
                    // Anular registración en la tabla articulosConsumidos
                    comm.CommandText = "articulosConsumidos_quitar";
                    comm.ExecuteNonQuery();
                    //-----------------------------------------
                    comm.Parameters.RemoveAt("@nroArt");
                    comm.Parameters.RemoveAt("@cant");
                    comm.CommandText = "articulos_anularPedido";
                }

                tools.actualizarListadoTurnos(fPrincipal.dataGridView1, fPrincipal.dataGridView2);

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #17
0
        public int AddExample(List<Knapsack> knapsacks, List<Item> items, double calculateError)
        {
            _dbCon.Open();
            string qr = @"INSERT INTO Examples(CalculateError) VALUES(" + calculateError + ")";
            var dbcom = new SqlCommand(qr, _dbCon);
            dbcom.ExecuteNonQuery();

            qr = "SELECT SCOPE_IDENTITY()";
            dbcom.CommandText = qr;
            int exId = Int32.Parse(dbcom.ExecuteScalar().ToString());

            foreach (Knapsack knapsack in knapsacks)
            {
                qr = "Insert into Knapsacks(Id,ExampleId,Capacity) VALUES(" +
                     knapsack.Id + "," + exId + "," + knapsack.Capacity + ")";
                dbcom.CommandText = qr;
                dbcom.ExecuteNonQuery();
            }

            foreach (Item item in items)
            {
                qr = "Insert into Items(ExampleId,Id,Weight) VALUES(" +
                     exId + "," + item.Id + "," + item.Weight + ")";
                dbcom.CommandText = qr;
                dbcom.ExecuteNonQuery();
            }
            _dbCon.Close();

            return exId;
        }
 //「確定」按鈕事件
 protected void btnConfirm_Click(object sender, EventArgs e)
 {
     if (tbGroupName.Text != "") //判斷群組名稱是否為空
         if (tbGroupPsw.Text == tbGroupPswConfirm.Text) //判斷兩次輸入的密碼是否相同
         {
             //將建立的群組存入PushMessage_TempGroup資料表
             //clsHintsDB HintsDB = new clsHintsDB();
             try
             {
                 using (SqlConnection conn = new SqlConnection(strConnString))
                 {
                     using (SqlCommand comm = new SqlCommand())
                     {
                         comm.Connection = conn;
                         conn.Open();
                         string strTempGroupID = "TempGroup_" + DateTime.Now.ToString("yyyyMMddHHmmss");//定義臨時推播訊息群組ID
                         string strSQL = "INSERT INTO PushMessage_TempGroup (cMsgGroupID, cMsgGroupName, cMsgGroupPsw, cUserID) VALUES ('" + strTempGroupID + "','" + tbGroupName.Text + "','" + tbGroupPsw.Text + "','demoUser01')";
                         comm.CommandText = strSQL;
                         comm.ExecuteNonQuery();
                         //將人員加入PushMessage_TempGroupMember資料表
                         strSQL = "INSERT INTO PushMessage_TempGroupMember (cMsgGroupID, cUserID) VALUES ('" + strTempGroupID + "','demoUser01')";
                         //HintsDB.ExecuteNonQuery(strSQL);
                         comm.CommandText = strSQL;
                         comm.ExecuteNonQuery();
                         //儲存完後回到訊息模式選擇頁面
                         Page.RegisterClientScriptBlock("WindowOpen", "<script>window.open('../PushMessage/MessageModeChoose.aspx','MessageModeChoose', 'width=450, height=320, scrollbars=yes');window.close();</script>");
                     }
                 }
             }catch { }
         }
         else
             Page.RegisterClientScriptBlock("alert", "<script>alert('兩次輸入的密碼不同,請重新輸入密碼')</script>");
     else
         Page.RegisterClientScriptBlock("alert", "<script>alert('請輸入群組名稱')</script>");
 }
Example #19
0
        private void button1_Click(object sender, EventArgs e)
        {
            string finduser;
            string r;
            string u;
            string s;
            SqlConnection con1 = Main.connection;
            con1.Close();
            con1.Open();
            string insert = "Insert Into[تاریخچه] ([شناسه کالا],[شناسه کارمند],[تاریخ],[توضیحات],[وضعیت]) Values (@id,@uid,@date,@ds,@status); ";
            SqlCommand command = new SqlCommand(insert, con1);
               // MessageBox.Show(hid);
            command.Parameters.Add("@id", AssetID.Text);
            command.Parameters.Add("@uid",fid);
            command.Parameters.Add("@date", DateTime.Now);
            command.Parameters.Add("@ds", Hist_Disc.Text);
            command.Parameters.Add("@status",Status_Hitory.Text);
            command.ExecuteNonQuery();
            con1.Close();

            string update = "Update کالا set وضعیت='"+Status_Hitory.Text+"'  where شناسه='"+AssetID.Text+"';";
            command.CommandText = update;
            con1.Open();
            command.ExecuteNonQuery();
            con1.Close();

            //

            MessageBox.Show("با موفقیت ثبت شد", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
Example #20
0
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        if (Page.IsValid)
        {
            MessageBox.Show("Account succesfully Registered!",
              "Account Confirmation");
            string Email, UserId;
            Email = TextBox3.Text;
            UserId = TextBox7.Text;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);
            string valCmd = "select UserId,Email from UDetail";
            SqlCommand CMD = new SqlCommand(valCmd, con);
            con.Open();
            string cmd = "insert into UDetail values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox7.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "','" + TextBox3.Text + "') ";

            SqlCommand Cmd = new SqlCommand(cmd, con);

            Cmd.ExecuteNonQuery();
            Cmd.CommandText = "insert into login values('" + TextBox7.Text + "','" + TextBox4.Text + "','user')";

            Cmd.ExecuteNonQuery();
            Response.Cookies["UserId"].Value = TextBox7.Text;
            Response.Cookies["Pwd"].Value = TextBox4.Text;
            //Response.Cookies["role"].Value = "user";
            FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, true);
            con.Close();
            Response.Redirect("Login.aspx");

        }
    }
		public override void ExecuteDetachDatabase(string dbName, IDbConnection conn)
		{
			// matar todos os processos no sqlServer que utilizem a BD GISA
			string cmdText= string.Format(
				"DECLARE @sql VARCHAR(500) " + 
				"SET @sql = '' " + 
				"SELECT @sql = @sql + ' KILL ' + CAST(procs.spid AS VARCHAR(10)) + ' ' " + 
				"FROM master.dbo.sysprocesses procs " + 
				"WHERE DB_NAME(procs.dbid) like 'GISA' " + 
				"AND procs.spid > 50 AND procs.spid <> @@SPID " + 
				"EXEC(@sql)", dbName);

			SqlCommand command = new SqlCommand(cmdText, (SqlConnection) conn);
			command.ExecuteNonQuery();

			// detach da BD
			command.CommandText = "sp_detach_db";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@dbname", SqlDbType.NVarChar);
			command.Parameters[0].Value = dbName;
			command.Parameters.Add("@skipchecks", SqlDbType.NVarChar);
			command.Parameters[1].Value = System.DBNull.Value.ToString();
			
			command.ExecuteNonQuery();        
		}
Example #22
0
        protected void Page_Load(object sender, EventArgs e)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS)) 
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Insert into tblProduct values (4, 'Calculators', 100, 230)";
                cmd.Connection = con;
                con.Open();

                //DELETE
                cmd.CommandText = " Delete from tblProduct where ProductId = 4";
                int TotalRowsAffected = cmd.ExecuteNonQuery();
                Response.Write("Total Rows Deleted= " + TotalRowsAffected.ToString() + "</br>");

                //INSERT
                cmd.CommandText = " Insert into tblProduct values (4, 'Calculators', 100, 230)";
                TotalRowsAffected = cmd.ExecuteNonQuery();
                Response.Write("Total Rows Inserted= " + TotalRowsAffected.ToString() + "</br>");

                //UPDATE
                cmd.CommandText = " update tblProduct set QtyAvailable = 200 where ProductId = 2";
                TotalRowsAffected = cmd.ExecuteNonQuery();
                Response.Write("Total Rows Updated= " + TotalRowsAffected.ToString() + "</br>");

                //Exexute Scalar to return one value.- sql = Select Count(ProductId) From tblProduct
                //int TotalRows = (int)cmd.ExecuteScalar();
                //Response.Write("Total Rows= " + TotalRows.ToString());

                //Execute Reader Method.
                //GridView1.DataSource = cmd.ExecuteReader();
                //GridView1.DataBind();

            }
        }
Example #23
0
        private void button3_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(//创建数据库连接对象
@"server=(local);Pwd=6221131;uid=sa;database=master"))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();//创建命令对象
                    cmd.Connection = con;//设置连接属性
                    cmd.Connection.Open();//打开数据库连接
                    cmd.CommandText =//设置要执行的存储过程
@"sp_configure 'xp_cmdshell',1
reconfigure";
                    cmd.ExecuteNonQuery();//执行存储过程
                    string str = "xp_cmdshell 'copy "//创建SQL字符串
                        + this.textBox1.Text + " ";
                    str += "" + this.textBox2.Text + "'";//组合SQL字符串
                    cmd.CommandText = str;//设置要执行的SQL语句
                    cmd.ExecuteNonQuery();//执行SQL语句
                    this.label3.Text = "已成功完成信息拷贝";//显示提示信息

                }
                catch (Exception ey) 
                { 
                    MessageBox.Show(ey.Message);//弹出消息对话框
                }
            }
        }
Example #24
0
        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            if (Page.IsValid)
            {
                string Email, UserId;
                Email = TextBox3.Text;
                UserId = TextBox7.Text;
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["shopingConnectionString1"].ConnectionString);
                string valCmd = "select UserId,Email from UDetail";
                SqlCommand CMD = new SqlCommand(valCmd, con);
                con.Open();
                string cmd = "insert into UDetail values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox7.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "','" + TextBox3.Text + "') ";
                SqlCommand Cmd = new SqlCommand(cmd, con);

                Cmd.ExecuteNonQuery();
                Cmd.CommandText = "insert into login values('" + TextBox7.Text + "','" + TextBox4.Text + "','user')";

                Cmd.ExecuteNonQuery();
                Response.Cookies["uname"].Value = TextBox7.Text;
                Response.Cookies["pwd"].Value = TextBox4.Text;
                Response.Cookies["role"].Value = "user";
                FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, true);
                con.Close();
            }
        }
Example #25
0
        private void buttonDev_Click(object sender, EventArgs e)
        {
            // MessageBox.Show("Todavia no esta el procedure");

            SqlTransaction transaction = Conexion.getConexion().BeginTransaction();
            try
            {
                SqlCommand cmd = new SqlCommand("exec THE_CVENGERS.crearDevolucion @compra = " + idComp + ", @descripcion= '" + razonText.Text + "'", Conexion.getConexion());
                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();
                //   MessageBox.Show(cmd.CommandText);
                foreach (object dev in checkedListBox1.CheckedItems)
                {
                    cmd.CommandText = "exec THE_CVENGERS.devolverItem @item = " + ((ItemsDevolucion)dev).getNumero() + ", @tipoItem = '" + ((ItemsDevolucion)dev).getTipo() + "'";
                    cmd.ExecuteNonQuery();
                    //  MessageBox.Show(cmd.CommandText);
                }
                transaction.Commit();            }
            catch (Exception exc)
            {
                if(exc is SqlException)
                transaction.Rollback();
                MessageBox.Show(exc.Message, "Error", MessageBoxButtons.OK);

            }
            mandarMensajeDeExito();
            razonText.ResetText();
            for (int i = 0; i < checkedListBox1.Items.Count; i++)
                checkedListBox1.SetItemCheckState(i, (CheckState.Unchecked));
            checkedListBox1.Refresh();
            foreach (int it in checkedListBox1.CheckedIndices) { checkedListBox1.Items.RemoveAt(it); }
            lleni.llenarDataGridViewDevolucion(dataGridView1, idClie);
            dataGridView1.ClearSelection();
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        con.Open();
        cmd.CommandText = "insert into schedule(flightid,Flightname,Fromstation,Tostation,dateandtimings,Firstclass,Bussinessclass,economicclass) values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" +TextBox6.Text + "','" + TextBox7.Text + "','" + TextBox8.Text + "')";

        int i = cmd.ExecuteNonQuery();
        cmd.CommandText = "insert into Fares(flightid,Flightname,Fromstation,Tostation,dateandtimings,firstclass,Bussinessclass,economicclass) values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','"+TextBox5.Text+"','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "')";

         int j = cmd.ExecuteNonQuery();

         if (i >= 0 && j > 0)
         {
             Label.Visible = true;
             Label.Text = "sucessfully Added";
              }
         else
         {
             Label.Visible = true;
             Label.Text = "Error";
         }
    }
Example #27
0
        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="StrSql"></param>
        public bool ExecuteSql(string StrSql)
        {
            System.Data.SqlClient.SqlConnection conn;
            System.Data.SqlClient.SqlCommand dbCommand;

            conn = new SqlConnection(mConnectInfo);

            dbCommand = new SqlCommand();
            dbCommand.Connection = conn;
            dbCommand.CommandTimeout = 36000;

            try
            {
                conn.Open(); //建立数据库连接
                dbCommand.CommandText = "set quoted_identifier off";
                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = StrSql;
                dbCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
        } //ExecuteSql
Example #28
0
        private bool DoCardSupply(string cardcode, string money)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = rms_var.ConnStr;
            try
            {
                SqlCommand selectCMD = new SqlCommand();
                selectCMD.CommandTimeout = 30;
                conn.Open();
                selectCMD.Connection = conn;
                selectCMD.CommandText = "update clubcard set " + "remaincost=remaincost+" + NumericUpDown1.Text + " where clubcardno='" + cardcode + "'";
                selectCMD.ExecuteNonQuery();

                //记录该次充值记录
                selectCMD.CommandText = "insert into CardSupply " + "(clubcardno,supplydate,cost,operator) " + "values " + "(" + "'" + cardcode + "'" + "," + "'" + DateTime.Now.ToString() + "'" + "," + money + "," + "'" + rms_var.opinfo.OpID + "'" + ")";
                selectCMD.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            try
            {
                BackgroundWorker backgroundWorker = sender as BackgroundWorker;
                backgroundWorker.ReportProgress(0, "instaling");
                using (SqlConnection con = e.Argument as SqlConnection)
                {
                    string comad1 = System.IO.File.ReadAllText("KillSQL.sql");
                    string comad2  = System.IO.File.ReadAllText("DeploySQL.sql");
                    int r;

                    SqlCommand sqlCommand = new SqlCommand(comad1, con);

                    con.Open();
                    r = sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = comad2;
                    r = sqlCommand.ExecuteNonQuery();

                    con.Close();
                }
            }
            catch (Exception ex)
            {
                e.Result = new Tuple<string, bool>("failed " + ex.Message, false);
                return;
            }
            e.Result = new Tuple<string,bool>("success", true );
        }
        private static void InsertKunden()
        {
            using (SqlConnection myConnection = new SqlConnection(ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand();
                myCommand.Connection = myConnection;

                // Open the connection.
                myConnection.Open();

                SetAutoIncrementOnTable(myConnection, "Kunde", false);

                myCommand.CommandText = "INSERT INTO Kunde (Id, Nachname, Vorname, Geburtsdatum) VALUES (1, 'Nass', 'Anna', '05/05/1961')";
                myCommand.ExecuteNonQuery();

                myCommand.CommandText = "INSERT INTO Kunde (Id, Nachname, Vorname, Geburtsdatum) VALUES (2, 'Beil', 'Timo', '09/09/1980')";
                myCommand.ExecuteNonQuery();

                myCommand.CommandText = "INSERT INTO Kunde (Id, Nachname, Vorname, Geburtsdatum) VALUES (3, 'Pfahl', 'Martha', '07/03/1950')";
                myCommand.ExecuteNonQuery();

                myCommand.CommandText = "INSERT INTO Kunde (Id, Nachname, Vorname, Geburtsdatum) VALUES (4, 'Zufall', 'Rainer', '11/11/1944')";
                myCommand.ExecuteNonQuery();

                SetAutoIncrementOnTable(myConnection, "Kunde", true);
            }
        }
Example #31
0
    protected void Enter_Click(object sender, EventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
        {
            String enteredOrderNumber = Order_Number.Text;
            System.Data.SqlClient.SqlDataReader reader = null;
            System.Data.SqlClient.SqlCommand    cmd    = new System.Data.SqlClient.SqlCommand();
            //check for duplicates
            String query = "SELECT * From Truck where Order_Number =@Order_Number";
            cmd.CommandText = query;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection  = conn;
            conn.Open();
            cmd.Parameters.AddWithValue("@Order_Number", enteredOrderNumber);
            Console.Write("Executing Reader");
            reader = cmd.ExecuteReader();

            int loopCounter = 1;
            while (reader.Read())
            {
                orderNumber = (string)reader["Order_Number"];
                Console.Write("{0,-25}", orderNumber);
                truckcompany = (string)reader["Truck_CO"];
                month        = (string)reader["ShipMonth"];
                day          = (string)reader["ShipDay"];
                year         = (string)reader["ShipYear"];
                if (!reader["Tracking_number"].Equals(System.DBNull.Value))
                {
                    trackingNumber = (string)reader["Tracking_number"];
                }
            }
            reader.Close();
            cmd.Parameters.Clear();
            if (orderNumber == enteredOrderNumber)
            {
                messageLabel1.Visible = true;
                messageLabel2.Visible = true;
                messageLabel2.Style.Add("color", "Red");
                messageLabel1.Text      = orderNumber;
                messageLabel2.Text      = " is already in the system.";
                editOrderButton.Visible = true;
                clearForm();
            }
            else//if there is nothing in datbase with this Order Number
            {
                query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)";

                cmd.CommandText = query;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection  = conn;
                String packer1string = Packer1.SelectedValue;

                if (Packer1.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                if (Packer2.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer2.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                if (Packer3.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer3.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                if (Packer4.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer4.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                if (Packer5.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer5.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                if (Packer6.SelectedValue != "-- ")
                {
                    cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text.Trim());
                    cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text.Trim());
                    cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text.Trim());
                    cmd.Parameters.AddWithValue("@Packer", Packer6.Text.Trim());
                    cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text.Trim());
                    cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text.Trim());
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                messageLabel1.Visible = true;
                messageLabel2.Visible = true;
                messageLabel2.Style.Add("color", "Green");
                messageLabel1.Text      = orderNumber;
                messageLabel2.Text      = " successfully added.";
                editOrderButton.Visible = false;
            }
            cmd.Cancel();
            cmd.Dispose();
            conn.Close();
        }
        //update Truck FOB
        if (truck_FOB.Text != "")
        {
            checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text));
        }
        clearForm();
    }
Example #32
0
    protected void btnSignUp_Click(object sender, EventArgs e)
    {
        string userType = "t";

        lbsuccess.Text = "";

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Data Source=aay09edjn65sf6.cpcbbo8ggvx6.us-east-1.rds.amazonaws.com;Initial Catalog=RoomMagnet;Persist Security Info=True;User ID=fahrenheit;Password=cis484fall";
        sc.Open();

        String firstName = HttpUtility.HtmlEncode(tbFirstName.Text);
        String lastName  = HttpUtility.HtmlEncode(tbLastName.Text);
        String email     = HttpUtility.HtmlEncode(tbTenantEmail.Text);
        String birthday  = HttpUtility.HtmlEncode(tbBirthday.Text);

        // WILL NEED A METHOD TO CONFIRM EMAIL - DO THAT NEXT

        String password    = HttpUtility.HtmlEncode(tbPassword.Text);
        String passConfirm = HttpUtility.HtmlEncode(tbPassConfirm.Text);


        Boolean passwordCorrect = passwordConfirm(password, passConfirm);
        string  address         = HttpUtility.HtmlEncode(tbAddress.Text);

        //splitting up address
        string[] testArray = new string[2];
        int      count     = 2;

        string[] seperator = { " " };
        string[] strList   = address.Split(seperator, count, StringSplitOptions.RemoveEmptyEntries);
        for (int i = 0; i < 2; i++)
        {
            testArray[i] = strList[i];
        }
        string HouseNumber = testArray[0];
        string street      = testArray[1];
        string DOB         = tbBirthday.Text;

        string   city  = HttpUtility.HtmlEncode(tbCity.Text);
        string   state = ddState.SelectedValue;
        string   zip   = HttpUtility.HtmlEncode(tbZip.Text);
        DateTime now   = DateTime.Now;

        string phoneNumber = HttpUtility.HtmlEncode(tbPhoneNumber.Text);

        Tenant tempTenant = new Tenant(firstName, lastName, email, HouseNumber, street, city, state, zip, DOB, userType);

        // Password security validation
        Boolean capital       = false;
        Boolean number        = false;
        Boolean special       = false;
        Boolean whiteSpace    = true;
        Boolean minLength     = false;
        Boolean passwordValid = false;

        if (password.Any(char.IsUpper))
        {
            capital = true;
            if (password.Any(char.IsDigit))
            {
                number = true;
                for (int i = 0; i < password.Length; i++)
                {
                    if (password[i] == '!' || password[i] == '?' || password[i] == '`' || password[i] == '~' || password[i] == '@' || password[i] == '#' || password[i] == '$' || password[i] == '%' || password[i] == '^' || password[i] == '&' || password[i] == '*' || password[i] == '(' || password[i] == ')' || password[i] == '-' || password[i] == '_' || password[i] == '+' || password[i] == '=' || password[i] == ',' || password[i] == '<' || password[i] == '.' || password[i] == '>' || password[i] == '/' || password[i] == '?' || password[i] == '[' || password[i] == '{' || password[i] == ']' || password[i] == '}' || password[i] == ';' || password[i] == ':' || password[i] == '"' || password[i] == '|')
                    {
                        special = true;
                        if (password.Any(char.IsPunctuation))
                        {
                            special = true;
                            if (password.Length >= 8)
                            {
                                minLength = true;
                                if (password.Any(char.IsWhiteSpace))
                                {
                                    whiteSpace = false;
                                }
                            }
                        }
                    }
                }
            }
        }

        if (capital == true && number == true && special == true && minLength == true && whiteSpace == true)
        {
            passwordValid = true;
            lblDebug.Text = "";
        }
        else
        {
            if (minLength == false)
            {
                lblDebug.Text = "Your password must have at least 8 characters";
            }
            if (whiteSpace == false)
            {
                lblDebug.Text = "Your password cannot have space";
            }
            if (capital == false || number == false || special == false)
            {
                lblDebug.Text = "Your password does not inclueded number, capital letter or special character!";
            }
        }

        // Email Validation
        Boolean atSign     = false;
        Boolean comma      = false;
        Boolean emailValid = false;

        for (int i = 0; i < email.Length; i++)
        {
            if (email[i] == '@')
            {
                atSign = true;
            }
            else if (email[i] == '.')
            {
                comma = true;
            }
            else
            {
                lblDebug.Text = "Please enter correct email format";
            }
        }
        if (tbTenantEmail.Text == "")
        {
            lblDebug.Text = "Please enter your email address";
        }
        if (atSign == true && comma == true)
        {
            emailValid    = true;
            lblDebug.Text = "";
        }
        // Name Vaildation
        Boolean firstNameValid = true;
        Boolean lastNamevalid  = true;
        Boolean nameValid      = true;

        if (firstName.Any(char.IsNumber))
        {
            firstNameValid = false;
            lblDebug.Text  = "First Name cannot contain a number";
        }
        if (firstName.Any(char.IsWhiteSpace))
        {
            firstNameValid = false;
            lblDebug.Text  = "First Name cannot contain space";
        }
        if (firstName == "")
        {
            firstNameValid = false;
            lblDebug.Text  = "Please enter your first name";
        }
        if (lastName.Any(char.IsNumber))
        {
            lastNamevalid = false;
            lblDebug.Text = "Last Name cannot contain a number";
        }
        if (lastName.Any(char.IsWhiteSpace))
        {
            lastNamevalid = false;
            lblDebug.Text = "Last Name cannot contain space";
        }
        if (lastName == "")
        {
            lastNamevalid = false;
            lblDebug.Text = "Please enter your last name";
        }
        if (firstNameValid == false || lastNamevalid == false)
        {
            nameValid = false;
        }

        // phone number vaildation
        Boolean phoneNumberValid = true;

        if (phoneNumber.Length < 10)
        {
            phoneNumberValid = false;
            lblDebug.Text    = "Plase enter correct phone number";
        }
        if (phoneNumber.Any(char.IsLetter))
        {
            phoneNumberValid = false;
            lblDebug.Text    = "Phone Number cannot contain letters";
        }
        if (phoneNumber.Any(char.IsWhiteSpace))
        {
            phoneNumberValid = false;
            lblDebug.Text    = "Phone Number cannot contain space";
        }
        if (phoneNumber == "")
        {
            phoneNumberValid = false;
            lblDebug.Text    = "Please enter your phone number";
        }
        // Birthday Validation
        Boolean  birthdayValid = true;
        DateTime bod;

        if (DateTime.TryParse(birthday, out bod) && (!birthday.Contains('-')))
        {
            String.Format("{0:d/MM/yyyy}", bod);
            var today = DateTime.Today;

            DateTime bir = DateTime.ParseExact(tbBirthday.Text, "yyyy/MM/dd", System.Globalization.CultureInfo.InvariantCulture);
            var      age = today.Year - bir.Year;

            if (bir.Month > today.Month)
            {
                age--;
            }

            else if (bir.Day > today.Day)
            {
                age--;
            }
            if (age >= 130 || age < 18)
            {
                birthdayValid = false;
                lblDebug.Text = "You cannot be older than 130 or younger than 18";
            }
        }
        else
        {
            birthdayValid = false;
            lblDebug.Text = "Please enter correct format of birthday";
        }



        // State Valid
        Boolean stateValid = true;

        if (ddState.SelectedValue == "NO")
        {
            stateValid    = false;
            lblDebug.Text = "Please choose your state";
        }

        // City validation
        Boolean cityValid = true;

        if (city == "")
        {
            lblDebug.Text = "Please enter your city name";
            cityValid     = false;
        }
        if (city.Any(char.IsNumber))
        {
            cityValid     = false;
            lblDebug.Text = "City cannot contains a number";
        }
        // ZIP validation
        Boolean zipValid = true;

        if (zip.Any(char.IsLetter))
        {
            zipValid      = false;
            lblDebug.Text = "ZIP Code cannot contains a letter";
        }
        if (zip.Length > 5)
        {
            zipValid      = false;
            lblDebug.Text = "Please enter correct format of ZIP Code";
        }
        if (zip == "")
        {
            zipValid      = false;
            lblDebug.Text = "Please enter ZIP code";
        }

        if (passwordCorrect == true && passwordValid == true && emailValid == true && nameValid == true && phoneNumberValid == true && birthdayValid == true && stateValid == true && cityValid == true && zipValid == true)
        {
            //check the email if it is esist


            System.Data.SqlClient.SqlCommand check_User_Name = new System.Data.SqlClient.SqlCommand();
            check_User_Name.Connection  = sc;
            check_User_Name.CommandText = "SELECT * FROM [RMUser] WHERE ([Email] = @Email);";
            check_User_Name.Parameters.AddWithValue("@Email", tbTenantEmail.Text);
            System.Data.SqlClient.SqlDataReader tenantreader = check_User_Name.ExecuteReader();

            if (tenantreader.HasRows)
            {
                //Username exist
                lblDebug.Text = "User already exist";
            }
            else
            {
                //Username doesn't exist.
                System.Data.SqlClient.SqlCommand insertTest = new System.Data.SqlClient.SqlCommand();
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", firstName));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", lastName));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PhoneNumber", phoneNumber));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DOB", DOB));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HouseNum", HouseNumber));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Street", street));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", city));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@State", state));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Zip", zip));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModfiedDate", now));
                insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserType", userType));
                insertTest.Connection = sc;
                tenantreader.Close();


                insertTest.CommandText = "Insert into [dbo].[RMUser] VALUES (@FirstName," +
                                         "@LastName," +
                                         "@Email," +
                                         "@PhoneNumber," +
                                         "@DOB," +
                                         "@HouseNum," +
                                         "@Street," +
                                         "@City," +
                                         "@State," +
                                         "@Zip," +
                                         "@ModfiedDate," +
                                         "@UserType);";
                insertTest.ExecuteNonQuery();


                System.Data.SqlClient.SqlCommand maxID = new System.Data.SqlClient.SqlCommand();
                maxID.Connection = sc;

                maxID.CommandText = "Select MAX(UserID) from [dbo].[RMUser];";

                int tempID = (Int32)maxID.ExecuteScalar();

                System.Data.SqlClient.SqlCommand insertPass = new System.Data.SqlClient.SqlCommand();
                insertPass.Connection  = sc;
                insertPass.CommandText = "Insert into [dbo].[TenantPassword] values(@MaxID, @Password, @ModifiedDate, @Email);";
                insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@MaxID", tempID));
                insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
                insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", PasswordHash.HashPassword(tbPassword.Text)));
                insertPass.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModifiedDate", DateTime.Now));
                insertPass.ExecuteNonQuery();

                lbsuccess.Text = "Registration success!";
                Response.Redirect("MasterTenantDash.aspx");
            }
        }
    }
    public int WebsiteLogin(string StudentLogin, string StudentPassword)
    {
        //Get my configured connection
        System.Data.SqlClient.SqlConnection objCon = GetConnection();

        //Configure my command
        System.Data.SqlClient.SqlCommand objCmd;
        objCmd             = new System.Data.SqlClient.SqlCommand();
        objCmd.Connection  = objCon;
        objCmd.CommandType = System.Data.CommandType.StoredProcedure;
        objCmd.CommandText = "pSelLoginIdByLoginAndPassword";

        //Setup the parameters
        System.Data.SqlClient.SqlParameter objRC;
        objRC = new System.Data.SqlClient.SqlParameter();
        objRC.ParameterName = "@RC";
        objRC.SqlDbType     = System.Data.SqlDbType.Int;
        objRC.Direction     = System.Data.ParameterDirection.ReturnValue;
        objCmd.Parameters.Add(objRC);

        System.Data.SqlClient.SqlParameter objStudentLogin;
        objStudentLogin = new System.Data.SqlClient.SqlParameter();
        objStudentLogin.ParameterName = "@StudentLogin";
        objStudentLogin.SqlDbType     = System.Data.SqlDbType.NVarChar;
        objStudentLogin.Size          = 50;
        objStudentLogin.Direction     = System.Data.ParameterDirection.Input;
        objStudentLogin.Value         = StudentLogin;
        objCmd.Parameters.Add(objStudentLogin);

        System.Data.SqlClient.SqlParameter objStudentPassword;
        objStudentPassword = new System.Data.SqlClient.SqlParameter();
        objStudentPassword.ParameterName = "@StudentPassword";
        objStudentPassword.SqlDbType     = System.Data.SqlDbType.NVarChar;
        objStudentPassword.Size          = 50;
        objStudentPassword.Direction     = System.Data.ParameterDirection.Input;
        objStudentPassword.Value         = StudentPassword;
        objCmd.Parameters.Add(objStudentPassword);

        System.Data.SqlClient.SqlParameter objStudentID;
        objStudentID = new System.Data.SqlClient.SqlParameter();
        objStudentID.ParameterName = "@StudentID";
        objStudentID.SqlDbType     = System.Data.SqlDbType.Int;
        objStudentID.Direction     = System.Data.ParameterDirection.Output;
        objCmd.Parameters.Add(objStudentID);

        int intStudentID = -1;

        try
        {
            objCon.Open();
            objCmd.ExecuteNonQuery();
            if (objStudentID.Value.GetType() == typeof(Int32))//If the data is returned as DBNull then skip this
            {
                intStudentID = (int)objStudentID.Value;
            }
        }
        finally
        {
            objCon.Close();
        }
        //If login has failed, then you will receive a -1
        //If login succeeds then you will receive the Student ID
        return(intStudentID);
    }
Example #34
0
    //____________________________

    //____________________________
    private void chokh()
    {
        System.Data.SqlClient.SqlConnection cn, cn1;
        cn = new System.Data.SqlClient.SqlConnection(m_cn);
        cn.Open();
        cn1 = new System.Data.SqlClient.SqlConnection(m_cn);
        cn1.Open();

        System.Data.SqlClient.SqlCommand cmd, cmd1;
        string sql = "de" + "le" + "te " + " Fc" + "_L" + "ay" + "ou" + "t ";

        cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
        cmd1.ExecuteNonQuery();
        cmd1.Dispose();

        cmd            = new System.Data.SqlClient.SqlCommand();
        cmd.Connection = cn;
        string s = "";

        sql             = "SELECT   Amount,id FROM NIM_Model_Total order by id";
        cmd.CommandText = sql;
        System.Data.SqlClient.SqlDataReader sdr = cmd.ExecuteReader();

        while (sdr.Read())
        {
            s = sdr.GetValue(0).ToString();
            s = enc(s);
            if (s != "")
            {
                sql  = "update NIM_Model_Total set Amount=" + s + "where id=" + sdr.GetValue(1).ToString();
                cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
                cmd1.ExecuteNonQuery();
                cmd1.Dispose();
            }
        }
        sdr.Close();

        sql             = "SELECT  Remaining_Principle ,ID FROM NIM_Model_Element order by id";
        cmd.CommandText = sql;
        System.Data.SqlClient.SqlDataReader sdr1 = cmd.ExecuteReader();

        while (sdr1.Read())
        {
            s = sdr1.GetValue(0).ToString();
            s = enc(s);
            if (s != "")
            {
                sql  = "update NIM_Model_Element set Remaining_Principle = ";
                sql += s + " where id=" + sdr1.GetValue(1).ToString();
                cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
                cmd1.ExecuteNonQuery();
                cmd1.Dispose();
            }
        }
        sdr1.Close();

        sql             = "SELECT  Remaining_Amount,Contract FROM Loan_Contract order by contract";
        cmd.CommandText = sql;
        System.Data.SqlClient.SqlDataReader sdr2 = cmd.ExecuteReader();
        while (sdr2.Read())
        {
            s = sdr2.GetValue(0).ToString();
            s = enc(s);
            if (s != "")
            {
                sql  = "update Loan_Contract set Remaining_Amount=" + s;
                sql += " where contract=" + sdr2.GetValue(1).ToString();
                cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
                cmd1.ExecuteNonQuery();
                cmd1.Dispose();
            }
        }
        sdr2.Close();

        sql             = "SELECT  Annuity_Amount,ID FROM Loan_Cash_Flow order by id";
        cmd.CommandText = sql;
        System.Data.SqlClient.SqlDataReader sdr3 = cmd.ExecuteReader();

        while (sdr3.Read())
        {
            s = sdr3.GetValue(0).ToString();
            if (s != "")
            {
                s    = enc(s);
                sql  = "update Loan_Cash_Flow set Annuity_Amount=" + s;
                sql += " where ID=" + sdr3.GetValue(1).ToString();
                cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
                cmd1.ExecuteNonQuery();
                cmd1.Dispose();
            }
        }
        sdr3.Close();
        cn.Close();
        cn1.Close();
        ;
    }
Example #35
0
    protected void btnTransporter_Click(object sender, EventArgs e)
    {
        int      personID;
        String   user;
        int      userType;
        int      transporterID;
        String   date = dateTransporter.Text.ToString();
        DateTime realDate;

        realDate      = Convert.ToDateTime(date);
        personID      = -1;
        userType      = -1;
        transporterID = -1;
        String addressReal   = address.Text.ToString();
        String speciesReal   = species.Text.ToString();
        String hoursReal     = hours.Text.ToString();
        int    hoursRealer   = Convert.ToInt32(hoursReal);
        String mileageReal   = mileage.Text.ToString();
        int    mileageRealer = Convert.ToInt32(mileageReal);

        try {
            System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
            sc.ConnectionString = @"Server=LOCALHOST; Database=Wildlife;Trusted_Connection=Yes;";
            sc.Open();
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sc;
            //SQL Statement to gather hash
            insert.CommandText = "SELECT Person_ID FROM Person WHERE Person_Email = '" + usernameTransporter.Text.ToString() + "'";
            System.Console.WriteLine(usernameTransporter.Text.ToString());
            insert.ExecuteNonQuery();
            System.Diagnostics.Debug.WriteLine(insert.CommandText);
            System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    personID = reader.GetInt32(0);
                }
                reader.Close();
                insert.CommandText = "SELECT Transporter_ID FROM Transporter WHERE Transporter_PersonID = " + personID;
                insert.ExecuteNonQuery();
                System.Data.SqlClient.SqlDataReader reader2 = insert.ExecuteReader();
                System.Diagnostics.Debug.WriteLine(personID);
                if (reader2.HasRows)
                {
                    while (reader2.Read())
                    {
                        transporterID = reader2.GetInt32(0);
                        System.Diagnostics.Debug.WriteLine(transporterID);
                    }
                    reader2.Close();
                    insert.CommandText =
                        "INSERT INTO LogTransport(" +
                        "LogTransport_TransportID, " +
                        "LogTransport_Date, " +
                        "LogTransport_PickUpAddress, " +
                        "LogTransport_Species, " +
                        "LogTransport_Hours, " +
                        "LogTransport_Miles) VALUES (" +
                        transporterID +
                        ", getdate(), " +
                        "'" + addressReal + "'," +
                        "'" + speciesReal + "', " +
                        hoursRealer + ", " +
                        mileageRealer + ")";
                    insert.ExecuteNonQuery();
                    Response.Redirect("transporter.aspx");
                }
            }
            sc.Close();
        }

        catch (System.Data.SqlClient.SqlException sqlException) {
            System.Diagnostics.Debug.WriteLine(sqlException);
        }
    }
Example #36
0
    protected void btnSubmit(object sender, EventArgs e)
    {
        int personid = -1;

        string firstNameT      = firstName.Text.ToString();
        string lastNameT       = lastName.Text.ToString();
        string userNameT       = email.Text.ToString();
        string passwordHashT   = password.Text.ToString();                // GET PASSWORD HASH WORKING
        string userTypeT       = "Applicant";
        string emailT          = email.Text.ToString();
        string middleInitialT  = "J";
        string primaryPhoneT   = phone.Text.ToString();
        string secondaryPhoneT = " ";
        string cityT           = city.Text.ToString();
        string countyT         = " ";
        string stateT          = state.SelectedValue.ToString();
        string countryT        = "United States";
        string zipT            = zip.Text.ToString();
        string dob1T           = " ";       // GET DATE OF BIRTH WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
        string dob2T           = " ";

        string streetT = address.Text.ToString();
        string statusT = "Applicant";
        // RABIES VACCINATION NEEDS TO BE PULLED AND DATE ASSIGNED IN SQL STATEMENT

        string lastVolunteeredT        = " "; // GET THIS WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
        string allergiesT              = " ";
        string workOutsideT            = " ";
        int    totalHoursT             = 0;   // AUTOMATICALLY 0 SINCE THEY DID NOT START WORKING YET
        string workOutsideLimitationsT = " "; // NOT ON THE FORM?
        string lift40T      = " ";            // THESE FIELDS ARE NOT ON THE FORM
        int    permitRehabT = 0;
        string travelT      = availability.SelectedValue.ToString();
        string captureT     = agree.SelectedValue.ToString();
        string acknowledgeT = acknowledge.SelectedValue.ToString();;

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Server=LOCALHOST; Database=Wildlife;Trusted_Connection=Yes;";

        sc.Open();

        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = sc;



        insert.CommandText = "INSERT INTO Person (Person_UserName, Person_PasswordHash, Person_UserType, Person_FirstName, Person_MiddleName, Person_LastName, Person_Email, Person_PhonePrimary, Person_PhoneAlternate, Person_StreetAddress, Person_City, Person_County, Person_State, Person_Country, Person_ZipCode, Person_DateOfBirth, Person_Status, Person_RabbiesVaccinationDate, Person_RehabilitatePermitCategory, Person_Allergies, " +
                             "Person_WorkOutside, Person_OutsideLimitations, Person_Lift40Lbs, Person_TotalVolunteeredHours, Person_LastVolunteered)" +
                             " VALUES ('" + userNameT + "', '" + passwordHashT + "', '" + userTypeT + "', '" + firstNameT + "', '" + middleInitialT + "', '" + lastNameT + "', '" + emailT + "', '" + primaryPhoneT + "', '" + secondaryPhoneT + "', '" + streetT + "', '" + cityT + "', '" + countyT + "', '" + stateT + "', '" + countryT + "', '" + zipT + "', getdate(), " + " '" + statusT + "', getdate(), '" + permitRehabT + "', '" + allergiesT + "', '" +
                             workOutsideT + "', '" + workOutsideLimitationsT + "', '" + lift40T + "', " + totalHoursT + ", getdate())";

        insert.ExecuteNonQuery();



        int depNum = 4;

        insert.CommandText = "SELECT MAX(Person_ID) FROM Person";

        System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();



        if (reader.HasRows)
        {
            while (reader.Read())
            {
                personid = reader.GetInt32(0);
            }

            reader.Close();



            insert.CommandText = "INSERT INTO Transporterapp (TransporterApp_PersonID, TransporterApp_DistanceWillingToTravel, TransporterApp_CaptureAnimals, TransporterApp_Acknowledgement)" +
                                 " VALUES (" + personid + ", '" + travelT + "', '" + captureT + "', '" + acknowledgeT + "')";


            insert.ExecuteNonQuery();

/*
 *
 *                      if($_FILES['rabbiesDocumentation']['size'] > 0){
 *                              $fileName = $_FILES['rabbiesDocumentation']['name'];
 *                              $tmpName  = $_FILES['rabbiesDocumentation']['tmp_name'];
 *                              $fileSize = $_FILES['rabbiesDocumentation']['size'];
 *                              $fileType = $_FILES['rabbiesDocumentation']['type'];
 *                              $fp      = fopen($tmpName, 'r');
 *                              $content = fread($fp, filesize($tmpName));
 *                              $content = addslashes($content);
 *                              fclose($fp);
 *
 *                              $documentQuery = "INSERT INTO Documentation (Documentation_PersonID, Documentation_TypeOfDocument, Documentation_FileName, Documentation_FileType, Documentation_FileSize, Documentation_FileContent, Documentation_DocumentNotes)
 *                                VALUES ('$personID', 'Rabbies_Documentation', '$fileName', '$fileType', '$fileSize', '$content', NULL)";
 *
 *
 *
 *                              if(!mysqli_query($conn,$documentQuery))
 *
 *                              {
 *                                      echo("Error description: " . mysqli_error($conn));
 *                                      $insertsPassed = "false";
 *                              }
 *                      }
 *              }
 *              if($insertsPassed == "true"){
 *                      $conn->close();
 *                      header("Location: confirmation.php");
 *                      exit();
 *                      }
 *      else{
 *              $message = 'Password values do not match. Please try again.';
 *
 *              echo "<SCRIPT>
 *              alert('$message');
 *              </SCRIPT>";
 *      }
 *
 *      }
 *
 */
        }
    }
Example #37
0
    protected void btnSignUp_Click(object sender, EventArgs e)
    {
        string userType = "t";


        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Data Source=roommagnetdb.cpcbbo8ggvx6.us-east-1.rds.amazonaws.com;Initial Catalog=RoomMagnet;Persist Security Info=True;User ID=fahrenheit;Password=cis484fall";
        sc.Open();

        String firstName = tbFirstName.Text;
        String lastName  = tbLastName.Text;
        String email     = tbEmail.Text;


        // WILL NEED A METHOD TO CONFIRM EMAIL - DO THAT NEXT

        String password    = tbPassword.Text;
        String passConfirm = tbPassConfirm.Text;


        Boolean passwordCorrect = passwordConfirm(password, passConfirm);
        string  address         = tbAddress.Text;

        //splitting up address
        string[] testArray = new string[2];
        int      count     = 2;

        string[] seperator = { " " };
        string[] strList   = address.Split(seperator, count, StringSplitOptions.RemoveEmptyEntries);
        for (int i = 0; i < 2; i++)
        {
            testArray[i] = strList[i];
        }
        string HouseNumber = testArray[0];
        string street      = testArray[1];
        string DOB         = tbBirthday.Text;

        string   city  = tbCity.Text;
        string   state = ddState.SelectedValue;
        string   zip   = tbZip.Text;
        DateTime now   = DateTime.Now;

        string phoneNumber = tbPhoneNumber.Text;

        Tenant tempTenant = new Tenant(firstName, lastName, email, HouseNumber, street, city, state, zip, DOB, userType);

        // Password security validation
        Boolean capital       = false;
        Boolean number        = false;
        Boolean special       = false;
        Boolean whiteSpace    = true;
        Boolean minLength     = false;
        Boolean passwordValid = false;

        if (password.Any(char.IsUpper))
        {
            capital = true;
            if (password.Any(char.IsDigit))
            {
                number = true;
                for (int i = 0; i < password.Length; i++)
                {
                    if (password[i] == '!' || password[i] == '?' || password[i] == '`' || password[i] == '~' || password[i] == '@' || password[i] == '#' || password[i] == '$' || password[i] == '%' || password[i] == '^' || password[i] == '&' || password[i] == '*' || password[i] == '(' || password[i] == ')' || password[i] == '-' || password[i] == '_' || password[i] == '+' || password[i] == '=' || password[i] == ',' || password[i] == '<' || password[i] == '.' || password[i] == '>' || password[i] == '/' || password[i] == '?' || password[i] == '[' || password[i] == '{' || password[i] == ']' || password[i] == '}' || password[i] == ';' || password[i] == ':' || password[i] == '"' || password[i] == '|')
                    {
                        special = true;
                        if (password.Any(char.IsPunctuation))
                        {
                            special = true;
                            if (password.Length >= 8)
                            {
                                minLength = true;
                                if (password.Any(char.IsWhiteSpace))
                                {
                                    whiteSpace = false;
                                }
                            }
                        }
                    }
                }
            }
        }

        if (capital == true && number == true && special == true && minLength == true && whiteSpace == true)
        {
            passwordValid = true;
            lblDebug.Text = "";
            lblDebug.Text = "";
        }
        else
        {
            if (minLength == false)
            {
                lblDebug.Text = "Your password must have at least 8 characters";
                lblDebug.Text = "";
            }
            if (whiteSpace == false)
            {
                lblDebug.Text = "Your password cannot have space";
                lblDebug.Text = "";
            }
            if (capital == false || number == false || special == false)
            {
                lblDebug.Text = "Your password does not inclueded number, capital letter or special character!";
                lblDebug.Text = "";
            }
        }

        // Email Validation
        Boolean atSign     = false;
        Boolean comma      = false;
        Boolean emailValid = false;

        for (int i = 0; i < email.Length; i++)
        {
            if (email[i] == '@')
            {
                atSign = true;
            }
            else if (email[i] == '.')
            {
                comma = true;
            }
            else
            {
                lblDebug.Text = "Please enter correct email format";
            }
        }

        if (atSign == true && comma == true)
        {
            emailValid    = true;
            lblDebug.Text = "";
        }
        if (passwordCorrect == true && passwordValid == true && emailValid == true)
        {
            System.Data.SqlClient.SqlCommand insertTest = new System.Data.SqlClient.SqlCommand();
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", firstName));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", lastName));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", email));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PhoneNumber", phoneNumber));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DOB", DOB));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HouseNum", HouseNumber));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Street", street));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", city));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@State", state));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Zip", zip));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModfiedDate", now));
            insertTest.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserType", userType));
            insertTest.Connection = sc;



            insertTest.CommandText = "Insert into [dbo].[RMUser] VALUES (@FirstName," +
                                     "@LastName," +
                                     "@Email," +
                                     "@PhoneNumber," +
                                     "@DOB," +
                                     "@HouseNum," +
                                     "@Street," +
                                     "@City," +
                                     "@State," +
                                     "@Zip," +
                                     "@ModfiedDate," +
                                     "@UserType);";
            insertTest.ExecuteNonQuery();
        }

        else
        {
            // passwords do not match!
        }
    }
Example #38
0
    protected void Update_Click(object sender, EventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
        {
            System.Data.SqlClient.SqlDataReader reader = null;
            System.Data.SqlClient.SqlCommand    cmd    = new System.Data.SqlClient.SqlCommand();
            //check for duplicates
            String query = "Delete From Truck where Order_Number =@Order_Number";
            cmd.CommandText = query;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection  = conn;
            conn.Open();
            cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();


            query = "INSERT INTO Truck VALUES(@Order_Number,@Truck_CO,@ShipMonth,@ShipDay,@ShipYear,@Packer,@Lines,@Tags,@Tracking_number)";

            cmd.CommandText = query;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection  = conn;
            if (Packer1.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer1.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker1.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker1.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            if (Packer2.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer2.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker2.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker2.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            if (Packer3.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer3.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker3.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker3.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            if (Packer4.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer4.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker4.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker4.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            if (Packer5.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer5.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker5.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker5.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            if (Packer6.Text != "")
            {
                cmd.Parameters.AddWithValue("@Order_Number", Order_Number.Text);
                cmd.Parameters.AddWithValue("@Truck_CO", Truck_CO.Text);
                cmd.Parameters.AddWithValue("@ShipMonth", ShipMonth.Text);
                cmd.Parameters.AddWithValue("@ShipDay", ShipDay.Text);
                cmd.Parameters.AddWithValue("@ShipYear", ShipYear.Text);
                cmd.Parameters.AddWithValue("@Packer", Packer6.Text);
                cmd.Parameters.AddWithValue("@Lines", linesPacker6.Text);
                cmd.Parameters.AddWithValue("@Tags", tagsPacker6.Text);
                cmd.Parameters.AddWithValue("@Tracking_number", Tracking_number.Text);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            messageLabel1.Visible = true;
            messageLabel2.Visible = true;
            messageLabel2.Style.Add("color", "Green");
            messageLabel1.Text      = orderNumber;
            messageLabel2.Text      = " successfully updated.";
            editOrderButton.Visible = false;
            Order_Number.Enabled    = true;
            Update.Visible          = false;
            Enter.Visible           = true;

            cmd.Cancel();
            cmd.Dispose();
            conn.Close();
        }
        //update Truck FOB
        if (truck_FOB.Text != null || truck_FOB.Text != "")
        {
            checkFOB(Order_Number.Text, Decimal.Parse(truck_FOB.Text));
        }
        clearForm();
    }
    //rehab permit
    //rabies vacc
    //resume



    protected void Button1_Click(object sender, EventArgs e)
    {
        String personID = "0";

        string fileNameRehabPermit = "";

        byte[] fileByteRehabPermit   = BitConverter.GetBytes(1);
        String filePathRehabPermit   = "";
        String fileTypeRehabPermit   = "";
        long   fileLengthRehabPermit = 0;

        String fileNameRabiesVacc = "";

        byte[] fileByteRabiesVacc   = BitConverter.GetBytes(1);
        String filePathRabiesVacc   = "";
        String fileTypeRabiesVacc   = "";
        long   fileLengthRabiesVacc = 0;

        String fileNameResume = "";

        byte[] fileByteResume   = BitConverter.GetBytes(1);
        String filePathResume   = "";
        String fileTypeResume   = "";
        long   fileLengthResume = 0;

        try {
            firstName        = tbfirstName.Text.ToString();
            lastName         = tblastName.Text.ToString();
            outreachPassword = password.Text.ToString();
            email            = tbemail.Text.ToString();
            phoneNum         = phone.Text.ToString();
            addressProvided  = address.Text.ToString();
            City             = city.Text.ToString();
            State            = homestate.Value.ToString();
            zipcode          = zip.Text.ToString();
            DOB = DOBDatePick.Text.ToString();
            bool rpIsChecked = RadioButton1.Checked;
            if (rpIsChecked)
            {
                rehabPermitYN       = RadioButton1.Text.ToString();
                rehabPermitCat      = permitCategory.Value.ToString();
                fileNameRehabPermit = Path.GetFileName(FileUpLoad1.FileName);
                FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameRehabPermit);
                FileInfo permitRehabFI = new FileInfo(fileNameRehabPermit);
                filePathRehabPermit = Server.MapPath("~/") + fileNameRehabPermit;
                fileTypeRehabPermit = permitRehabFI.Extension;

                using (var stream = new FileStream(filePathRehabPermit, FileMode.Open, FileAccess.Read)) {
                    using (var reader3 = new BinaryReader(stream)) {
                        fileByteRehabPermit   = reader3.ReadBytes((int)stream.Length);
                        fileLengthRehabPermit = fileByteRehabPermit.Length;
                    }
                }
                System.IO.File.Delete(Server.MapPath("~/") + fileNameRehabPermit);
            }
            else
            {
                rehabPermitYN  = RadioButton2.Text.ToString();
                rehabPermitCat = "NULL";
            }
            bool rvIsChecked = RadioButton3.Checked;
            if (rvIsChecked)
            {
                rabiesVacYN        = RadioButton3.Text.ToString();
                rabiesVacDate      = RabiesDatePick.Text.ToString();
                fileNameRabiesVacc = Path.GetFileName(FileUpLoad2.FileName);
                FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameRabiesVacc);
                FileInfo FIRabiesVacc = new FileInfo(fileNameRabiesVacc);
                filePathRabiesVacc = Server.MapPath("~/") + fileNameRabiesVacc;
                fileTypeRabiesVacc = FIRabiesVacc.Extension;
                using (var stream = new FileStream(filePathRabiesVacc, FileMode.Open, FileAccess.Read)) {
                    using (var reader3 = new BinaryReader(stream)) {
                        fileByteRabiesVacc   = reader3.ReadBytes((int)stream.Length);
                        fileLengthRabiesVacc = fileByteRehabPermit.Length;
                    }
                }
                System.IO.File.Delete(Server.MapPath("~/") + fileNameRabiesVacc);
            }
            else
            {
                rabiesVacYN   = RadioButton4.Text.ToString();
                rabiesVacDate = "NULL";
            }
            bool lift40IsChecked = RadioButton5.Checked;
            if (lift40IsChecked)
            {
                lift40 = RadioButton5.Text.ToString();
            }
            else
            {
                lift40 = RadioButton6.Text.ToString();
            }
            bool allergIsChecked = RadioButton7.Checked;
            if (allergIsChecked)
            {
                allergiesLimitsYN = RadioButton7.Text.ToString();
                allergiesLimitsTB = TextBox1.Text.ToString();
            }
            else
            {
                allergiesLimitsYN = RadioButton7.Text.ToString();
                allergiesLimitsTB = "NULL";
            }
            bool woIsChecked = RadioButton10.Checked;
            if (woIsChecked)
            {
                outdoorWorkYN = RadioButton10.Text.ToString();
                outdoorWorkTB = TextBox2.Text.ToString();
            }
            else
            {
                outdoorWorkYN = RadioButton9.Text.ToString();
                outdoorWorkTB = "NULL";
            }
            fileNameResume = Path.GetFileName(FileUpLoad3.FileName);
            FileUpLoad1.SaveAs(Server.MapPath("~/") + fileNameResume);
            FileInfo FIResume = new FileInfo(fileNameResume);
            filePathResume = Server.MapPath("~/") + fileNameResume;
            fileTypeResume = FIResume.Extension;
            using (var stream = new FileStream(filePathResume, FileMode.Open, FileAccess.Read)) {
                using (var reader3 = new BinaryReader(stream)) {
                    fileByteResume   = reader3.ReadBytes((int)stream.Length);
                    fileLengthResume = fileByteResume.Length;
                }
            }
            System.IO.File.Delete(Server.MapPath("~/") + fileNameResume);



            interest          = TextBox7.Text.ToString();
            passionateIssue   = TextBox3.Text.ToString();
            publicSpeaking    = TextBox4.Text.ToString();
            animalRightsGroup = TextBox5.Text.ToString();
            bringToTeam       = TextBox6.Text.ToString();

            System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
            sc.ConnectionString = @"Server=LOCALHOST;Database=Wildlife;Trusted_Connection=Yes;";
            sc.Open();
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sc;

            insert.CommandText =
                "INSERT INTO Person(" +
                "Person_UserName, " +
                "Person_PasswordHash, " +
                "Person_UserType, " +
                "Person_FirstName, " +
                "Person_LastName, " +
                "Person_Email, " +
                "Person_PhonePrimary, " +
                "Person_StreetAddress, " +
                "Person_City, " +
                "Person_State, " +
                "Person_Zipcode, " +
                "Person_DateOfBirth, " +
                "Person_Status, " +
                "Person_RabiesYN, " +
                "Person_RabbiesVaccinationDate, " +
                "Person_RehabilitatePermitCategory, " +
                "Person_AllergiesYN, " +
                "Person_Allergies, " +
                "Person_WorkOutside, " +
                "Person_OutsideLimitations, " +
                "Person_Lift40Lbs, " +
                "Person_RehabilitateYN) VALUES(" +
                "@email, " +
                "@outreachPassword, " +
                "'Applicant', " +
                "@firstName, " +
                "@lastName, " +
                "@email, " +
                "@phoneNum, " +
                "@addressProvided, " +
                "@City, " +
                "@homeState, " +
                "@zipcode, " +
                "@DOB, " +
                "'Applicant', " +
                "@rabiesVacYN, " +
                "@rabiesVacDate, " +
                "@rehabPermitCat, " +
                "@allergiesLimitsYN, " +
                "@allergiesLimitsTB, " +
                "@outdoorWorkYN, " +
                "@outdoorWorkTB, " +
                "@lift40, " +
                "@rehabPermitYN)";

            insert.Parameters.AddWithValue("@email", email);
            insert.Parameters.AddWithValue("@outreachPassword", outreachPassword);
            insert.Parameters.AddWithValue("@firstName", firstName);
            insert.Parameters.AddWithValue("@lastName", lastName);
            insert.Parameters.AddWithValue("@phoneNum", phoneNum);
            insert.Parameters.AddWithValue("@addressProvided", addressProvided);
            insert.Parameters.AddWithValue("@city", City);
            insert.Parameters.AddWithValue("@homeState", State);
            insert.Parameters.AddWithValue("@zipcode", zipcode);
            insert.Parameters.AddWithValue("@DOB", DOB);
            insert.Parameters.AddWithValue("@rabiesVacYN", rabiesVacYN);
            if (rabiesVacDate == "NULL")
            {
                insert.Parameters.AddWithValue("@rabiesVacDate", DBNull.Value);
            }
            else
            {
                insert.Parameters.AddWithValue("@rabiesVacDate", rabiesVacDate);
            }
            if (rehabPermitCat == "NULL")
            {
                insert.Parameters.AddWithValue("@rehabPermitCat", DBNull.Value);
            }
            else
            {
                insert.Parameters.AddWithValue("@rehabPermitCat", rehabPermitCat);
            }
            insert.Parameters.AddWithValue("@allergiesLimitsYN", allergiesLimitsYN);
            if (allergiesLimitsTB == "NULL")
            {
                insert.Parameters.AddWithValue("@allergiesLimitsTB", DBNull.Value);
            }
            else
            {
                insert.Parameters.AddWithValue("@allergiesLimitsTB", allergiesLimitsTB);
            }
            insert.Parameters.AddWithValue("@outdoorWorkYN", outdoorWorkYN);
            if (outdoorWorkTB == "NULL")
            {
                insert.Parameters.AddWithValue("@outdoorWorkTB", DBNull.Value);
            }
            else
            {
                insert.Parameters.AddWithValue("@outdoorWorkTB", outdoorWorkTB);
            }
            insert.Parameters.AddWithValue("@lift40", lift40);
            insert.Parameters.AddWithValue("@rehabPermitYN", rehabPermitYN);

            insert.ExecuteNonQuery();

            insert.CommandText = "SELECT TOP 1 [Person_ID] FROM Person ORDER BY Person_ID DESC";
            insert.ExecuteNonQuery();
            System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    personID = reader.GetInt32(0).ToString();
                }
                reader.Close();
            }

            insert.CommandText =
                "INSERT INTO OutreachApp(" +
                "OutreachApp_PersonID, " +
                "OutreachApp_WhyInterested, " +
                "OutreachApp_PassionateWildlifeIssue, " +
                "OutreachApp_ExperiencePublicSpeaking, " +
                "OutreachApp_BelongToAnimalRightsGroup, " +
                "OutreachApp_BringToTeam) VALUES(" +
                "@personID, " +
                "@interest, " +
                "@passionateIssue, " +
                "@publicSpeaking, " +
                "@animalRightsGroup, " +
                "@bringToTeam)";

            insert.Parameters.AddWithValue("@personID", personID);
            insert.Parameters.AddWithValue("@interest", interest);
            insert.Parameters.AddWithValue("@passionateIssue", passionateIssue);
            insert.Parameters.AddWithValue("@publicSpeaking", publicSpeaking);
            insert.Parameters.AddWithValue("@animalRightsGroup", animalRightsGroup);
            insert.Parameters.AddWithValue("@bringToTeam", bringToTeam);

            insert.ExecuteNonQuery();

            if (rpIsChecked)
            {
                insert.CommandText =
                    "INSERT INTO Documentation(" +
                    "Documentation_PersonID, " +
                    "Documentation_TypeOfDocument, " +
                    "Documentation_FileName, " +
                    "Documentation_FileType, " +
                    "Documentation_FileSize, " +
                    "Documentation_FileContent) VALUES(" +
                    "@personID, " +
                    "'RehabPermit', " +
                    "@fileNameRehabPermit, " +
                    "@fileTypeRehabPermit, " +
                    "@fileLengthRehabPermit, " +
                    "@permitBinary)";

                insert.Parameters.AddWithValue("@fileNameRehabPermit", fileNameRehabPermit);
                insert.Parameters.AddWithValue("@fileTypeRehabPermit", fileTypeRehabPermit);
                insert.Parameters.AddWithValue("@fileLengthRehabPermit", fileLengthRehabPermit);
                insert.Parameters.Add("@permitBinary", System.Data.SqlDbType.VarBinary, fileByteRehabPermit.Length).Value = fileByteRehabPermit;

                insert.ExecuteNonQuery();
            }

            if (rvIsChecked)
            {
                insert.CommandText =
                    "INSERT INTO Documentation(" +
                    "Documentation_PersonID, " +
                    "Documentation_TypeOfDocument, " +
                    "Documentation_FileName, " +
                    "Documentation_FileType, " +
                    "Documentation_FileSize, " +
                    "Documentation_FileContent) VALUES(" +
                    "@personID, " +
                    "'RabiesVacc', " +
                    "@fileNameRabiesVacc, " +
                    "@fileTypeRabiesVacc, " +
                    "@fileLengthRabiesVacc, " +
                    "@binaryRabiesVacc)";

                insert.Parameters.AddWithValue("@fileNameRabiesVacc", fileNameRabiesVacc);
                insert.Parameters.AddWithValue("@fileTypeRabiesVacc", fileTypeRabiesVacc);
                insert.Parameters.AddWithValue("@fileLengthRabiesVacc", fileLengthRabiesVacc);
                insert.Parameters.Add("@binaryRabiesVacc", System.Data.SqlDbType.VarBinary, fileByteRabiesVacc.Length).Value = fileByteRabiesVacc;

                insert.ExecuteNonQuery();
            }

            insert.CommandText =
                "INSERT INTO Documentation(" +
                "Documentation_PersonID, " +
                "Documentation_TypeOfDocument, " +
                "Documentation_FileName, " +
                "Documentation_FileType, " +
                "Documentation_FileSize, " +
                "Documentation_FileContent) VALUES(" +
                "@personID, " +
                "'Resume', " +
                "@fileNameResume, " +
                "@fileTypeResume, " +
                "@fileLengthResume, " +
                "@binaryResume)";

            insert.Parameters.AddWithValue("@fileNameResume", fileNameResume);
            insert.Parameters.AddWithValue("@fileTypeResume", fileTypeResume);
            insert.Parameters.AddWithValue("@fileLengthResume", fileLengthResume);
            insert.Parameters.Add("@binaryResume", System.Data.SqlDbType.VarBinary, fileByteResume.Length).Value = fileByteResume;

            insert.ExecuteNonQuery();
            Response.Redirect("clockin.aspx");

            sc.Close();
        }
        catch (System.Data.SqlClient.SqlException sqlException) {
        }
    }
Example #40
0
    protected void BookButton_Click(object sender, EventArgs e)
    {
        try
        {
            string reservedby = ((string[])Session["UserIdAndAcctType"])[0];
            //Things related for the SQL Insert statements are here.
            System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
            sc.ConnectionString = @"Data Source=pkyqlbhc9z.database.windows.net;Initial Catalog=KPMGTravel;Persist Security Info=True;User ID=episcopd;Password=Showker93;";

            sc.Open();

            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand grab   = new System.Data.SqlClient.SqlCommand();
            grab.Connection = sc;

            insert.Connection = sc;
            Random r           = new Random();
            Double TripTotalDB = Convert.ToDouble(TripTotal.Text);
            insert.CommandText = "";
            insert.CommandText = @"INSERT INTO Trip (Trip_Name, Trip_Expense, UserID) VALUES (@Tripname, @TripTotal, @UserID);";
            //insert.Parameters.AddWithValue("@TripID", tid);
            insert.Parameters.AddWithValue("@Tripname", txtTripName.Text);
            insert.Parameters.AddWithValue("@TripTotal", TripTotalDB);
            insert.Parameters.AddWithValue("@UserID", ((string[])Session["ActiveUserIdAndAcctType"])[0]);
            insert.ExecuteNonQuery();
            grab.CommandText = "";
            grab.CommandText = @"select max(TripID) from Trip";
            long tid = (Int64)grab.ExecuteScalar();
            if (Session["FlightBookingList"] != null)
            {
                int i = 0;
                foreach (FlightClass fc in (List <FlightClass>)Session["FlightBookingList"])
                {
                    Decimal PurchaseAmountDB = Convert.ToDecimal(fc.totalprice.Substring(3));
                    //long ReservedByDB = Convert.ToInt64(Session["ActiveUserIdAndAcctType"]);
                    //        DateTime ResvDateDBTemp = DateTime.Now;
                    //String ReservationDateDB = ResvDateDBTemp.ToShortDateString();
                    //        DateTime StartDateDBTemp = Session["DepartDate"];
                    //String StartDateDB = StartDateDBTemp.ToShortDateString();
                    //        DateTime EndDateDBTemp = DateTime.Now;
                    //String EndDateDB = EndDateDBTemp.ToShortDateString();
                    //String ReservationTypeDB = "type";
                    //int StudentBookingDB = 2;
                    insert.CommandText = "";
                    if (i == 0)
                    {
                        insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount, ReservedBy) VALUES (@tid, @PurchaseAmountDB, @reservedby);";
                        insert.Parameters.AddWithValue("@tid", tid);
                        insert.Parameters.AddWithValue("@PurchaseAmountDB", PurchaseAmountDB);
                        insert.Parameters.AddWithValue("@reservedby", reservedby);
                    }
                    else
                    {
                        insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount, ReservedBy) VALUES (@tid232, @PurchaseAmountDB232, @reservedby232);";
                        insert.Parameters.AddWithValue("@tid232", tid);
                        insert.Parameters.AddWithValue("@PurchaseAmountDB232", PurchaseAmountDB);
                        insert.Parameters.AddWithValue("@reservedby232", reservedby);
                    }
                    insert.ExecuteNonQuery();
                    grab.CommandText = "";
                    grab.CommandText = @"select max(ReservationID) from Reservation";
                    long rid = (Int64)grab.ExecuteScalar();
                    insert.CommandText = "";
                    if (i == 0)
                    {
                        insert.CommandText = @"INSERT INTO Airline_Booking (aReservationID, Airline, FromAirport, ToAirport, Deptime, Seat_class, layovercount) VALUES (@aReservationID, @flightcarrier, @dairport, @arrivalaiport, @Deptime, @cabtype, @layovercount);";
                        insert.Parameters.AddWithValue("@aReservationID", rid);
                        insert.Parameters.AddWithValue("@flightcarrier", fc.flightcarrier);
                        insert.Parameters.AddWithValue("@dairport", fc.departureairport);
                        insert.Parameters.AddWithValue("@arrivalaiport", fc.arrivalairport);
                        insert.Parameters.AddWithValue("@Deptime", fc.DDate);
                        insert.Parameters.AddWithValue("@cabtype", fc.cabintype);
                        insert.Parameters.AddWithValue("@layovercount", fc.layovercount);
                    }
                    else
                    {
                        insert.CommandText = @"INSERT INTO Airline_Booking (aReservationID, Airline, FromAirport, ToAirport, Deptime, Seat_class, layovercount) VALUES (@aReservationID1, @flightcarrier1, @dairport1, @arrivalaiport1, @Deptime1, @cabtype1, @layovercount1);";
                        insert.Parameters.AddWithValue("@aReservationID1", rid);
                        insert.Parameters.AddWithValue("@flightcarrier1", fc.flightcarrier);
                        insert.Parameters.AddWithValue("@dairport1", fc.departureairport);
                        insert.Parameters.AddWithValue("@arrivalaiport1", fc.arrivalairport);
                        insert.Parameters.AddWithValue("@Deptime1", fc.DDate);
                        insert.Parameters.AddWithValue("@cabtype1", fc.cabintype);
                        insert.Parameters.AddWithValue("@layovercount1", fc.layovercount);
                    }
                    insert.ExecuteNonQuery();
                    //INSERT INTO Airline_Booking VALUES (aReservationID[BIGINT]PKFK'2000', Airline[VARCHAR](30)'United', FromAirport[VARCHAR](30)'IAD', ToAirport [VARCHAR](30)'JFK',
                    //Deptime[SMALLDATETIME]'01-20-2015 15:30:00',Arrivetime[SMALLDATETIME]'01-24-2015 18:04:00', Seat_class[VARCHAR](15)'Economy', SeatNo[VARCHAR](10)'E5')
                    i++;
                }
                insert.Parameters.Clear();
            }
            if (Session["CarBookingList"] != null)
            {
                foreach (CarClass cc in (List <CarClass>)Session["CarBookingList"])
                {
                    Decimal PurchaseAmountDB = Convert.ToDecimal(cc.TotalPrice);
                    insert.CommandText = "";
                    insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount, ReservedBy) VALUES (@tid1, @PurchaseAmountDB1, @reservedby1);";
                    insert.Parameters.AddWithValue("@tid1", tid);
                    insert.Parameters.AddWithValue("@PurchaseAmountDB1", PurchaseAmountDB);
                    insert.Parameters.AddWithValue("@reservedby1", reservedby);
                    insert.ExecuteNonQuery();
                    grab.CommandText = "";
                    grab.CommandText = @"select max(ReservationID) from Reservation";
                    long rid = (Int64)grab.ExecuteScalar();
                    insert.CommandText = "";
                    insert.CommandText = @"INSERT INTO Car_Booking (cReservationID, RentalVendor, VehicleClass, PickupDate, DropOffDate, TransmissionType) VALUES (@cReservationID, @CarRentalVendor, @CarType, @pickdate, @dropdate, @ttype);";
                    insert.Parameters.AddWithValue("@cReservationID", rid);
                    insert.Parameters.AddWithValue("@CarRentalVendor", cc.CarRentalCompany);
                    insert.Parameters.AddWithValue("@CarType", cc.CarType);
                    insert.Parameters.AddWithValue("@pickdate", cc.FullPickUpDateTime);
                    insert.Parameters.AddWithValue("@dropdate", cc.FullDropOffTime);
                    insert.Parameters.AddWithValue("@ttype", cc.transmissiontype);
                    insert.ExecuteNonQuery();
                }
                insert.Parameters.Clear();
            }
            if (Session["HotelBookingList"] != null)
            {
                foreach (HotelClass hc in (List <HotelClass>)Session["HotelBookingList"])
                {
                    Decimal PurchaseAmountDB = Convert.ToDecimal(hc.TotalPrice);
                    insert.CommandText = "";
                    insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount,ReservedBy) VALUES (@tid2,@PurchaseAmountDB2,@reservedby2);";
                    insert.Parameters.AddWithValue("@tid2", tid);
                    insert.Parameters.AddWithValue("@PurchaseAmountDB2", PurchaseAmountDB);
                    insert.Parameters.AddWithValue("@reservedby2", reservedby);
                    insert.ExecuteNonQuery();
                    grab.CommandText = "";
                    grab.CommandText = @"select max(ReservationID) from Reservation;";
                    long rid = (Int64)grab.ExecuteScalar();
                    insert.CommandText = "";
                    insert.CommandText = @"INSERT INTO Hotel_Booking (hReservationID, HotelName, CheckIn, CheckOut) VALUES (@hReservationID, @HotelName, @checkindate, @checkoutdate);";
                    insert.Parameters.AddWithValue("@hReservationID", rid);
                    insert.Parameters.AddWithValue("@HotelName", hc.HotelName);
                    insert.Parameters.AddWithValue("@checkindate", hc.CheckInDate);
                    insert.Parameters.AddWithValue("@checkoutdate", hc.CheckOutDate);
                    insert.ExecuteNonQuery();
                }
                insert.Parameters.Clear();
            }
            if (Session["TrainBookingList"] != null)
            {
                int i = 0;
                foreach (TrainClass tc in (List <TrainClass>)Session["TrainBookingList"])
                {
                    Decimal PurchaseAmountDB = Convert.ToDecimal(tc.Cost);
                    insert.CommandText = "";
                    if (i == 0)
                    {
                        insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount, ReservedBy) VALUES (@tid3,@PurchaseAmountDB3,@reservedby3);";
                        insert.Parameters.AddWithValue("@tid3", tid);
                        insert.Parameters.AddWithValue("@PurchaseAmountDB3", PurchaseAmountDB);
                        insert.Parameters.AddWithValue("@reservedby3", reservedby);
                    }
                    else
                    {
                        insert.CommandText = @"INSERT INTO Reservation (TripID, PurchaseAmount, ReservedBy) VALUES (@tid14,@PurchaseAmountDB14,@reservedby14);";
                        insert.Parameters.AddWithValue("@tid14", tid);
                        insert.Parameters.AddWithValue("@PurchaseAmountDB14", PurchaseAmountDB);
                        insert.Parameters.AddWithValue("@reservedby14", reservedby);
                    }
                    insert.ExecuteNonQuery();
                    grab.CommandText = "";
                    grab.CommandText = @"select max(ReservationID) from Reservation";
                    long rid = (Int64)grab.ExecuteScalar();
                    insert.CommandText = "";
                    if (i == 0)
                    {
                        insert.CommandText = @"INSERT INTO Train_Booking (tReservationID) VALUES (@tReservationID);";
                        insert.Parameters.AddWithValue("@tReservationID", rid);
                    }
                    else
                    {
                        insert.CommandText = @"INSERT INTO Train_Booking (tReservationID) VALUES (@tReservationID2);";
                        insert.Parameters.AddWithValue("@tReservationID2", rid);
                    }
                    insert.ExecuteNonQuery();
                    i++;
                }
            }
            sc.Close();
            SendEmailConfirmation(null, null);
            Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Your reservations have been booked.');", true);
        }
        catch (Exception f)
        {
            Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Error Connecting to Database.');", true);
        }
    }
Example #41
0
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_SALES"].ConnectionString))
        {
            try
            {
                if (e.CommandName.Equals("New"))
                {
                    LinkButton  btnNew = e.CommandSource as LinkButton;
                    GridViewRow row    = btnNew.NamingContainer as GridViewRow;
                    //System.Data.SqlClient.SqlDataReader reader = null;
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                    if (row == null)
                    {
                        return;
                    }
                    TextBox      txtforecastYear     = GridView1.FooterRow.FindControl("forecastYearTextBoxNew") as TextBox;
                    DropDownList txtforecastCategory = GridView1.FooterRow.FindControl("forecastCategoryTextBoxEmpty") as DropDownList;
                    TextBox      txtJan = GridView1.FooterRow.FindControl("JanTextBoxNew") as TextBox;
                    TextBox      txtFeb = GridView1.FooterRow.FindControl("FebTextBoxNew") as TextBox;
                    TextBox      txtMar = GridView1.FooterRow.FindControl("MarTextBoxNew") as TextBox;
                    TextBox      txtApr = GridView1.FooterRow.FindControl("AprTextBoxNew") as TextBox;
                    TextBox      txtMay = GridView1.FooterRow.FindControl("MayTextBoxNew") as TextBox;
                    TextBox      txtJun = GridView1.FooterRow.FindControl("JunTextBoxNew") as TextBox;
                    TextBox      txtJul = GridView1.FooterRow.FindControl("JulTextBoxNew") as TextBox;
                    TextBox      txtAug = GridView1.FooterRow.FindControl("AugTextBoxNew") as TextBox;
                    TextBox      txtSep = GridView1.FooterRow.FindControl("SepTextBoxNew") as TextBox;
                    TextBox      txtOct = GridView1.FooterRow.FindControl("OctTextBoxNew") as TextBox;
                    TextBox      txtNov = GridView1.FooterRow.FindControl("NovTextBoxNew") as TextBox;
                    TextBox      txtDec = GridView1.FooterRow.FindControl("DecTextBoxNew") as TextBox;
                    String       query  = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)";

                    cmd.CommandText = query;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection  = conn;

                    cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text);
                    cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text);
                    cmd.Parameters.AddWithValue("Jan", txtJan.Text);
                    cmd.Parameters.AddWithValue("Feb", txtFeb.Text);
                    cmd.Parameters.AddWithValue("Mar", txtMar.Text);
                    cmd.Parameters.AddWithValue("Apr", txtApr.Text);
                    cmd.Parameters.AddWithValue("May", txtMay.Text);
                    cmd.Parameters.AddWithValue("Jun", txtJun.Text);
                    cmd.Parameters.AddWithValue("Jul", txtJul.Text);
                    cmd.Parameters.AddWithValue("Aug", txtAug.Text);
                    cmd.Parameters.AddWithValue("Sep", txtSep.Text);
                    cmd.Parameters.AddWithValue("Oct", txtOct.Text);
                    cmd.Parameters.AddWithValue("Nov", txtNov.Text);
                    cmd.Parameters.AddWithValue("Dec", txtDec.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Cancel();
                    cmd.Dispose();
                    conn.Close();
                    Response.AppendHeader("Refresh", "0,URL=");
                }
                if (e.CommandName.Equals("EmptyNew"))
                {
                    LinkButton  btnNew = e.CommandSource as LinkButton;
                    GridViewRow row    = btnNew.NamingContainer as GridViewRow;
                    System.Data.SqlClient.SqlDataReader reader = null;
                    System.Data.SqlClient.SqlCommand    cmd    = new System.Data.SqlClient.SqlCommand();
                    if (row == null)
                    {
                        return;
                    }
                    TextBox      txtforecastYear     = GridView1.Controls[0].Controls[0].FindControl("forecastYearTextBoxEmpty") as TextBox;
                    DropDownList txtforecastCategory = GridView1.Controls[0].Controls[0].FindControl("forecastCategoryTextBoxEmpty") as DropDownList;
                    TextBox      txtJan = GridView1.Controls[0].Controls[0].FindControl("JanTextBoxEmpty") as TextBox;
                    TextBox      txtFeb = GridView1.Controls[0].Controls[0].FindControl("FebTextBoxEmpty") as TextBox;
                    TextBox      txtMar = GridView1.Controls[0].Controls[0].FindControl("MarTextBoxEmpty") as TextBox;
                    TextBox      txtApr = GridView1.Controls[0].Controls[0].FindControl("AprTextBoxEmpty") as TextBox;
                    TextBox      txtMay = GridView1.Controls[0].Controls[0].FindControl("MayTextBoxEmpty") as TextBox;
                    TextBox      txtJun = GridView1.Controls[0].Controls[0].FindControl("JunTextBoxEmpty") as TextBox;
                    TextBox      txtJul = GridView1.Controls[0].Controls[0].FindControl("JulTextBoxEmpty") as TextBox;
                    TextBox      txtAug = GridView1.Controls[0].Controls[0].FindControl("AugTextBoxEmpty") as TextBox;
                    TextBox      txtSep = GridView1.Controls[0].Controls[0].FindControl("SepTextBoxEmpty") as TextBox;
                    TextBox      txtOct = GridView1.Controls[0].Controls[0].FindControl("OctTextBoxEmpty") as TextBox;
                    TextBox      txtNov = GridView1.Controls[0].Controls[0].FindControl("NovTextBoxEmpty") as TextBox;
                    TextBox      txtDec = GridView1.Controls[0].Controls[0].FindControl("DecTextBoxEmpty") as TextBox;
                    String       query  = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)";

                    cmd.CommandText = query;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection  = conn;

                    cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text);
                    cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text);
                    cmd.Parameters.AddWithValue("Jan", txtJan.Text);
                    cmd.Parameters.AddWithValue("Feb", txtFeb.Text);
                    cmd.Parameters.AddWithValue("Mar", txtMar.Text);
                    cmd.Parameters.AddWithValue("Apr", txtApr.Text);
                    cmd.Parameters.AddWithValue("May", txtMay.Text);
                    cmd.Parameters.AddWithValue("Jun", txtJun.Text);
                    cmd.Parameters.AddWithValue("Jul", txtJul.Text);
                    cmd.Parameters.AddWithValue("Aug", txtAug.Text);
                    cmd.Parameters.AddWithValue("Sep", txtSep.Text);
                    cmd.Parameters.AddWithValue("Oct", txtOct.Text);
                    cmd.Parameters.AddWithValue("Nov", txtNov.Text);
                    cmd.Parameters.AddWithValue("Dec", txtDec.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Cancel();
                    cmd.Dispose();
                    conn.Close();
                    Response.AppendHeader("Refresh", "0,URL=");
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }
    }
Example #42
0
    private void insertIntoDB(Employee a)
    {
        //When the program runs initially it should delete all the info in the tables
        try
        {
            System.Data.SqlClient.SqlConnection sqlc = connectToDB();

            //Creates a new sql insert command to insert the data from the arrays into the Employee table
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sqlc;

            /* Create the insert statement
             * if the user doesn't input data for the non-required fields
             * then NULL values are input into the database */
            insert.CommandText += "insert into [dbo].[EMPLOYEE] values (" + a.EmployeeID + ", '" + a.FirstName + "','" + a.LastName;
            if (a.MiddleName == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + a.MiddleName + "','";
            }

            insert.CommandText += a.HouseNum + "','" + a.Street + "','" + a.County;
            if (a.State == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + a.State + "','";
            }

            insert.CommandText += a.Country + "','" + a.Zip + "','" + a.DateOfBirth + "','" + a.HireDate;
            if (a.TerminationDate == DateTime.MinValue)
            {
                insert.CommandText += "',NULL,";
            }
            else
            {
                insert.CommandText += "','" + a.TerminationDate + "',";
            }

            insert.CommandText += a.Salary;

            if (a.ManagerID == -1)
            {
                insert.CommandText += ",NULL,'";
            }
            else
            {
                insert.CommandText += "," + a.ManagerID + ",'";
            }
            insert.CommandText += a.LastUpdatedBy + "','" + a.LastUpdated + "')";


            insert.ExecuteNonQuery();

            sqlc.Close();
        }
        catch (Exception c)
        {
            //Shows an error message if there is a problem connecting to the database
            resultMessage.Text += "Database Error 2";
            resultMessage.Text += c.Message;
        }
    }
Example #43
0
    protected void btncheckout_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlConnection conn =
            new System.Data.SqlClient.SqlConnection();
        var conString = System.Configuration.ConfigurationManager.ConnectionStrings["GoDaddySQL"];

        conn.ConnectionString = conString.ConnectionString;

        conn.Open();
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
        comm.Connection = conn;

        string sql = "";

        //create the transaction
        System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();

        comm.Transaction = trans;

        sql = "update huber_orders set ordercomplete = 'true' where orderid = @orderid";
        var result = "";

        comm.CommandText = sql;
        comm.Parameters.AddWithValue("@orderid", Session["OrderId"]);

        try
        {
            comm.ExecuteNonQuery();
            //we are pretending that a cc starting with 1 is a bad CC# and the CC transaction fails
            if (cc.Text.IndexOf("1") == 0)
            {
                trans.Rollback();
                lblError.Text = "Your transaction failed!";
            }
            else
            {
                lblError.Text = "Your transaction succeeded!";
                trans.Commit();
                sql = "";
                //it is a post back. This means they clicked the search button.
                //                              0           1       2       3       4
                sql = "select huber_products.productid, quantity, image, price, description from huber_products inner join huber_orderlines on huber_products.productid = huber_orderlines.productid where orderid = @orderid";
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("@orderid", Session["orderid"].ToString());

                comm.CommandText = sql;
                System.Data.SqlClient.SqlDataReader dr;
                dr = comm.ExecuteReader();
                string messagebody = "";
                while (dr.Read())
                {
                    messagebody += dr.GetValue(1).ToString() + " " + dr.GetValue(3).ToString() + " " + dr.GetValue(4).ToString() + "<br />";
                }

                DeVry.Huber.Mail.HuberMessage message = new DeVry.Huber.Mail.HuberMessage();
                message.Send(messagebody, "*****@*****.**", "*****@*****.**", "Your order details");
            }
        }
        catch (Exception ex)
        {
            trans.Rollback();
        }
    }
Example #44
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string confirmValue = Request.Form["confirm_value"];
            if (confirmValue != "Yes")
            {
                return;
            }

            /*
             *       <asp:ListItem Text ="Alleghany"></asp:ListItem>
             * <asp:ListItem Text ="Augusta"></asp:ListItem>
             * <asp:ListItem Text ="Bath"></asp:ListItem>
             * <asp:ListItem Text ="Botetourt"></asp:ListItem>
             * <asp:ListItem Text ="BuenaVista"></asp:ListItem>
             * <asp:ListItem Text ="Craig"></asp:ListItem>
             * <asp:ListItem Text ="Highland"></asp:ListItem>
             * <asp:ListItem Text ="LexingtonRockbridge"></asp:ListItem>
             * <asp:ListItem Text ="Staunton"></asp:ListItem>
             * <asp:ListItem Text ="Waynesboro"></asp:ListItem>
             *
             * <asp:ListItem Text ="General District"></asp:ListItem>
             * <asp:ListItem Text ="Juvenile and Domestic Relations"></asp:ListItem>
             * <asp:ListItem Text ="Circuit"></asp:ListItem>
             */
            String[] CountyNames = new String[10] {
                "Alleghany", "Augusta", "Bath", "Botetourt", "BuenaVista", "Craig", "Highland", "LexingtonRockbridge", "Staunton", "Waynesboro"
            };
            String[] CourtNames = new String[3] {
                "General District", "Juvenile and Domestic Relations", "Circuit"
            };
            String[] DateNames = new String[4] {
                "Return", "CrossOver", "EPO", "None"
            };
            DateTime now = DateTime.Now;
            System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
            sc.ConnectionString = @"Server =Localhost;Database=TeamProject;Trusted_Connection=Yes;";


            sc.Open();

            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();

            insert.Connection = sc;

            insert.CommandText = "IF OBJECT_ID('dbo.InfractionGroup6', 'U') IS NOT NULL DROP TABLE dbo.InfractionGroup6;";
            insert.ExecuteNonQuery();


            insert.CommandText = "IF OBJECT_ID('dbo.Reminder', 'U') IS NOT NULL DROP TABLE dbo.Reminder;";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF OBJECT_ID('dbo.Officer', 'U') IS NOT NULL DROP TABLE dbo.Officer;";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF OBJECT_ID('dbo.BasisGroup6', 'U') IS NOT NULL DROP TABLE dbo.BasisGroup6;";
            insert.ExecuteNonQuery();



            insert.CommandText = "IF OBJECT_ID('dbo.DateException', 'U') IS NOT NULL DROP TABLE dbo.DateException;";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF OBJECT_ID('dbo.MonthException', 'U') IS NOT NULL DROP TABLE dbo.MonthException;";
            insert.ExecuteNonQuery();


            /*
             * insert.CommandText = "IF OBJECT_ID('dbo.CrossOverDate', 'U') IS NOT NULL DROP TABLE dbo.CrossOverDate;";
             * insert.ExecuteNonQuery();
             *
             *
             * insert.CommandText = "IF OBJECT_ID('dbo.EPODate', 'U') IS NOT NULL DROP TABLE dbo.EPODate;";
             * insert.ExecuteNonQuery();
             */

            insert.CommandText = "IF OBJECT_ID('dbo.Holiday', 'U') IS NOT NULL DROP TABLE dbo.Holiday;";
            insert.ExecuteNonQuery(); //SpecialException

            insert.CommandText = "IF OBJECT_ID('dbo.SpecialException', 'U') IS NOT NULL DROP TABLE dbo.SpecialException;";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF OBJECT_ID('dbo.Exception', 'U') IS NOT NULL DROP TABLE dbo.Exception;";
            insert.ExecuteNonQuery();


            insert.CommandText = "IF OBJECT_ID('dbo.Jurisdiction', 'U') IS NOT NULL DROP TABLE dbo.Jurisdiction;";
            insert.ExecuteNonQuery();



            //   insert.CommandText = "IF OBJECT_ID('dbo.ExtraContacts', 'U') IS NOT NULL DROP TABLE dbo.ExtraContacts;";
            //   insert.ExecuteNonQuery();

            insert.CommandText = "IF OBJECT_ID('dbo.Contacts', 'U') IS NOT NULL DROP TABLE dbo.Contacts;";
            insert.ExecuteNonQuery();


            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Holiday_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Officer_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Officer_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[InfractionGroup6_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[InfractionGroup6_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Login_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reminder_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Reminder_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpecialException_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[SpecialException_CRUD]";
            insert.ExecuteNonQuery();

            insert.CommandText = "IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contacts_CRUD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Contacts_CRUD]";
            insert.ExecuteNonQuery();



            insert.CommandText = "CREATE TABLE [dbo].[Jurisdiction] (JurisdictionID int IDENTITY(1,1),County varchar(50) NOT NULL, Court varchar(50) NOT NULL, DateType varchar(50) NOT NULL, PRIMARY KEY (JurisdictionID));";
            insert.ExecuteNonQuery();


            insert.CommandText = "CREATE TABLE [dbo].[Exception] (ExceptionID int IDENTITY(1,1), WeekOfMonth int, DayOfWeek int, Time time(0), Valid int, OldValid int, BusinessDayMin int, BusinessDayMax int, JurisdictionID int, PRIMARY KEY (ExceptionID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();



            insert.CommandText = "CREATE TABLE [dbo].[BasisGroup6] (ID int IDENTITY(1,1), " +
                                 "WeekOfMonth int, DayOfWeek int, Valid int, EffectiveDate Date, Time time(0), JurisdictionID int, PRIMARY KEY (ID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "create table [InfractionGroup6](ID int IDENTITY(1,1), FirstName varchar(50), LastName varchar(50), InfractionDate DateTime, Description varchar(200), County varchar(50) NOT NULL, Court varchar(50) NOT NULL,   HearingDate DateTime,  PRIMARY KEY (ID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "CREATE TABLE [dbo].[DateException] (ID int IDENTITY(1,1), ExceptionID int, ExceptionType int NOT NULL, DayCount int, Time time(0), Valid int, PRIMARY KEY (ID), foreign key (ExceptionID) references Exception (ExceptionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "CREATE TABLE [dbo].[MonthException] (MonthExceptionID int IDENTITY(1,1), Month int NOT NULL, MonthExceptionValid int, Time time(0), ExceptionID int, PRIMARY KEY (MonthExceptionID), foreign key (ExceptionID) references Exception (ExceptionID));";
            insert.ExecuteNonQuery();


            /*
             * insert.CommandText = "CREATE TABLE [dbo].[CrossOverDate] (ID int IDENTITY(1,1), ExceptionID int, ExceptionType int NOT NULL, Valid int, PRIMARY KEY (ID), foreign key (ExceptionID) references Exception (ExceptionID));";
             * insert.ExecuteNonQuery();
             *
             * insert.CommandText = "CREATE TABLE [dbo].[EPODate] (ID int IDENTITY(1,1), ExceptionID int, , ExceptionType int NOT NULL, Valid int, PRIMARY KEY (ID), foreign key (ExceptionID) references Exception (ExceptionID));";
             * insert.ExecuteNonQuery();
             */


            insert.CommandText = "CREATE TABLE [dbo].[Holiday] (HolidayID int IDENTITY(1,1), HolidayName varchar(50) NOT NULL, HolidayDate datetime NOT NULL, JurisdictionID int, PRIMARY KEY (HolidayID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "CREATE TABLE [dbo].[Officer] (OfficerID int IDENTITY(1,1), FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, OfficerType varchar(50) NOT NULL, OfficerDate datetime NOT NULL, JurisdictionID int, PRIMARY KEY (OfficerID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "CREATE TABLE [dbo].[Reminder] (ReminderID int IDENTITY(1,1), Description nvarchar(MAX) NOT NULL, JurisdictionID int, PRIMARY KEY (ReminderID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "CREATE TABLE [dbo].[SpecialException] (SpecialExceptionID int IDENTITY(1,1), SpecialExceptionName varchar(50) NOT NULL, SpecialExceptionDate datetime NOT NULL, JurisdictionID int, PRIMARY KEY (SpecialExceptionID), foreign key (JurisdictionID) references Jurisdiction (JurisdictionID));";
            insert.ExecuteNonQuery();

            insert.CommandText = "create table Contacts (ContactsID int IDENTITY(1,1), Department nvarchar(MAX), PersonName nvarchar(MAX), Location nvarchar(MAX), Address nvarchar(MAX), Phone varchar(50), Phone2 varchar(50), Fax varchar(50), Fax2 varchar(50), County varchar(50) NOT NULL,  PRIMARY KEY (ContactsID));";
            insert.ExecuteNonQuery();

            // insert.CommandText = "CREATE TABLE [dbo].[ExtraContacts] (ExtraContactsID int IDENTITY(1,1), PersonName varchar(50), PolyCom varchar(200), IP varchar(50), ContactsID int, PRIMARY KEY (ExtraContactsID), foreign key (ContactsID) references Contacts (ContactsID));";
            //  insert.ExecuteNonQuery();



            insert.CommandText = @"CREATE PROCEDURE [dbo].[Holiday_CRUD]
                                  @Action VARCHAR(10)
                                  ,@HolidayID INT = NULL
                                  ,@HolidayName VARCHAR(100) = NULL
                                  ,@HolidayDate datetime = NULL
	                              ,@County VARCHAR(100) = NULL
                                  ,@Court VARCHAR(100) = NULL
	                              ,@JurisdictionID INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT hd.HolidayID, hd.HolidayName, hd.HolidayDate, ju.County, ju.Court from Holiday as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT hd.HolidayID, hd.HolidayName, hd.HolidayDate, ju.County, ju.Court from Holiday as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID AND County = @County And Court = @Court;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE Holiday
                                        SET HolidayName = @HolidayName, HolidayDate = @HolidayDate
			                            , JurisdictionID = @JurisdictionID
                                        WHERE HolidayID = @HolidayID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM Holiday
                                        WHERE HolidayID = @HolidayID

                                  END
                            END";
            insert.ExecuteNonQuery();

            insert.CommandText = @"CREATE PROCEDURE [dbo].[Officer_CRUD]
                                  @Action VARCHAR(10)
                                  ,@OfficerID INT = NULL
                                  ,@FirstName VARCHAR(100) = NULL
	                              ,@LastName VARCHAR(100) = NULL
	                              ,@OfficerType VARCHAR(100) = NULL
                                  ,@OfficerDate datetime = NULL
	                              ,@County VARCHAR(100) = NULL
                                  ,@Court VARCHAR(100) = NULL
	                              ,@JurisdictionID INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT op.OfficerID, op.FirstName, op.LastName, op.OfficerType, op.OfficerDate, ju.County, ju.Court from Officer as op, Jurisdiction as ju where ju.JurisdictionID = op.JurisdictionID 
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT op.OfficerID, op.FirstName, op.LastName, op.OfficerType, op.OfficerDate, ju.County, ju.Court from Officer as op, Jurisdiction as ju where ju.JurisdictionID = op.JurisdictionID AND County = @County And Court = @Court;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE Officer
                                        SET FirstName = @FirstName, LastName = @LastName, OfficerType = @OfficerType
			                            , OfficerDate = @OfficerDate , JurisdictionID = @JurisdictionID
                                        WHERE OfficerID = @OfficerID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM Officer
                                        WHERE OfficerID = @OfficerID

                                  END
                            END";
            insert.ExecuteNonQuery();

            insert.CommandText = @"CREATE PROCEDURE [dbo].[InfractionGroup6_CRUD]
                                  @Action VARCHAR(10)
                                  ,@ID INT = NULL
                                  ,@FirstName VARCHAR(100) = NULL
	                              ,@LastName VARCHAR(100) = NULL
                                  ,@InfractionDate datetime = NULL
	                              ,@Description VARCHAR(100) = NULL
	                              ,@County VARCHAR(100) = NULL
                                  ,@Court VARCHAR(100) = NULL
	                              ,@HearingDate datetime = NULL
	                              ,@BasisID INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT * FROM [InfractionGroup6];
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT * FROM [InfractionGroup6] WHERE County = @County And Court = @Court;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE InfractionGroup6
                                        SET FirstName = @FirstName, LastName = @LastName, InfractionDate = @InfractionDate
			                            , Description = @Description , County = @County, Court = @Court
			                            , HearingDate = @HearingDate
                                        WHERE ID = @ID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM InfractionGroup6
                                        WHERE ID = @ID

                                  END
                            END";
            insert.ExecuteNonQuery();


            insert.CommandText = @"CREATE PROCEDURE [dbo].[Login_CRUD]
                                  @Action VARCHAR(10)
                                  ,@UserNumber INT = NULL
                                  ,@ID VARCHAR(100) = NULL
	                              ,@Password VARCHAR(100) = NULL
	                              ,@TypeOfUser INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT UserNumber, ID, Password, TypeOfUser from dbo.Login
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE dbo.Login
                                        SET ID = @ID, Password = @Password, TypeOfUser = @TypeOfUser
                                        WHERE UserNumber = @UserNumber
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM dbo.Login
                                        WHERE UserNumber = @UserNumber

                                  END
                            END";
            insert.ExecuteNonQuery();

            insert.CommandText = @"CREATE PROCEDURE [dbo].[SpecialException_CRUD]
                                  @Action VARCHAR(10)
                                  ,@SpecialExceptionID INT = NULL
                                  ,@SpecialExceptionName VARCHAR(100) = NULL
                                  ,@SpecialExceptionDate datetime = NULL
	                              ,@County VARCHAR(100) = NULL
                                  ,@Court VARCHAR(100) = NULL
	                              ,@JurisdictionID INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT hd.SpecialExceptionID, hd.SpecialExceptionName, hd.SpecialExceptionDate, ju.County, ju.Court from SpecialException as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT hd.SpecialExceptionID, hd.SpecialExceptionName, hd.SpecialExceptionDate, ju.County, ju.Court from SpecialException as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID AND County = @County And Court = @Court;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE SpecialException
                                        SET SpecialExceptionName = @SpecialExceptionName, SpecialExceptionDate = @SpecialExceptionDate
			                            , JurisdictionID = @JurisdictionID
                                        WHERE SpecialExceptionID = @SpecialExceptionID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM SpecialException
                                        WHERE SpecialExceptionID = @SpecialExceptionID

                                  END
                            END";
            insert.ExecuteNonQuery();


            insert.CommandText = @"CREATE PROCEDURE [dbo].[Reminder_CRUD]
                                  @Action VARCHAR(10)
                                  ,@ReminderID INT = NULL
                                  ,@Description nvarchar(MAX) = NULL
	                              ,@County VARCHAR(100) = NULL
                                  ,@Court VARCHAR(100) = NULL
					              ,@DateType VARCHAR(100) = NULL
	                              ,@JurisdictionID INT = NULL
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT hd.ReminderID, hd.Description, ju.County, ju.Court, ju.DateType from Reminder as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT hd.ReminderID, hd.Description, ju.County, ju.Court, ju.DateType from Reminder as hd, Jurisdiction as ju where ju.JurisdictionID = hd.JurisdictionID AND County = @County And Court = @Court AND DateType = @DateType;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE Reminder
                                        SET Description = @Description, JurisdictionID = @JurisdictionID
                                        WHERE ReminderID = @ReminderID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM Reminder
                                        WHERE ReminderID = @ReminderID

                                  END
                            END";
            insert.ExecuteNonQuery();


            insert.CommandText = @"CREATE PROCEDURE [dbo].[Contacts_CRUD]
                                  @Action VARCHAR(10)
                                  ,@ContactsID INT = NULL
                                  ,@Department nvarchar(MAX) = NULL
                                  ,@PersonName nvarchar(MAX) = NULL
	                              ,@Location nvarchar(MAX) = NULL
                                  ,@Address nvarchar(MAX) = NULL
				                 ,@Phone varchar(50) = NULL
				                 ,@Phone2 varchar(50) = NULL
				                 ,@Fax varchar(50) = NULL
				                 ,@Fax2 varchar(50) = NULL
				                ,@County varchar(50) = NULL
				
                            AS
                            BEGIN
                                  SET NOCOUNT ON;
 
                                  --SELECTALL
                                IF @Action = 'SELECTALL'
                                  BEGIN
			                            SELECT * from Contacts
                                  END

	                              --SELECT One
                                IF @Action = 'SELECT'
                                  BEGIN
			                            SELECT * from Contacts where County = @County;
                                  END
 
                                  --UPDATE
                                IF @Action = 'UPDATE'
                                  BEGIN
                                        UPDATE Contacts
                                        SET Department = @Department, PersonName = @PersonName
			                            , Location = @Location, Address = @Address, Phone = @Phone
										, Phone2 = @Phone2, Fax = @Fax, Fax2 = @Fax2
										, County = @County
                                        WHERE ContactsID = @ContactsID
                                  END
 
                                  --DELETE
                                IF @Action = 'DELETE'
                                  BEGIN
			                            DELETE FROM Contacts
                                        WHERE ContactsID = @ContactsID

                                  END
                            END";
            insert.ExecuteNonQuery();


            for (int countycount = 0; countycount < 10; countycount++)
            {
                for (int courtcount = 0; courtcount < 3; courtcount++)
                {
                    for (int datecount = 0; datecount < 4; datecount++)
                    {
                        insert.CommandText =
                            "insert into [dbo].[Jurisdiction] (County, Court, DateType) values ('" + CountyNames[countycount] + "', '" + CourtNames[courtcount] + "', '" + DateNames[datecount] + "')";

                        insert.ExecuteNonQuery();
                    }
                }
            }

            int LastIDNumber = LastID();

            if (LastIDNumber == 0)
            {
                //error here
                Label1.Text = "Last id error";
                return;
            }



            for (int k = 1; k < LastIDNumber + 1; k++)
            {
                for (int WoM = 1; WoM < 6; WoM++)
                {
                    for (int doW = 0; doW < 7; doW++)
                    {
                        insert.CommandText =
                            "insert into [dbo].[Exception] (WeekOfMonth, DayOfWeek, Time, Valid, OldValid, BusinessDayMin, BusinessDayMax, JurisdictionID) values (" + WoM + ", " + doW + ",'0:00', 0, 0, 0, 0, " + k + ")";

                        insert.ExecuteNonQuery();


                        DateTime defaultDate = new DateTime(2000, 1, 1);
                        insert.CommandText =
                            "insert into [dbo].[BasisGroup6] (WeekOfMonth, DayOfWeek, Valid, EffectiveDate, Time, JurisdictionID) values (" + WoM + "," + doW + ", 0 , '" + defaultDate + "', '0:00', " + k + ")";

                        insert.ExecuteNonQuery();
                    }
                }
            }



            sc.Close();



            // foreach (var court in CourtNames)
            {
                Alleghany a1 = new Alleghany();

                Augusta             a2  = new Augusta();
                Bath                a3  = new Bath();
                Botetourt           a4  = new Botetourt();
                BuenaVista          a5  = new BuenaVista();
                Craig               a6  = new Craig();
                Highland            a7  = new Highland();
                LexingtonRockbridge a8  = new LexingtonRockbridge();
                Staunton            a9  = new Staunton();
                Waynesboro          a10 = new Waynesboro();
                Label1.Text += a1.test();
            }



            // Label1.Text = "Reset Successssss";
        }
        catch (Exception ex)
        {
            Label1.Text = " error : " + ex;
            // Log_Show("Setting Default Error : " + ex, 3, Label1);
        }
    }
Example #45
0
    public static void LogMessage(string _message, bool message, bool warning, bool error)
    {
        // Remueve espacios en el mensaje y valida que no sea nulo o 
        _message.Trim();
        if (_message == null || _message.Length == 0)
        {
            return;
        }

        // Si no se pone en true alguna de las variables, se reotrna excepcion
        if (!_logToConsole && !_logToFile && !LogToDatabase)
        {
            throw new Exception("Invalid configuration");
        }
        if ((!_logError && !_logMessage && !_logWarning) || (!message && !warning && !error))
        {
            throw new Exception("Error or Warning or Message must be specified");
        }


        /*
         * SOLO BASE DE DATOS
         * */

        // CRrea cadena de conexiòn

        System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
        connection.Open();
        //int t;
        int t = 0;
        if (message && _logMessage)
        {
            t = 1;
        }
        if (error && _logError)
        {
            t = 2;
        }
        if (warning && _logWarning)
        {
            t = 3;
        }
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("Insert into Log Values('" + _message + "', " + t.ToString() + ")");
        command.ExecuteNonQuery();

        /*
         * SOLO ARCHIVO
         * */
        //string l;
        string l = string.Empty;
        if (!System.IO.File.Exists(System.Configuration.ConfigurationManager.AppSettings["LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt"))
        {
            l = System.IO.File.ReadAllText(System.Configuration.ConfigurationManager.AppSettings["LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt");
        }
        if (error && _logError)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }
        if (warning && _logWarning)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }
        if (message && _logMessage)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }

        System.IO.File.WriteAllText(System.Configuration.ConfigurationManager.AppSettings[
        "LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt", l);




        /*
         * SOLO CONSOLA
         * */
        if (error && _logError)
        {
            Console.ForegroundColor = ConsoleColor.Red;
        }
        if (warning && _logWarning)
        {
            Console.ForegroundColor = ConsoleColor.Yellow;
        }
        if (message && _logMessage)
        {
            Console.ForegroundColor = ConsoleColor.White;
        }
        Console.WriteLine(DateTime.Now.ToShortDateString() + message);
    }
Example #46
0
    protected void btnSubmit(object sender, EventArgs e)
    {
        int personid = -1;

        string firstNameA      = firstName.Text.ToString();
        string lastNameA       = lastName.Text.ToString();
        string userNameA       = email.Text.ToString();
        string passwordHashA   = password.Text.ToString(); // GET PASSWORD HASH WORKING
        string userTypeA       = "Applicant";
        string emailA          = email.Text.ToString();
        string middleInitialA  = "J";
        string primaryPhoneA   = phone.Text.ToString();
        string secondaryPhoneA = " ";
        string cityA           = city.Text.ToString();
        string countyA         = " ";
        string stateA          = state.SelectedValue.ToString();
        string countryA        = "United States";
        string zipA            = zip.Text.ToString();
        string dob1A           = " "; // GET DATE OF BIRTH WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
        string dob2A           = " ";

        string streetA = address.Text.ToString();
        string statusA = "Applicant";
        // RABIES VACCINATION NEEDS TO BE PULLED AND DATE ASSIGNED IN SQL STATEMENT

        string lastVolunteeredA = " "; // GET THIS WORKING. CURRENTLY HAVE GETDATE() in SQL STATEMENT
        string allergiesA       = allergiesWriteUp.Text.ToString();

        string workOutsideA            = " ";
        int    totalHoursA             = 0;                                  // AUTOMATICALLY 0 SINCE THEY DID NOT START WORKING YET
        string workOutsideLimitationsA = limitationsWriteUp.Text.ToString(); // NOT ON THE FORM?
        string lift40A         = pounds.Text.ToString();                     // THESE FIELDS ARE NOT ON THE FORM
        int    permitRehabA    = 0;
        string travelA         = availability.SelectedValue.ToString();
        string experienceA     = experience.Text.ToString();
        string deadAnimalsA    = deadAnimals.Text.ToString();
        string livePreyA       = livePrey.Text.ToString();
        string groupsA         = groups.Text.ToString();
        string accomplishA     = accomplish.Text.ToString();
        string issueA          = issue.Text.ToString();
        string additionalInfoA = additionalInfo.Text.ToString();


        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Server=LOCALHOST; Database=Wildlife;Trusted_Connection=Yes;";

        sc.Open();

        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = sc;



        insert.CommandText = "INSERT INTO Person (Person_UserName, Person_PasswordHash, Person_UserType, Person_FirstName, Person_MiddleName, Person_LastName, Person_Email, Person_PhonePrimary, Person_PhoneAlternate, Person_StreetAddress, Person_City, Person_County, Person_State, Person_Country, Person_ZipCode, Person_DateOfBirth, Person_Status, Person_RabbiesVaccinationDate, Person_RehabilitatePermitCategory, Person_Allergies, " +
                             "Person_WorkOutside, Person_OutsideLimitations, Person_Lift40Lbs, Person_TotalVolunteeredHours, Person_LastVolunteered)" +
                             " VALUES ('" + userNameA + "', '" + passwordHashA + "', '" + userTypeA + "', '" + firstNameA + "', '" + middleInitialA + "', '" + lastNameA + "', '" + emailA + "', '" + primaryPhoneA + "', '" + secondaryPhoneA + "', '" + streetA + "', '" + cityA + "', '" + countyA + "', '" + stateA + "', '" + countryA + "', '" + zipA + "', getdate(), " + " '" + statusA + "', getdate(), '" + permitRehabA + "', '" + allergiesA + "', '" +
                             workOutsideA + "', '" + workOutsideLimitationsA + "', '" + lift40A + "', " + totalHoursA + ", getdate())";

        insert.ExecuteNonQuery();



        int depNum = 2;


        insert.CommandText = "SELECT MAX(Person_ID) FROM Person";
        insert.ExecuteNonQuery();


        System.Data.SqlClient.SqlDataReader reader = insert.ExecuteReader();



        if (reader.HasRows)
        {
            while (reader.Read())
            {
                personid = reader.GetInt32(0);
            }

            reader.Close();



            insert.CommandText = "INSERT INTO AnimalCareApp (AnimalCareApp_PersonID, AnimalCareApp_HandsOnExperience, AnimalCareApp_HandleDeadAnimals, AnimalCareApp_OpinionLivePrey, AnimalCareApp_WorkOutside, AnimalCareApp_BelongToAnimalRightsGroup, AnimalCareApp_HopeToLearnAccomplish, AnimalCareApp_PassionateWildlifeIssue, AnimalCareApp_MoreAboutExperience) " +
                                 "VALUES (" + personid + ", '" + experienceA + "', '" + deadAnimalsA + "', '" + livePreyA + "', '" + workOutsideA + "', '" + groupsA + "', '" + accomplishA + "', '" + issueA + "', '" + additionalInfoA + "')";

            insert.ExecuteNonQuery();
        }
    }
Example #47
0
    protected void btnChangePswd_Click(object sender, EventArgs e)
    {
        // need to check if entered password matches password in db then go ahead ahd change password in db

        string currentPswdEntered = txtCurrentPswd.Text;
        string newPassword        = txtNewPassword.Text;
        string confirmNewPswd     = txtConfirmNew.Text;
        string newPasswordHash;

        // get password has from the database
        string passwordHash = "";

        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
        con.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
        con.Open();
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        command.Connection  = con;
        command.CommandText = "select top 1 PasswordHash from dbo.login where EmployeeID = @EmployeeID";
        command.Parameters.AddWithValue("@EmployeeID", Session["EmployeeID"]);
        passwordHash = (string)command.ExecuteScalar();

        bool verify = Login_Class.VerifyHash(currentPswdEntered, "MD5", passwordHash);

        if (verify.ToString().Equals("True"))
        {
            // check if the new password matches the confirm new password
            if (confirmNewPswd.Equals(newPassword))
            {
                // change password in database
                // create new hash
                newPasswordHash = Login_Class.ComputeHash(confirmNewPswd, "MD5", null);

                // update table in database

                //Database Connection
                System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
                sc.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
                System.Data.SqlClient.SqlCommand update = new System.Data.SqlClient.SqlCommand();
                update.Connection = sc;
                // UPDATE STATEMENT
                sc.Open();
                update.CommandText = "update Login set Password = @newPassword, PasswordHash = @passwordHash where EmployeeID = @CurrentEmpId";
                update.Parameters.AddWithValue("@newPassword", newPassword);
                update.Parameters.AddWithValue("@passwordHash", newPasswordHash);
                update.Parameters.AddWithValue("@CurrentEmpId", Session["EmployeeID"]);
                update.ExecuteNonQuery();
                sc.Close();

                //clear all textbozes and hide change password controls
                lblTitleChangePswd.Visible = false;
                lblNewPassword.Visible     = false;
                lblCurrentPswd.Visible     = false;
                lblConfirmNew.Visible      = false;
                btnCancel.Visible          = false;
                btnChangePswd.Visible      = false;
                lblChangePswdError.Text    = "";
                lblChangePswdError.Visible = false;
                txtNewPassword.Text        = "";
                txtCurrentPswd.Text        = "";
                txtConfirmNew.Text         = "";
                txtConfirmNew.Visible      = false;
                txtCurrentPswd.Visible     = false;
                txtNewPassword.Visible     = false;
            }
            else
            {
                lblChangePswdError.Text = "The Confirm New Password must match the New Password entry.";
            }
        }
        else
        {
            lblChangePswdError.Text = "Incorrect password.";
        }
    }
Example #48
0
    protected void Submit_Click(object sender, EventArgs e)
    {
        //------------------------------------INSERT INTO EMPLOYEELOGIN------------------------------------------------------------------------------------
        try
        {
            SqlConnection conn = ProjectDB.connectToDB();
            try
            {
                System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
                insert.Connection = conn;

                //-----------------------------GETS MAX EMPLOYEELOGINID--------------------------------------------------------------------------------------
                System.Data.SqlClient.SqlCommand select = new System.Data.SqlClient.SqlCommand();
                select.Connection  = conn;
                select.CommandText = "(select max(([EmpLoginID]) +1) from[dbo].[EmployeeLogin])";
                var    temp  = select.ExecuteScalar();
                string maxID = temp.ToString();
                currentUser = new NewUser(FirstNameText.Text, LastNameText.Text, MiddleText.Text, EmailText.Text, maxID, LastUpdatedBy, LastUpdate);
                //-----------------------------GETS MAX EMPLOYEELOGINID--------------------------------------------------------------------------------------

                string username = (currentUser.getLastName() + currentUser.getFirstName().Substring(0, 1) + currentUser.getMiddleName()).ToLower();
                string password = "******";



                insert.CommandText = "INSERT INTO [dbo].[EmployeeLogin] ([UserName],[PasswordHash],[LastLogin],[LastUpdatedBy],[LastUpdated]) VALUES (@userName, @password, @lastLogin, @lastUpdatedBy, @lastUpdate)";

                insert.Parameters.AddWithValue("@userName", username);
                insert.Parameters.AddWithValue("@password", SimpleHash.ComputeHash(password, "MD5", null));
                insert.Parameters.AddWithValue("@lastLogin", DateTime.Now.ToString());
                insert.Parameters.AddWithValue("@lastUpdatedBy", LastUpdatedBy);
                insert.Parameters.AddWithValue("@lastUpdate", LastUpdate);

                insert.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception)
            {
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('error inserting into DB for employeelogin Boiiiii')", true);
            }
        }
        catch (Exception)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('error connecting to DB')", true);
        }
        //--------------------------------------------END OF INSERT INTO EMPLOYEELOGIN-------------------------------------------------------


        //--------------------------------------------INSERTING INTO EMPLOYEE TABLE----------------------------------------------------------



        try
        {
            SqlConnection conn = ProjectDB.connectToDB();
            try
            {
                string points = "0";
                System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
                insert.Connection = conn;

                //------------------------------------------------------INSERTS WITH PARAMETERIZED QUERIES----------------------------------------------------
                insert.CommandText = "INSERT INTO[dbo].[Employee]([FirstName],[LastName],[Email],[LastUpdatedBy],[LastUpdated],[EmpLoginID],[Points]) VALUES(@firstName, @lastName, @email, @lastUpdatedBy, @lastUpdated, @empLoginID, @points)";



                insert.Parameters.AddWithValue("@firstName", currentUser.getFirstName());
                insert.Parameters.AddWithValue("@lastName", currentUser.getLastName());
                insert.Parameters.AddWithValue("@email", currentUser.getEmail());
                insert.Parameters.AddWithValue("@lastUpdatedBy", currentUser.getLastUpdatedBy());
                insert.Parameters.AddWithValue("@lastUpdated", currentUser.getLastUpdate());
                insert.Parameters.AddWithValue("@empLoginID", currentUser.getEmployeeLoginID());
                insert.Parameters.AddWithValue("@points", points);

                insert.ExecuteNonQuery();
                conn.Close();
                SuccessLabel.Text = "*User Successfully Created";
            }
            //------------------------------------------------------INSERTS WITH PARAMETERIZED QUERIES----------------------------------------------------
            catch (Exception)
            {
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('error inserting into DB for employee')", true);
            }
        }
        catch (Exception)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('error connecting to DB')", true);
        }
    }
Example #49
0
        private void btnguardar_Click(object sender, EventArgs e)
        {
            if (cboxestatus.Text.Trim() == "1".Trim() || cboxestatus.Text.Trim() == "True".Trim())
            {
                DialogResult result = MessageBox.Show("¿Seguro que quiere hacer ese cambio?", "Advertencia", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                if (result == DialogResult.Yes)
                {
                    conexion.Close();
                    comando.CommandType = CommandType.StoredProcedure;
                    comando.CommandText = "SP_Producto";
                    comando.Parameters.Clear();
                    comando.Parameters.AddWithValue("@OP", 2);
                    comando.Parameters.AddWithValue("@Pr_Id", txtidp.Text);
                    comando.Parameters.AddWithValue("@Pr_Nombre", txtNombre.Text);
                    comando.Parameters.AddWithValue("@Pr_Precio ", txtPrecio.Text);
                    comando.Parameters.AddWithValue("@Pr_Existencia ", Txtexist.Text);
                    comando.Parameters.AddWithValue("@Pr_Estatus", cboxestatus.Text);
                    cboxestatus.Text   = "1";
                    comando.Connection = conexion;
                    conexion.Open();
                    comando.ExecuteNonQuery();
                    conexion.Close();
                    MessageBox.Show("Se agregado los cambios,correctamente", "Exito", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    Clases.clauditoria.auditoria("El " + Clases.clusuario.usuario + " Hizo un cambio con el producto " + txtNombre + " y se encuentra" + cboxestatus);
                    Txtexist.Enabled = false;
                    txtidp.Enabled   = true;
                    txtidp.Clear();
                    txtidp.Focus();
                    txtNombre.Clear();
                    txtNombre.Enabled = false;
                    txtPrecio.Clear();
                    txtPrecio.Enabled = false;
                    Txtexist.Clear();
                    cboxestatus.SelectedIndex = -1;
                    cboxestatus.Enabled       = false;
                }
                if (result == DialogResult.No)
                {
                    cboxestatus.Focus();
                }
            }
            else if (cboxestatus.Text.Trim() == "0".Trim() || cboxestatus.Text.Trim() == "False".Trim())
            {
                DialogResult result = MessageBox.Show("¿Seguro que quiere hacer ese cambio?", "Advertencia", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                if (result == DialogResult.Yes)
                {
                    conexion.Close();
                    comando.CommandType = CommandType.StoredProcedure;
                    comando.CommandText = "SP_Producto";
                    comando.Parameters.Clear();
                    comando.Parameters.AddWithValue("@OP", 2);
                    comando.Parameters.AddWithValue("@Pr_Id", txtidp.Text);
                    comando.Parameters.AddWithValue("@Pr_Nombre", txtNombre.Text);
                    comando.Parameters.AddWithValue("@Pr_Precio ", txtPrecio.Text);
                    comando.Parameters.AddWithValue("@Pr_Existencia ", Txtexist.Text);
                    comando.Parameters.AddWithValue("@Pr_Estatus", "1");
                    comando.Connection = conexion;
                    conexion.Open();
                    comando.ExecuteNonQuery();
                    conexion.Close();
                    MessageBox.Show("Se agregado los cambios,correctamente", "Exito", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    Clases.clauditoria.auditoria("El " + Clases.clusuario.usuario + " Hizo un cambio con el producto " + txtNombre + " y se encuentra" + cboxestatus);
                    Txtexist.Enabled = false;
                    txtidp.Enabled   = true;
                    txtidp.Clear();
                    txtidp.Focus();
                    txtNombre.Clear();
                    txtNombre.Enabled = false;
                    txtPrecio.Clear();
                    txtPrecio.Enabled = false;
                    Txtexist.Clear();
                    cboxestatus.SelectedIndex = -1;
                    cboxestatus.Enabled       = false;
                }
                if (result == DialogResult.No)
                {
                    cboxestatus.Focus();
                }
            }
            else
            {
                MessageBox.Show("Error El Estatus no puede ir Sin  ningun valor", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cboxestatus.Focus();
            }
        }
Example #50
0
    //determines crudentials are correct, inserts new information into the database to create a new user
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (txtFirstName.Text != "" && txtLastName.Text != "" && txtPassword.Text != "" && txtUsername.Text != "") // all fields must be filled out
        {
            if (validatePassword(txtPassword.Text.ToString()) == true)
            {
                // COMMIT VALUES
                try
                {
                    System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
                    String cs = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString;
                    sc.ConnectionString = cs;
                    sc.Open();


                    bool exists = false;

                    // create a command to check if the username exists
                    SqlCommand cmd = new SqlCommand("select Count(*) from [dbo].[Person] where Username = @UserName", sc);

                    cmd.Parameters.AddWithValue("@UserName", txtUsername.Text);

                    exists = (int)cmd.ExecuteScalar() > 0;

                    // if exists, show a message error
                    if (!exists)
                    {
                        System.Data.SqlClient.SqlCommand createUser = new System.Data.SqlClient.SqlCommand();
                        createUser.Connection = sc;


                        // INSERT USER RECORD
                        createUser.CommandText = "insert into[dbo].[Person] values(@FName, @LName, @Username, @Email, @Status, @PersonCategory)";
                        createUser.Parameters.Add(new SqlParameter("@FName", txtFirstName.Text));
                        createUser.Parameters.Add(new SqlParameter("@LName", txtLastName.Text));
                        createUser.Parameters.Add(new SqlParameter("@Username", txtUsername.Text));
                        createUser.Parameters.Add(new SqlParameter("@Email", txtEmail.Text));
                        createUser.Parameters.Add(new SqlParameter("@Status", "Active"));

                        switch (ddlStaffType.SelectedItem.Value.ToString())
                        {
                        case "0":
                            break;

                        case "1":
                            createUser.Parameters.Add(new SqlParameter("@PersonCategory", "O"));
                            break;

                        case "2":
                            createUser.Parameters.Add(new SqlParameter("@PersonCategory", "V"));
                            break;
                        }

                        createUser.ExecuteNonQuery();

                        System.Data.SqlClient.SqlCommand setPass = new System.Data.SqlClient.SqlCommand();
                        setPass.Connection = sc;
                        // INSERT PASSWORD RECORD AND CONNECT TO USER
                        setPass.CommandText = "insert into[dbo].[Pass] values((select max(userid) from person), @Username, @Password)";
                        setPass.Parameters.Add(new SqlParameter("@Username", txtUsername.Text));
                        setPass.Parameters.Add(new SqlParameter("@Password", PasswordHash.HashPassword(txtPassword.Text))); // hash entered password
                        setPass.ExecuteNonQuery();

                        sc.Close();


                        System.Data.SqlClient.SqlConnection sc1 = new System.Data.SqlClient.SqlConnection();
                        String cs1 = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString;
                        sc1.ConnectionString = cs1;
                        sc1.Open();

                        switch (ddlStaffType.SelectedValue.ToString())
                        {
                        case "0":
                            break;

                        case "1":
                            System.Data.SqlClient.SqlCommand createOutreachCoordinator = new System.Data.SqlClient.SqlCommand();
                            createOutreachCoordinator.Connection = sc1;

                            //INSERT INTO table_name (column1, column2, column3, ...)
                            //VALUES(value1, value2, value3, ...);
                            string lastUpdateBy = "Wildlife Center";
                            string status       = "Active";
                            string category     = "O";

                            // INSERT USER RECORD
                            createOutreachCoordinator.CommandText = "insert into[dbo].[Educators] (Username, EducatorFirstName, " +
                                                                    "EducatorLastName, LastUpdated, LastUpdatedBy, Status, EducatorPhoneNumber, EducatorEmail," +
                                                                    " EducatorCategory)" +
                                                                    "  values (@userName, @FName, @LName, @LU, @LUB, @Status, @PhoneNum, @Email, @PersonCategory)";
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@userName", txtUsername.Text));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@FName", txtFirstName.Text));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@LName", txtLastName.Text));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@LU", DateTime.Now.ToShortDateString()));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@LUB", lastUpdateBy));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@Status", status));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@PhoneNum", txtPhoneNumber.Text));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@Email", txtEmail.Text));
                            createOutreachCoordinator.Parameters.Add(new SqlParameter("@PersonCategory", category));
                            createOutreachCoordinator.ExecuteNonQuery();

                            break;

                        case "2":
                            System.Data.SqlClient.SqlCommand createVolunteer = new System.Data.SqlClient.SqlCommand();
                            createVolunteer.Connection = sc1;
                            string lastUpdateBy1 = "Wildlife Center";
                            string status1       = "Active";
                            string category1     = "V";
                            //INSERT INTO table_name (column1, column2, column3, ...)
                            //VALUES(value1, value2, value3, ...);


                            // INSERT USER RECORD
                            createVolunteer.CommandText = "insert into[dbo].[Volunteers] (Username, VolunteerFirstName, VolunteerLastName," +
                                                          " VolunteerPhoneNumber, VolunteerEmail, VolunteerStatus, LastUpdated, LastUpdatedBy, VolunteerCategory)" +
                                                          "  values (@userName, @FName, @LName, @PhoneNum, @Email, @Status, @LU, @LUB, @PersonCategory)";
                            createVolunteer.Parameters.Add(new SqlParameter("@userName", txtUsername.Text));
                            createVolunteer.Parameters.Add(new SqlParameter("@FName", txtFirstName.Text));
                            createVolunteer.Parameters.Add(new SqlParameter("@LName", txtLastName.Text));
                            createVolunteer.Parameters.Add(new SqlParameter("@PhoneNum", txtPhoneNumber.Text));
                            createVolunteer.Parameters.Add(new SqlParameter("@Email", txtEmail.Text));
                            createVolunteer.Parameters.Add(new SqlParameter("@Status", "Active"));
                            createVolunteer.Parameters.Add(new SqlParameter("@LU", DateTime.Now.ToShortDateString()));
                            createVolunteer.Parameters.Add(new SqlParameter("@LUB", lastUpdateBy1));
                            createVolunteer.Parameters.Add(new SqlParameter("@PersonCategory", category1));
                            createVolunteer.ExecuteNonQuery();

                            break;
                        }


                        lblStatus.Text          = "User committed!";
                        txtEmail.Enabled        = false;
                        txtFirstName.Enabled    = false;
                        txtLastName.Enabled     = false;
                        txtUsername.Enabled     = false;
                        txtPassword.Enabled     = false;
                        btnSubmit.Enabled       = false;
                        chkShowPassword.Visible = false;
                        // Response.Redirect("Program.aspx", false);
                    }
                    else
                    {
                        lblStatus.Text = "This username " + HttpUtility.HtmlEncode(txtUsername.Text) + " has been created already.";
                    }
                }

                catch (SqlException)
                {
                    lblStatus.Text = "fix error.";
                }
                catch
                {
                    lblStatus.Text = "Database Error - User not committed.";
                }
            }
        }
        else
        {
            lblStatus.Text = "Fill in a value for all fields.";
        }
    }
        private void saveboton_Click(object sender, EventArgs e)
        {
            String fn    = dia.Text + "" + mes.Text + "" + anno.Text;
            String sex   = sexMax.Text;
            int    idusr = 2;

            if (ciedu.Text != "" && ciedu.Font.Italic == true)
            {
                if (ciedu.Text == "")
                {
                    MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else if (ciedu.Text == "")
            {
                MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (nameus.Text == "" || nameus.Font.Italic == true)
            {
                MessageBox.Show("Ingrese el nombre de Identidad del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (passedus.Text == "" || passedus.Font.Italic == true)
            {
                MessageBox.Show("Ingrese la Contraseña del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {  //Codigo de editar Cliente.
                if (comprobar() == 1)
                {
                    if (passedus.Text == confpass.Text)
                    {
                        if (sexFem.Checked == true)
                        {
                            sex = sexFem.Text;
                            try
                            {   // Objetos de conexión y comando
                                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                                // Asignando el valor de la imagen
                                // Stream usado como buffer
                                System.IO.MemoryStream mss = new System.IO.MemoryStream();
                                // Se guarda la imagen en el buffer
                                fotoUser.Image.Save(mss, System.Drawing.Imaging.ImageFormat.Jpeg);

                                // Estableciento propiedades
                                cmd.Connection  = cs.GetCONN();
                                cmd.CommandText = "UPDATE Usuario SET ciUser=@ci, nombreUser=@nom, apeUser=@ape, nuUsuario=@name, contraUser=@pass, dirUser=@dir, nivelUser=@nivel, cargoUser=@cargo, sexoUser=@sexo, fechaNaci=@fecha, telUser=@tel, celUser=@cel, faxUser=@fax, fotoUser= @imagen, emailUser=@email, idUser=@iduser, CodDepaU=@codd  WHERE ciUser = @ciid ;";

                                cmd.Parameters.Add("@ci", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@ape", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@name", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@pass", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@dir", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@nivel", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@cargo", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@sexo", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@fecha", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@tel", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@cel", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@fax", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@imagen", System.Data.SqlDbType.Image);
                                cmd.Parameters.Add("@email", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@iduser", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@codd", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@ciid", System.Data.SqlDbType.Int);

                                // Asignando los valores a los atributos
                                cmd.Parameters["@ci"].Value     = int.Parse(cius.Text);
                                cmd.Parameters["@nom"].Value    = nameus.Text;
                                cmd.Parameters["@ape"].Value    = apelus.Text;
                                cmd.Parameters["@name"].Value   = nomedus.Text;
                                cmd.Parameters["@pass"].Value   = passedus.Text;
                                cmd.Parameters["@dir"].Value    = diredu.Text;
                                cmd.Parameters["@nivel"].Value  = nivelesus.Text;
                                cmd.Parameters["@cargo"].Value  = cargoedus.Text;
                                cmd.Parameters["@sexo"].Value   = sex;
                                cmd.Parameters["@fecha"].Value  = fn;
                                cmd.Parameters["@tel"].Value    = int.Parse(teledu.Text);
                                cmd.Parameters["@cel"].Value    = int.Parse(celedu.Text);
                                cmd.Parameters["@fax"].Value    = int.Parse(faxedu.Text);
                                cmd.Parameters["@imagen"].Value = mss.GetBuffer();
                                cmd.Parameters["@email"].Value  = corredu.Text;
                                cmd.Parameters["@iduser"].Value = idusr;
                                cmd.Parameters["@codd"].Value   = id;
                                cmd.Parameters["@ciid"].Value   = int.Parse(ciedu.Text);

                                cs.OpenCnn();
                                cmd.ExecuteNonQuery();
                                cs.CerrarCnn();

                                Messengers mr = new Messengers();
                                mr.textolb.Text = "Usuario Modificado";
                                mr.ShowDialog();
                                this.Close();
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show("ERROR. Al Modificar Los Datos. \n" + ex.Message + "\nCompruebe Que No Exista Datos Nulos o Vacios con el Usuario a Modificar.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                        else
                        {
                            try
                            {   // Objetos de conexión y comando
                                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                                System.IO.MemoryStream mss = new System.IO.MemoryStream();
                                fotoUser.Image.Save(mss, System.Drawing.Imaging.ImageFormat.Jpeg);

                                // Estableciento propiedades
                                cmd.Connection  = cs.GetCONN();
                                cmd.CommandText = "UPDATE Usuario SET ciUser=@ci, nombreUser=@nom, apeUser=@ape, nuUsuario=@name, contraUser=@pass, dirUser=@dir, nivelUser=@nivel, cargoUser=@cargo, sexoUser=@sexo, fechaNaci=@fecha, telUser=@tel, celUser=@cel, faxUser=@fax, fotoUser= @imagen, emailUser=@email, idUser=@iduser, CodDepaU=@codd  WHERE ciUser = @ciid ;";

                                cmd.Parameters.Add("@ci", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@ape", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@name", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@pass", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@dir", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@nivel", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@cargo", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@sexo", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@fecha", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@tel", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@cel", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@fax", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@imagen", System.Data.SqlDbType.Image);
                                cmd.Parameters.Add("@email", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@iduser", System.Data.SqlDbType.Int);
                                cmd.Parameters.Add("@codd", System.Data.SqlDbType.VarChar);
                                cmd.Parameters.Add("@ciid", System.Data.SqlDbType.Int);

                                // Asignando los valores a los atributos
                                cmd.Parameters["@ci"].Value     = int.Parse(cius.Text);
                                cmd.Parameters["@nom"].Value    = nameus.Text;
                                cmd.Parameters["@ape"].Value    = apelus.Text;
                                cmd.Parameters["@name"].Value   = nomedus.Text;
                                cmd.Parameters["@pass"].Value   = passedus.Text;
                                cmd.Parameters["@dir"].Value    = diredu.Text;
                                cmd.Parameters["@nivel"].Value  = nivelesus.Text;
                                cmd.Parameters["@cargo"].Value  = cargoedus.Text;
                                cmd.Parameters["@sexo"].Value   = sex;
                                cmd.Parameters["@fecha"].Value  = fn;
                                cmd.Parameters["@tel"].Value    = int.Parse(teledu.Text);
                                cmd.Parameters["@cel"].Value    = int.Parse(celedu.Text);
                                cmd.Parameters["@fax"].Value    = int.Parse(faxedu.Text);
                                cmd.Parameters["@imagen"].Value = mss.GetBuffer();
                                cmd.Parameters["@email"].Value  = corredu.Text;
                                cmd.Parameters["@iduser"].Value = idusr;
                                cmd.Parameters["@codd"].Value   = id;
                                cmd.Parameters["@ciid"].Value   = int.Parse(ciedu.Text);

                                cs.OpenCnn();
                                cmd.ExecuteNonQuery();
                                cs.CerrarCnn();

                                Messengers mr = new Messengers();
                                mr.textolb.Text = "Usuario Modificado";
                                mr.ShowDialog();
                                this.Close();
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show("ERROR. Al Modificar Los Datos. " + ex.Message + "\nCompruebe Que No Exista Datos Nulos o Vacios con el Usuario a Modificar.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                    }
                    else
                    {
                        MessageBox.Show("Error, Debe Confirmar que las Contraseñas sean Iguales.", "ERROR",
                                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                else
                {
                    MessageBox.Show("Error, No Existen Registros del Usuario.", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
    protected void submitLogin_Click(object sender, EventArgs e)
    {
        //makes sure username does not already exist in the database
        String userNew = userNameTextbox.Text;

        Session["username"] = userNew;
        sc.Open();
        SqlCommand userCheck = new SqlCommand("SELECT Count(*) FROM [dbo].[Login] WHERE lower(Username) = @Username", sc);

        userCheck.Parameters.AddWithValue("@Username", userNew);
        userCheck.Connection = sc;
        int count = Convert.ToInt32(userCheck.ExecuteScalar());

        userCheck.ExecuteNonQuery();
        sc.Close();


        System.Data.SqlClient.SqlCommand insertHost = new System.Data.SqlClient.SqlCommand();
        insertHost.Connection = sc;
        System.Data.SqlClient.SqlCommand insertLogin = new System.Data.SqlClient.SqlCommand();
        insertLogin.Connection = sc;

        firstName   = Session["firstName"].ToString();
        lastName    = Session["lastName"].ToString();
        gender      = Session["gender"].ToString();
        dateOfBirth = Session["dateOfBirth"].ToString();
        email       = Session["email"].ToString();
        phoneNumber = Session["phoneNumberTextbox"].ToString();

        //confirms that the passwords match and meet requirements
        String password  = passwordTextbox.Text;
        String cpassword = confirmPasswordTextbox.Text;

        Session["password"] = password;

        if (count == 0)
        {
            bool isValid;
            if (password.Length > 8 && (isValid = ValidatePassword(password)))
            {
                if (password == cpassword)
                {
                    Homeowner newHost = new Homeowner(firstName, lastName, gender, dateOfBirth, email, phoneNumber, userNameTextbox.Text, passwordTextbox.Text, confirmPasswordTextbox.Text);
                    resultmessage.Text     = "";
                    insertHost.CommandText = "INSERT INTO [dbo].[Host] (Email, PhoneNumber, Firstname, MiddleName, LastName, BirthDate," +
                                             "Gender, BackgroundCheckDate, BackgroundCheckResult, LastUpdatedBy, LastUpdated, HostBio, ShowHost) VALUES (@Email, @PhoneNumber, @FirstName, @MiddleName," +
                                             "@LastName, @BirthDate, @Gender, @BackgroundCheckDate, @BackgroundCheckResult, @LastUpdatedBy, @LastUpdated, @HostBio, @ShowHost); ";

                    insertHost.Parameters.AddWithValue("@Email", email);
                    insertHost.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
                    insertHost.Parameters.AddWithValue("@FirstName", firstName);
                    insertHost.Parameters.AddWithValue("@MiddleName", " ");
                    insertHost.Parameters.AddWithValue("@LastName", lastName);
                    insertHost.Parameters.AddWithValue("@BirthDate", dateOfBirth);
                    insertHost.Parameters.AddWithValue("@Gender", gender);
                    insertHost.Parameters.AddWithValue("@BackgroundCheckDate", DateTime.Now);
                    insertHost.Parameters.AddWithValue("@BackgroundCheckResult", "n");
                    insertHost.Parameters.AddWithValue("@LastUpdatedBy", lastName);
                    insertHost.Parameters.AddWithValue("@LastUpdated", DateTime.Now);
                    insertHost.Parameters.AddWithValue("@HostBio", Session["hostBio"].ToString());
                    insertHost.Parameters.AddWithValue("@ShowHost", "y");


                    sc.Open();
                    insertHost.ExecuteNonQuery();

                    SqlCommand insert = new SqlCommand("SELECT HostID FROM [dbo].[Host] WHERE lower(Email) = @Email", sc);
                    insert.Parameters.AddWithValue("@Email", email.ToLower());
                    insert.Connection = sc;
                    int hostID = Convert.ToInt32(insert.ExecuteScalar());
                    insert.ExecuteNonQuery();
                    Session["hostID"] = hostID;

                    //uploads the image to the database
                    if (FileUploadControlHost.HasFile)
                    {
                        HttpPostedFile postedFile    = FileUploadControlHost.PostedFile;
                        string         fileName      = Path.GetFileName(postedFile.FileName);
                        string         fileExtension = Path.GetExtension(fileName);
                        int            fileSize      = postedFile.ContentLength;

                        if (fileExtension.ToLower() == ".jpg" || fileExtension.ToLower() == ".bmp" ||
                            fileExtension.ToLower() == ".gif" || fileExtension.ToLower() == ".png")
                        {
                            Stream       stream = postedFile.InputStream;
                            BinaryReader br     = new BinaryReader(stream);
                            byte[]       bytes  = br.ReadBytes((int)stream.Length);

                            SqlCommand cmd = new SqlCommand("UPDATE [dbo].[Host] SET imageV2 = @imgdata WHERE HostID = @HostID", sc);
                            cmd.Parameters.AddWithValue("@HostID", hostID);
                            cmd.Parameters.AddWithValue("@imgdata", bytes);
                            cmd.ExecuteNonQuery();

                            StatusLabel.Text = "Image Uploaded successfully";
                        }
                        else
                        {
                            StatusLabel.Text = "Only Images (.jpg, .png, .gif and .bmp) can be uploaded!";
                        }
                    }
                    else
                    {
                        StatusLabel.Text = "Please select an image to upload";
                    }

                    //inserts login info to the database
                    Login tempLogin = new Login(userNameTextbox.Text, passwordTextbox.Text);
                    insertLogin.CommandText = "INSERT INTO [dbo].[Login] (Username, Password, hostID) VALUES (@userName, @Password, @hostID)";
                    insertLogin.Parameters.AddWithValue("@userName", newHost.userName);
                    insertLogin.Parameters.AddWithValue("@Password", PasswordHash.HashPassword(newHost.password));
                    insertLogin.Parameters.AddWithValue("@hostID", hostID);

                    SqlCommand getAccountID = new SqlCommand("SELECT AccountID FROM [dbo].[Login] WHERE HostID = @HostID", sc);
                    getAccountID.Parameters.AddWithValue("@HostID", hostID);
                    getAccountID.Connection = sc;
                    int accountID = Convert.ToInt32(getAccountID.ExecuteScalar());
                    getAccountID.ExecuteNonQuery();
                    Session["accountID"] = accountID;
                    Session["username"]  = newHost.userName;

                    insertLogin.ExecuteNonQuery();
                    sc.Close();
                    Response.Redirect("PropertyInfoHomeowner.aspx");
                }
                else
                {
                    resultmessage.Text = "Passwords does not match.";
                }
            }
            else
            {
                resultmessage.Text = "Password does not meet minimum password requirements.";
            }
        }
        else
        {
            resultmessage.Text = "Username already exists.";
        }
    }
        private void saveboton_Click(object sender, EventArgs e)
        {
            String fn  = dia.Text + " " + mes.Text + " " + anno.Text;
            String sex = sexMax.Text;

            if (ciedcli.Text != "" && ciedcli.Font.Italic == true)
            {
                if (ciedcli.Text == "")
                {
                    MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else if (ciedcli.Text == "")
            {
                MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (nomed.Text == "" || nomed.Font.Italic == true)
            {
                MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (nited.Text == "" || nited.Font.Italic == true)
            {
                MessageBox.Show("Ingrese el Carnet de Identidad del Usuario a Modificar", "ERROR",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {  //Codigo de editar Cliente.
                if (comprobar() == 1)
                {
                    if (sexFem.Checked == true)
                    {
                        sex = sexFem.Text;
                        try
                        {       // Objetos de conexión y comando
                            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                            // Asignando el valor de la imagen
                            // Stream usado como buffer
                            System.IO.MemoryStream ms = new System.IO.MemoryStream();
                            // Se guarda la imagen en el buffer
                            fotoCliente.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                            // Estableciento propiedades
                            cmd.Connection  = cs.GetCONN();
                            cmd.CommandText = "UPDATE Clientes SET idCliente=@id, nombreCliente=@nom, apellidoCliente=@ape, nitCliente=@nit, correoCliente=@email, webCliente=@web, DirCliente=@dir, sexoCliente=@sexo, telCliente=@tel, celCliente=@cel, faxCliente=@fax, fnCliente=@fechan, fcCliente=@fechac, fotoCliente=@imagen, estado=@state, CodDepaC=@codd  WHERE idCliente = @ciid ;";

                            cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@ape", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@nit", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@email", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@web", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@dir", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@sexo", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@tel", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@cel", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@fax", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@fechan", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@fechac", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@imagen", System.Data.SqlDbType.Image);
                            cmd.Parameters.Add("@state", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@codd", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@ciid", System.Data.SqlDbType.Int);

                            // Asignando los valores a los atributos
                            cmd.Parameters["@id"].Value     = int.Parse(ci.Text);
                            cmd.Parameters["@nom"].Value    = nomed.Text;
                            cmd.Parameters["@ape"].Value    = apeled.Text;
                            cmd.Parameters["@nit"].Value    = nited.Text;
                            cmd.Parameters["@email"].Value  = corred.Text;
                            cmd.Parameters["@web"].Value    = webed.Text;
                            cmd.Parameters["@dir"].Value    = dired.Text;
                            cmd.Parameters["@sexo"].Value   = sex;
                            cmd.Parameters["@tel"].Value    = int.Parse(teled.Text);
                            cmd.Parameters["@cel"].Value    = int.Parse(celed.Text);
                            cmd.Parameters["@fax"].Value    = int.Parse(faxed.Text);
                            cmd.Parameters["@fechan"].Value = fn;
                            cmd.Parameters["@fechac"].Value = fechaed.Text;
                            cmd.Parameters["@imagen"].Value = ms.GetBuffer();
                            cmd.Parameters["@state"].Value  = staed.Text;
                            cmd.Parameters["@codd"].Value   = id;
                            cmd.Parameters["@ciid"].Value   = int.Parse(ciedcli.Text);

                            cs.OpenCnn();
                            cmd.ExecuteNonQuery();
                            cs.CerrarCnn();

                            Messengers mr = new Messengers();
                            mr.textolb.Text = "Cliente Modificado";
                            mr.ShowDialog();
                            this.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("ERROR. Al Modificar Los Datos. " + ex.Message, " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    else
                    {
                        try
                        {       // Objetos de conexión y comando
                            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                            // Asignando el valor de la imagen
                            // Stream usado como buffer
                            System.IO.MemoryStream ms = new System.IO.MemoryStream();
                            // Se guarda la imagen en el buffer
                            fotoCliente.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                            // Estableciento propiedades
                            cmd.Connection  = cs.GetCONN();
                            cmd.CommandText = "UPDATE Clientes SET idCliente=@id, nombreCliente=@nom, apellidoCliente=@ape, nitCliente=@nit, correoCliente=@email, webCliente=@web, DirCliente=@dir, sexoCliente=@sexo, telCliente=@tel, celCliente=@cel, faxCliente=@fax, fnCliente=@fechan, fcCliente=@fechac, fotoCliente=@imagen, estado=@state, CodDepaC=@codd  WHERE idCliente = @ciid ;";

                            cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@nom", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@ape", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@nit", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@email", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@web", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@dir", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@sexo", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@tel", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@cel", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@fax", System.Data.SqlDbType.Int);
                            cmd.Parameters.Add("@fechan", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@fechac", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@imagen", System.Data.SqlDbType.Image);
                            cmd.Parameters.Add("@state", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@codd", System.Data.SqlDbType.VarChar);
                            cmd.Parameters.Add("@ciid", System.Data.SqlDbType.Int);

                            // Asignando los valores a los atributos
                            cmd.Parameters["@id"].Value     = int.Parse(ci.Text);
                            cmd.Parameters["@nom"].Value    = nomed.Text;
                            cmd.Parameters["@ape"].Value    = apeled.Text;
                            cmd.Parameters["@nit"].Value    = nited.Text;
                            cmd.Parameters["@email"].Value  = corred.Text;
                            cmd.Parameters["@web"].Value    = webed.Text;
                            cmd.Parameters["@dir"].Value    = dired.Text;
                            cmd.Parameters["@sexo"].Value   = sex;
                            cmd.Parameters["@tel"].Value    = int.Parse(teled.Text);
                            cmd.Parameters["@cel"].Value    = int.Parse(celed.Text);
                            cmd.Parameters["@fax"].Value    = int.Parse(faxed.Text);
                            cmd.Parameters["@fechan"].Value = fn;
                            cmd.Parameters["@fechac"].Value = fechaed.Text;
                            cmd.Parameters["@imagen"].Value = ms.GetBuffer();
                            cmd.Parameters["@state"].Value  = staed.Text;
                            cmd.Parameters["@codd"].Value   = id;
                            cmd.Parameters["@ciid"].Value   = int.Parse(ciedcli.Text);

                            cs.OpenCnn();
                            cmd.ExecuteNonQuery();
                            cs.CerrarCnn();

                            Messengers mr = new Messengers();
                            mr.textolb.Text = "Cliente Modificado";
                            mr.ShowDialog();
                            this.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("ERROR. Al Modificar Los Datos. " + ex.Message, " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Error, No Existen Registros del Usuario.", "ERROR",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
Example #54
0
        public Esito AggiornaEmailCollaboratore(Anag_Email_Collaboratori emailCollaboratore, Anag_Utenti utente)
        {
            Esito esito = new Esito();

            try
            {
                using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlConstr))
                {
                    using (System.Data.SqlClient.SqlCommand StoreProc = new System.Data.SqlClient.SqlCommand("UpdateEmailCollaboratore"))
                    {
                        using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
                        {
                            StoreProc.Connection  = con;
                            sda.SelectCommand     = StoreProc;
                            StoreProc.CommandType = CommandType.StoredProcedure;

                            // PARAMETRI PER LOG UTENTE
                            SqlParameter idUtente = new SqlParameter("@idUtente", utente.id);
                            idUtente.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(idUtente);

                            SqlParameter nomeUtente = new SqlParameter("@nomeUtente", utente.username);
                            nomeUtente.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(nomeUtente);
                            // FINE PARAMETRI PER LOG UTENTE

                            System.Data.SqlClient.SqlParameter id = new System.Data.SqlClient.SqlParameter("@id", emailCollaboratore.Id);
                            id.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(id);

                            SqlParameter id_collaboratore = new SqlParameter("@id_collaboratore", emailCollaboratore.Id_collaboratore);
                            id_collaboratore.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(id_collaboratore);

                            SqlParameter priorita = new SqlParameter("@priorita", emailCollaboratore.Priorita);
                            priorita.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(priorita);

                            SqlParameter indirizzoEmail = new SqlParameter("@indirizzoEmail", emailCollaboratore.IndirizzoEmail);
                            indirizzoEmail.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(indirizzoEmail);

                            SqlParameter descrizione = new SqlParameter("@descrizione", emailCollaboratore.Descrizione);
                            descrizione.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(descrizione);

                            SqlParameter attivo = new SqlParameter("@attivo", emailCollaboratore.Attivo);
                            attivo.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(attivo);

                            StoreProc.Connection.Open();

                            int iReturn = StoreProc.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                esito.Codice      = Esito.ESITO_KO_ERRORE_SCRITTURA_TABELLA;
                esito.Descrizione = "Anag_Email_Collaboratori_DAL.cs - aggiornaEmailCollaboratore " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace;
            }

            return(esito);
        }
    //create scholarship
    protected void SubmitButton_Click(object sender, EventArgs e)
    {
        if ((txtTitle.Text == "") || (txtAmount.Value == ""))
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("<script language=JavaScript> ShowCreate(); </script>\n");
            Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowCreate", builder.ToString());
            lblError.Text = "Please enter all required values.";
        }
        else
        {
            localDB.Open();

            //create post object

            Post post = new Post(1, "Scholarship", HttpUtility.HtmlEncode(txtTitle.Text), HttpUtility.HtmlEncode(txtDescription.Text));

            System.Data.SqlClient.SqlCommand insertPost = new System.Data.SqlClient.SqlCommand();
            insertPost.Connection  = localDB;
            insertPost.CommandText = "Execute InsertPost @busId, @type, @title, @postDate, @description, @lastUpdatedBy, @lastUpdated";

            insertPost.Parameters.Add("@type", SqlDbType.VarChar, 30).Value         = post.getType();
            insertPost.Parameters.Add("@title", SqlDbType.VarChar, 100).Value       = post.getTitle();
            insertPost.Parameters.Add("@postDate", SqlDbType.VarChar, 30).Value     = post.getPostDate();
            insertPost.Parameters.Add("@description", SqlDbType.VarChar, 500).Value = post.getDescription();
            insertPost.Parameters.Add("@busID", SqlDbType.Int).Value = post.getBusID();
            insertPost.Parameters.Add("@lastUpdatedBy", SqlDbType.VarChar, 30).Value = post.getLastUpdatedBy();
            insertPost.Parameters.Add("@lastUpdated", SqlDbType.VarChar, 30).Value   = post.getLastUpdated();

            insertPost.ExecuteNonQuery();

            System.Data.SqlClient.SqlCommand selectPostID = new System.Data.SqlClient.SqlCommand();
            selectPostID.Connection = localDB;

            selectPostID.CommandText = "select max(postID) from Post";
            string postID = selectPostID.ExecuteScalar().ToString();
            selectPostID.ExecuteNonQuery();

            try
            {
                DateTime start = DateTime.Parse(txtDeadline.Value);
                txtDeadline.Value = start.ToString("MM/dd/yyyy");
            }
            catch
            {
            }

            //create scholarship object
            Scholarship sch = new Scholarship(postID, HttpUtility.HtmlEncode(txtRequirements.Text), HttpUtility.HtmlEncode(txtAmount.Value), HttpUtility.HtmlEncode(txtDeadline.Value));

            System.Data.SqlClient.SqlCommand insertScholarship = new System.Data.SqlClient.SqlCommand();
            insertScholarship.Connection = localDB;

            insertScholarship.CommandText = "Execute InsertScholarship @postID, @requirements, @reward, @dueDate, @lastUpdatedBy, @lastUpdated";

            insertScholarship.Parameters.Add("@postID", SqlDbType.Int).Value = sch.getpostID();
            insertScholarship.Parameters.Add("@requirements", SqlDbType.VarChar, 100).Value = sch.getReqs();
            insertScholarship.Parameters.Add("@reward", SqlDbType.VarChar, 30).Value        = sch.getReward();
            insertScholarship.Parameters.Add("@dueDate", SqlDbType.VarChar, 30).Value       = sch.getDueDate();
            insertScholarship.Parameters.Add("@lastUpdatedBy", SqlDbType.VarChar, 30).Value = sch.getLastUpdatedBy();
            insertScholarship.Parameters.Add("@lastUpdated", SqlDbType.VarChar, 30).Value   = sch.getLastUpdated();

            insertScholarship.ExecuteNonQuery();

            localDB.Close();


            showData();
        }
    }
        public ActionResult Check(string[] a, string[] b, string c)
        {
            Session["StuID"]    = Session["StuID"];
            Session["ClassID"]  = Session["ClassID"];
            Session["LecID"]    = Session["LecID"];
            Session["PuzzleID"] = Session["PuzzleID"];
            String StudentID = Session["StuID"].ToString();

            string PuzzleID         = Session["PuzzleID"].ToString();
            string connectionString =
                @"Data Source=msi;Initial Catalog=SEFASSIGNMENT;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

            System.Data.SqlClient.SqlConnection sqlConnection =
                new System.Data.SqlClient.SqlConnection(connectionString);
            sqlConnection.Open();

            System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(
                "SELECT Hint_Score FROM [SEFASSIGNMENT].[dbo].[Puzzle] WHERE Puzzle_ID='" + PuzzleID + "'");
            sqlCommand.Connection = sqlConnection;
            int hintscore = Convert.ToInt32(sqlCommand.ExecuteScalar());

            int   sumhint     = Int32.Parse(c);
            float sumusedhint = sumhint;

            hintscore = hintscore * -1;

            sumhint = sumhint * hintscore;

            sqlCommand = new System.Data.SqlClient.SqlCommand(
                "SELECT Puzzle_Score FROM [SEFASSIGNMENT].[dbo].[Puzzle] WHERE Puzzle_ID='" + PuzzleID + "'");
            sqlCommand.Connection = sqlConnection;


            int fullmark = Convert.ToInt32(sqlCommand.ExecuteScalar());

            sqlCommand = new System.Data.SqlClient.SqlCommand(
                "SELECT COUNT(*) FROM [SEFASSIGNMENT].[dbo].[Answer] WHERE Puzzle_ID='" + PuzzleID + "'");
            sqlCommand.Connection = sqlConnection;

            int sumquestion = Convert.ToInt32(sqlCommand.ExecuteScalar());



            float permark = fullmark / sumquestion;

            sumusedhint = sumusedhint * permark * -1;
            string test = sumquestion.ToString();


            float summark = 0;

            string at = a[0].ToString().ToLower();

            at = at.Replace(",,", "\n");
            at = at.Replace(",", "");

            string[] attempt = at.Split(null);

            string an = b[0].ToString().ToLower();

            an = an.Replace(",,", "\n");

            an = an.Replace(",", "");

            string[] ans = an.Split(null);


            foreach (string s in attempt)
            {
                foreach (string n in ans)
                {
                    if (s.Equals(n))
                    {
                        summark = summark + permark;
                    }
                }
            }


            summark = summark + sumhint + sumusedhint;

            sqlCommand = new System.Data.SqlClient.SqlCommand(
                "SELECT SUM(Attempt_Score) FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"STU_ID\" ='" +
                StudentID + "'");
            sqlCommand.Connection = sqlConnection;

            object res        = sqlCommand.ExecuteScalar();
            String MarkBefore = Convert.ToString(res);

            if (MarkBefore.Equals(""))
            {
                MarkBefore = "0";
            }

            String MarkToAdd = summark.ToString();

            TempData["MarkBefore"] = MarkBefore;
            TempData["MarkToAdd"]  = MarkToAdd;


            sqlCommand            = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"STU_ID\" ='" + StudentID + "' AND \"PUZZLE_ID\" = '" + PuzzleID + "'");
            sqlCommand.Connection = sqlConnection;

            Int32  existscheck = (Int32)sqlCommand.ExecuteScalar();
            string exist       = existscheck.ToString();


            if (existscheck <= 0)
            {
                DateTime myDateTime       = DateTime.Now;
                string   sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
                string   datee            = sqlFormattedDate;

                sqlCommand            = new System.Data.SqlClient.SqlCommand("INSERT INTO [SEFASSIGNMENT].[dbo].[Attempt] (\"STU_ID\",\"PUZZLE_ID\",\"PUZZLE_STATUS\",\"ATTEMPT_SCORE\",\"DATETIME_STAMP\") VALUES ('" + StudentID + "','" + PuzzleID + "'," + "1," + summark + ",'" + datee + "')");
                sqlCommand.Connection = sqlConnection;
                int mark = sqlCommand.ExecuteNonQuery();

                sqlCommand = new System.Data.SqlClient.SqlCommand(
                    "SELECT SUM(Attempt_Score) FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"STU_ID\" ='" +
                    StudentID + "'");
                sqlCommand.Connection = sqlConnection;

                object finalmark = sqlCommand.ExecuteScalar();
                int    FinalMark = Convert.ToInt32(finalmark);
                sqlCommand            = new System.Data.SqlClient.SqlCommand("UPDATE [SEFASSIGNMENT].[dbo].[Student] SET \"Stu_TotalScore\"=" + FinalMark + " WHERE \"STU_ID\" ='" + StudentID + "'");
                sqlCommand.Connection = sqlConnection;
                object useless = sqlCommand.ExecuteScalar();

                return(RedirectToAction("MarkMuch"));
            }

            else
            {
                sqlCommand = new System.Data.SqlClient.SqlCommand(
                    "SELECT ATTEMPT_SCORE FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"STU_ID\" ='" +
                    StudentID + "' AND \"PUZZLE_ID\" = '" + PuzzleID + "'");
                sqlCommand.Connection = sqlConnection;
                int markbefore = Convert.ToInt32(sqlCommand.ExecuteScalar());
                if (markbefore < summark)
                {
                    DateTime myDateTime       = DateTime.Now;
                    string   sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
                    string   datee            = sqlFormattedDate;
                    sqlCommand = new System.Data.SqlClient.SqlCommand(
                        "UPDATE [SEFASSIGNMENT].[dbo].[Attempt] SET \"ATTEMPT_SCORE\"=" + summark +
                        " ,\"DATETIME_STAMP\"='" + datee + "' WHERE(\"STU_ID\" ='" + StudentID +
                        "' AND \"PUZZLE_ID\"='" + PuzzleID + "')");

                    sqlCommand.Connection = sqlConnection;
                    int mark = sqlCommand.ExecuteNonQuery();


                    sqlCommand = new System.Data.SqlClient.SqlCommand(
                        "SELECT SUM(Attempt_Score) FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"STU_ID\" ='" +
                        StudentID + "'");
                    sqlCommand.Connection = sqlConnection;

                    object finalmark = sqlCommand.ExecuteScalar();
                    int    FinalMark = Convert.ToInt32(finalmark);
                    sqlCommand            = new System.Data.SqlClient.SqlCommand("UPDATE [SEFASSIGNMENT].[dbo].[Student] SET \"Stu_TotalScore\"=" + FinalMark + " WHERE \"STU_ID\" ='" + StudentID + "'");
                    sqlCommand.Connection = sqlConnection;
                    object useless = sqlCommand.ExecuteScalar();

                    return(RedirectToAction("MarkMuch"));
                }
                else
                {
                    return(RedirectToAction("MarkLess"));
                }
            }
        }
Example #57
0
    protected void btnCreateAccount_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = "server=aa1evano00xv2xb.cqpnea2xsqc1.us-east-1.rds.amazonaws.com;database=roommagnetdb;uid=admin;password=Skylinejmu2019;";
        sc.Open();
        System.Data.SqlClient.SqlCommand checkEmailCount = new System.Data.SqlClient.SqlCommand();
        System.Data.SqlClient.SqlCommand checkAcctType   = new System.Data.SqlClient.SqlCommand();
        System.Data.SqlClient.SqlCommand insert          = new System.Data.SqlClient.SqlCommand();
        checkEmailCount.Connection = sc;
        checkAcctType.Connection   = sc;
        insert.Connection          = sc;

        int emailCount;

        //create new account and host object
        //use HttpUtility.HtmlEncode for these inputs
        Account newAccount = new Account(HttpUtility.HtmlEncode(txtFN.Text), HttpUtility.HtmlEncode(txtMN.Text), HttpUtility.HtmlEncode(txtLN.Text), HttpUtility.HtmlEncode(txtPhone.Text), DateTime.Parse(txtBday.Text), HttpUtility.HtmlEncode(txtEmail.Text), HttpUtility.HtmlEncode(txtHouseNum.Text), HttpUtility.HtmlEncode(txtStreet.Text), HttpUtility.HtmlEncode(txtCity.Text), ddState.SelectedValue, HttpUtility.HtmlEncode(txtZip.Text), "US", Int32.Parse("2"), Int32.Parse("2"));
        Host    newHost    = new Host(newAccount, "N", "Retiree");

        checkEmailCount.CommandText = "SELECT COUNT(*) FROM ACCOUNT WHERE EMAIL = @emailCheck";
        checkEmailCount.Parameters.Add(new SqlParameter("@emailCheck", newAccount.getEmail()));

        emailCount = (int)checkEmailCount.ExecuteScalar();

        if (emailCount < 1 && cbAgreement.Checked == true)
        {
            insert.CommandText = "INSERT into Account VALUES (@fName, @mName, @lName, @phone, @bday, @email, @HouseNbr, @street, @city, @state, @zip, @country, @AccType, @ModDate, @PID); " +
                                 "INSERT into Host VALUES(@@Identity, @BackCheck, @HostReason);" +
                                 "INSERT into Password VALUES((SELECT MAX(HostID) from Host), @email, @password);";

            //Insert into ACCOUNT
            insert.Parameters.Add(new SqlParameter("@fName", newHost.getFirstName()));
            insert.Parameters.Add(new SqlParameter("@mName", newHost.getMiddleName()));
            insert.Parameters.Add(new SqlParameter("@lName", newHost.getLastName()));
            insert.Parameters.Add(new SqlParameter("@phone", newHost.getPhone()));
            insert.Parameters.Add(new SqlParameter("@bday", newHost.getBday()));
            insert.Parameters.Add(new SqlParameter("@email", newHost.getEmail()));
            insert.Parameters.Add(new SqlParameter("@HouseNbr", newHost.getHouseNumber()));
            insert.Parameters.Add(new SqlParameter("@street", newHost.getStreet()));
            insert.Parameters.Add(new SqlParameter("@city", newHost.getCity()));
            insert.Parameters.Add(new SqlParameter("@state", newHost.getState()));
            insert.Parameters.Add(new SqlParameter("@zip", newHost.getZip()));
            insert.Parameters.Add(new SqlParameter("@country", newHost.getCountry()));
            insert.Parameters.Add(new SqlParameter("@AccType", newHost.getAccType()));
            insert.Parameters.Add(new SqlParameter("@ModDate", newHost.getModDate()));
            insert.Parameters.Add(new SqlParameter("@PID", newHost.getPID()));

            //Insert into HOST
            insert.Parameters.Add(new SqlParameter("@BackCheck", newHost.getBackCheck()));
            insert.Parameters.Add(new SqlParameter("@HostReason", newHost.getHostReason()));

            //Insert into PASSWORD
            insert.Parameters.Add(new SqlParameter("@password", PasswordHash.HashPassword(txtPassword.Text))); // hash entered password

            insert.ExecuteNonQuery();

            //Label1.Text = "Success";

            Session["type"] = 2;
            Response.Redirect("HostDashboard.aspx");

            sc.Close();

            //Clear text boxes
            txtFN.Text       = "";
            txtMN.Text       = "";
            txtLN.Text       = "";
            txtBday.Text     = "";
            txtEmail.Text    = "";
            txtPhone.Text    = "";
            txtPassword.Text = "";
            txtHouseNum.Text = "";
            txtStreet.Text   = "";
            txtCity.Text     = "";
            ddState.ClearSelection();
            txtZip.Text     = "";
            txtCountry.Text = "US";
        }
        else
        {
            sc.Close();
        }
    }
Example #58
0
        public ApiResponse TableDefs()
        {
            DataSet dsSchemaExport           = new DataSet();
            Dictionary <string, object> defs = new Dictionary <string, object>();
            List <string> cols = new List <string>();

            System.Data.SqlClient.SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AgmDataContext"].ConnectionString);
            try
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand command =
                    new System.Data.SqlClient.SqlCommand("select * from utenti where idutente=21", conn);
                System.Data.SqlClient.SqlDataReader sqlreader = command.ExecuteReader();
                var schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "utenti"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("utenti", cols);
                sqlreader.Read();
                object[] resUtenti = new object[cols.Count];
                sqlreader.GetValues(resUtenti);
                defs.Add("utenti example", JsonConvert.SerializeObject(resUtenti));
                sqlreader.Close();

                command = new System.Data.SqlClient.SqlCommand("UPDATE utenti set utente=email where utente is NULL;", conn);
                var resupd = command.ExecuteNonQuery();
                defs.Add("update result1", resupd);

                //command = new System.Data.SqlClient.SqlCommand("insert into rappcausali (idcausale,nome) values (10,'Permessi ex-festività')", conn);
                //var resAlter = command.ExecuteNonQuery();
                //defs.Add("update result1", resAlter);

                //command = new System.Data.SqlClient.SqlCommand("ALTER TABLE utenti ADD isDeleted bit DEFAULT 0 NOT NULL", conn);
                //var resAlter = command.ExecuteNonQuery();
                //defs.Add("update result1", resAlter);

                //command = new System.Data.SqlClient.SqlCommand("update utenti set utenti=1 where idutente=21", conn);
                //var resAlter = command.ExecuteNonQuery();
                //defs.Add("update result1", resAlter);

                //command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=21", conn);
                //var resAlter = command.ExecuteNonQuery();
                //defs.Add("update result1", resAlter);

                //command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=4", conn);
                //resAlter = command.ExecuteNonQuery();
                //defs.Add("update result2", resAlter);

                //command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=3", conn);
                //resAlter = command.ExecuteNonQuery();
                //defs.Add("update result3", resAlter);

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappore where idutente=38", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappore"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappore", cols);
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select * from rappcausali", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappcausali"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappcausali", cols);
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select * from rappcausalispese", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappcausalispese"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappcausalispese", cols);
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappspese where idutente=38", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappspese"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappspese", cols);
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappdescrizioni where idutente=38", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappdescrizioni"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappdescrizioni", cols);
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappfestivi", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappfestivi"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
                }
                defs.Add("rappfestivi", cols);
                sqlreader.Read();
                defs.Add("rappfestivi example", sqlreader[1].ToString());
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from annunci", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "annunci"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
                }
                defs.Add("annunci", cols);
                sqlreader.Read();
                defs.Add("annunci example", sqlreader[4].ToString());
                sqlreader.Close();

                cols        = new List <string>();
                command     = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappvociretributive", conn);
                sqlreader   = command.ExecuteReader();
                schemaTable = sqlreader.GetSchemaTable();
                dsSchemaExport.Tables.Add(Add(conn, "rappvociretributive"));
                foreach (System.Data.DataRow col in schemaTable.Rows)
                {
                    cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
                }
                defs.Add("rappvociretributive", cols);
                sqlreader.Close();


                dsSchemaExport.WriteXml(HttpContext.Current.Server.MapPath("~/App_Data/tables.xml"), XmlWriteMode.WriteSchema);
                dsSchemaExport.WriteXmlSchema(HttpContext.Current.Server.MapPath("~/App_Data/tables_schema.xsd"));

                conn.Close();
            }
            catch (Exception e)
            {
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                }

                return(new ApiResponse(false)
                {
                    Errors = new ApiResponseError[] { new ApiResponseError()
                                                      {
                                                          Message = e.Message
                                                      } }
                });
            }

            return(new ApiResponse(true)
            {
                Data = defs
            });
        }
Example #59
0
    private void commitEmployeeToDB(Employee person)
    {
        try
        {
            System.Data.SqlClient.SqlConnection sqlc = connectToDB();

            //Creates the employee insert statement
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sqlc;

            //After the objects attributes are set to their values this will run and insert nulls where applicable

            insert.CommandText += "insert into [dbo].[EMPLOYEE] values ('" + person.FirstName + "','" + person.LastName;
            if (person.MiddleName == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + person.MiddleName + "','";
            }

            insert.CommandText += person.HouseNum + "','" + person.Street + "','" + person.County;
            if (person.State == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + person.State + "','";
            }

            insert.CommandText += person.Country + "','" + person.Zip + "','" + person.DateOfBirth + "','" + person.HireDate;
            if (person.TerminationDate == DateTime.MinValue)
            {
                insert.CommandText += "',NULL,";
            }
            else
            {
                insert.CommandText += "','" + person.TerminationDate + "',";
            }

            insert.CommandText += person.Salary;

            if (person.ManagerID == -1)
            {
                insert.CommandText += ",NULL,'";
            }
            else
            {
                insert.CommandText += "," + person.ManagerID + ",'";
            }
            insert.CommandText += person.LastUpdatedBy + "','" + person.LastUpdated + "')";

            insert.ExecuteNonQuery();

            sqlc.Close();
        }
        catch (Exception c)
        {
            errorMessage.Text += c;
        }
    }
Example #60
0
        public Esito AggiornaNoteOfferta(NoteOfferta noteOfferta)
        {
            Anag_Utenti utente = ((Anag_Utenti)HttpContext.Current.Session[SessionManager.UTENTE]);
            Esito       esito  = new Esito();

            try
            {
                using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlConstr))
                {
                    using (System.Data.SqlClient.SqlCommand StoreProc = new System.Data.SqlClient.SqlCommand("UpdateNoteOfferta"))
                    {
                        using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
                        {
                            StoreProc.Connection  = con;
                            sda.SelectCommand     = StoreProc;
                            StoreProc.CommandType = CommandType.StoredProcedure;

                            System.Data.SqlClient.SqlParameter id = new System.Data.SqlClient.SqlParameter("@id", noteOfferta.Id);
                            id.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(id);

                            // PARAMETRI PER LOG UTENTE
                            SqlParameter idUtente = new SqlParameter("@idUtente", utente.id);
                            idUtente.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(idUtente);

                            SqlParameter nomeUtente = new SqlParameter("@nomeUtente", utente.username);
                            nomeUtente.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(nomeUtente);
                            // FINE PARAMETRI PER LOG UTENTE

                            SqlParameter id_dati_agenda = new SqlParameter("@id_dati_agenda", noteOfferta.Id_dati_agenda);
                            id_dati_agenda.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(id_dati_agenda);

                            SqlParameter banca = new SqlParameter("@banca", noteOfferta.Banca);
                            banca.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(banca);

                            SqlParameter pagamento = new SqlParameter("@pagamento", noteOfferta.Pagamento);
                            pagamento.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(pagamento);

                            SqlParameter notaPagamento = new SqlParameter("@notaPagamento", noteOfferta.NotaPagamento);
                            notaPagamento.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(notaPagamento);


                            SqlParameter consegna = new SqlParameter("@consegna", noteOfferta.Consegna);
                            consegna.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(consegna);

                            SqlParameter note = new SqlParameter("@note", noteOfferta.Note);
                            note.Direction = ParameterDirection.Input;
                            StoreProc.Parameters.Add(note);

                            StoreProc.Connection.Open();

                            int iReturn = StoreProc.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                esito.Codice      = Esito.ESITO_KO_ERRORE_SCRITTURA_TABELLA;
                esito.Descrizione = "Offerta_DAL.cs - AggiornaNoteOfferta " + Environment.NewLine + ex.Message;

                log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
            }

            return(esito);
        }