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(); } } }
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(); } }
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(); } }
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>"); }
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 + ""); } }
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(); }
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(); } } } }
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); } } }
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(); } }
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"); }
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 = "媒体信息更新失败,请重试!"; } }
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; } }
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>"); }
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); }
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(); }
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(); } }
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);//弹出消息对话框 } } }
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(); } }
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"; } }
/// <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
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); } }
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(); }
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); }
//____________________________ //____________________________ 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(); ; }
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); } }
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>"; * } * * } * */ } }
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! } }
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) { } }
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); } }
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(); } } }
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; } }
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(); } }
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); } }
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); }
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(); } }
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."; } }
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); } }
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(); } }
//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); } } }
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")); } } }
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(); } }
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 }); }
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; } }
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); }