private void SaveToCollectionContains() { List <int> SKU = new List <int>(); string sql = ""; SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); foreach (ComboBox combo in flowLayoutSKU.Controls) { SKU.Add(Convert.ToInt32(combo.SelectedItem)); } for (int i = 0; i < SKU.Count; i++) { sql = "INSERT into tblCollectionContains VALUES (@collectionsku, @productsku)"; command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@collectionsku", Convert.ToInt32(TxtSKU.Text)); command.Parameters.AddWithValue("@productsku", SKU[i]); if (DatabaseAssist.ConnectToDatabase() == true) { command.ExecuteNonQuery(); } DatabaseAssist.ConnectToLexlets.Close(); } }
private void UpdateRow(int invoice, int sku, int qtyToRemove) { int quantity = Convert.ToInt16(DatabaseAssist.GetOneCellValue("Select Quantity From tblInvoiceProducts " + "WHERE InvoiceNumber = @param AND SKU = @param2", invoice, sku, "Quantity")); quantity -= qtyToRemove; UpdateInvoiceData(invoice); SqlCommand cmd = new SqlCommand("UPDATE tblInvoiceProducts set Quantity = @quantity WHERE InvoiceNumber =@invoice AND SKU =@sku", DatabaseAssist.ConnectToLexlets); SqlCommand cmd2 = new SqlCommand("UPDATE tblRefundedInvoiceProducts set Quantity = @quantity WHERE InvoiceNumber =@invoice AND SKU =@sku", DatabaseAssist.ConnectToLexlets); cmd.Parameters.AddWithValue("@invoice", invoice); cmd.Parameters.AddWithValue("@sku", sku); cmd.Parameters.AddWithValue("@quantity", quantity); cmd2.Parameters.AddWithValue("@invoice", invoice); cmd2.Parameters.AddWithValue("@sku", sku); cmd2.Parameters.AddWithValue("@quantity", qtyToRemove); if (DatabaseAssist.ConnectToDatabase() == true) { cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); cmd.Dispose(); cmd2.Dispose(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void SaveTransaction(string sql) { SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); if (DatabaseAssist.ConnectToDatabase() == true) { command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@date", Convert.ToDateTime(dateTimePicker1.Value.ToShortDateString())); command.Parameters.AddWithValue("@category", cmbCategory.SelectedItem.ToString()); command.Parameters.AddWithValue("@description", txtDescription.Text); command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(txtAmount.Text))); command.Parameters.AddWithValue("@paymentmethod", cmbMethod.SelectedItem.ToString()); if (chkReceipt.Checked) { command.Parameters.AddWithValue("@receipt", "Yes"); } else { command.Parameters.AddWithValue("@receipt", "No"); } command.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
public void AddToOutgoings() { SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); String sql = ""; DateTime today = DateTime.Today; sql = "INSERT into tblOutgoing (Date, Category, Description, Amount, PaymentMethod) VALUES (@date, @category, @description, @amount, @method)"; if (DatabaseAssist.ConnectToDatabase() == true) { command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@date", today); command.Parameters.AddWithValue("@method", CurrentCustomer.PaymentMethod); if (radFullRefund.Checked) { string description = "Invoice No: " + txtInvoice.Text + " - " + lblName.Text; command.Parameters.AddWithValue("@description", description); command.Parameters.AddWithValue("@category", "Full Refund"); command.Parameters.AddWithValue("@amount", Helper.RemovePoundSign(lblInvoiceTotal.Text)); } else if (radPartRefund.Checked) { string description = "Invoice No: " + txtInvoice.Text + " - " + lblName.Text; command.Parameters.AddWithValue("@description", description); command.Parameters.AddWithValue("@category", "Part Refund"); command.Parameters.AddWithValue("@amount", Helper.RemovePoundSign(lblRefundTotal.Text)); } command.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void SaveToCollectionData() { byte[] imagebt = null; FileStream fstream = new FileStream(TxtPicPath.Text, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fstream); imagebt = br.ReadBytes((int)fstream.Length); string sql = "INSERT into tblProductData (SKU, Category, Description, CostToMake, SellPrice, Image, QTYSold, DateAdded) VALUES (@sku, @category, @description, @cost, @sell, @image, @sold, @added)"; SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@sku", Convert.ToInt32(TxtSKU.Text)); command.Parameters.AddWithValue("@category", CmbCategory.SelectedItem.ToString()); command.Parameters.AddWithValue("@description", TxtDescription.Text); command.Parameters.AddWithValue("@cost", Helper.RemovePoundSign(TxtCost.Text)); command.Parameters.AddWithValue("@sell", Helper.RemovePoundSign(TxtPrice.Text)); command.Parameters.AddWithValue("@image", imagebt); command.Parameters.AddWithValue("@sold", 0); command.Parameters.AddWithValue("@added", DateTime.Now); if (DatabaseAssist.ConnectToDatabase() == true) { command.ExecuteNonQuery(); } DatabaseAssist.ConnectToLexlets.Close(); }
public void AddToRefunds() { SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); String sql = ""; DateTime today = DateTime.Today; sql = "INSERT into tblRefunds VALUES (@date, @custID, @invoice, @refundtype, @amount, @reason, @refundmethod)"; if (DatabaseAssist.ConnectToDatabase() == true) { command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@date", today); command.Parameters.AddWithValue("@custID", CurrentCustomer.CustomerID); command.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice); command.Parameters.AddWithValue("@reason", txtReason.Text); command.Parameters.AddWithValue("@refundmethod", CurrentCustomer.PaymentMethod); if (radFullRefund.Checked) { command.Parameters.AddWithValue("@refundtype", "Full Refund"); command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(lblInvoiceTotal.Text))); } else if (radPartRefund.Checked) { command.Parameters.AddWithValue("@refundtype", "Part Refund"); command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(lblRefundTotal.Text))); } command.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void LoadData(string sql) { string userID = "1"; dataGridView1.DataSource = null; dataGridView1.Update(); dataGridView1.Refresh(); SqlCommand cmd; SqlDataAdapter adapter = new SqlDataAdapter(); try { if (cmbUsers.SelectedItem != null) { userID = DatabaseAssist.GetOneCellValue("SELECT UserId FROM tblUsers WHERE Username = @param", cmbUsers.SelectedItem.ToString(), "UserId"); } } catch { } if (DatabaseAssist.ConnectToDatabase() == true) { cmd = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); cmd.Parameters.AddWithValue("@user", Convert.ToInt32(userID)); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); adapter.Fill(ds, "tbl1"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "tbl1"; DatabaseAssist.ConnectToLexlets.Close(); } }
public void GetCostandMaterialID(Label cost, Label id, Label cat, Label Col, ComboBox desc) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter da = new SqlDataAdapter("Select MaterialId, CostPerItem FROM tblMaterials WHERE Category = @category AND ColourID = @colour AND Description =@desc", DatabaseAssist.ConnectToLexlets); da.SelectCommand.Parameters.AddWithValue("@category", Convert.ToInt32(cat.Text)); da.SelectCommand.Parameters.AddWithValue("@colour", Convert.ToInt32(Col.Text)); da.SelectCommand.Parameters.AddWithValue("@desc", desc.SelectedItem.ToString()); DataTable dt = new DataTable(); try { da.Fill(dt); cost.Text = (dt.Rows[0]["CostPerItem"].ToString()); id.Text = (dt.Rows[0]["MaterialId"].ToString()); } catch { MessageBox.Show("No Entries Found"); } DatabaseAssist.ConnectToLexlets.Close(); da.Dispose(); dt.Dispose(); } }
private void InsertIntoInvoiceProducts(DataTable orders) { for (int i = 0; i < orders.Rows.Count; i++) { string GiftBox = orders.Rows[i]["GiftBox"].ToString(); double giftPrice; string sqlquery = "insert into tblInvoiceProducts VALUES (@invoice, @sku, @size, @qty, @giftbox, @giftboxprice, @skuprice)"; SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets); if (GiftBox == "Yes") { GiftBox = "Silver Box - Bracelet"; giftPrice = 4.50; } else { GiftBox = "No"; giftPrice = 0; } com.Parameters.AddWithValue("@invoice", Convert.ToInt32(orders.Rows[i]["Order"])); com.Parameters.AddWithValue("@sku", Convert.ToInt32(orders.Rows[i]["SKU"])); com.Parameters.AddWithValue("@size", orders.Rows[i]["Size"]); com.Parameters.AddWithValue("@qty", Convert.ToInt32(orders.Rows[i]["Qty"])); com.Parameters.AddWithValue("@GiftBox", GiftBox); com.Parameters.AddWithValue("@GiftBoxprice", giftPrice); com.Parameters.AddWithValue("@skuprice", Convert.ToDouble(orders.Rows[i]["ItemPrice"])); if (DatabaseAssist.ConnectToDatabase() == true) { com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } DatabaseAssist.UpdateProductQtySold(Convert.ToInt32(orders.Rows[i]["SKU"]), Convert.ToInt32(orders.Rows[i]["Qty"]), "Add"); } }
private void BtnCopyToTable_Click(object sender, EventArgs e) { foreach (DataGridViewRow dr in dataGridViewExcel.Rows) { string sqlquery = "insert into tblImportSales VALUES (@order, @fname, @sname, @add1, @add2, @email," + "@date, @subtotal, @shipping, @total, @percent, @gbp, @payment, @sku, @qty, @price, @gift, @size, @postcode)"; SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@order", dr.Cells["Order ."].Value ?? DBNull.Value); com.Parameters.AddWithValue("@fname", dr.Cells["FirstName"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@sname", dr.Cells["SurName"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@add1", dr.Cells["Shipping Street Address"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@add2", dr.Cells["Shipping City"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@email", dr.Cells["Email Address"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@date", Convert.ToDateTime(dr.Cells["Order Date and Time Stamp"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@subtotal", Convert.ToDouble(dr.Cells["Subtotal"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@shipping", Convert.ToDouble(dr.Cells["Shipping Cost"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@total", Convert.ToDouble(dr.Cells["Total"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@percent", dr.Cells["Percent"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@gbp", Convert.ToDouble(dr.Cells["GBP"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@payment", dr.Cells["PaymentMethod"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@sku", dr.Cells["SKU"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@qty", dr.Cells["LineItem Qty"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@price", Convert.ToDouble(dr.Cells["LineItem Sale Price"].Value ?? DBNull.Value)); com.Parameters.AddWithValue("@gift", dr.Cells["GiftBox"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@size", dr.Cells["size"].Value ?? DBNull.Value); com.Parameters.AddWithValue("@postcode", dr.Cells["Shipping Zip"].Value ?? DBNull.Value); if (DatabaseAssist.ConnectToDatabase() == true) { com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } } MessageBox.Show("Table Has Been Inserted"); }
private void TxtByMatID_KeyUp(object sender, KeyEventArgs e) { string byMaterialID = txtByMatID.Text; if (Helper.CheckTextBoxForString(txtByMatID.Text) == "notnumber") { MessageBox.Show("ID Numbers do not contain letters, Please Enter a Number", "Invalid Charactor"); txtByMatID.Text = ""; txtByMatID.Focus(); } else { if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter adapter = new SqlDataAdapter("select tblMaterials.MaterialID, tblMaterialCategory.CategoryName, tblColours.ColourName, tblMaterials.Description from tblMaterials INNER JOIN tblMaterialCategory ON tblMaterialCategory.CategoryID = tblMaterials.Category INNER JOIN tblColours ON tblColours.ColourID = tblMaterials.ColourID WHERE tblMaterials.MaterialID = @byid", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@byid", byMaterialID); DataSet ds = new DataSet(); adapter.Fill(ds, "tblMaterials"); dataGridEditMaterial.DataSource = ds; dataGridEditMaterial.DataMember = "tblMaterials"; dataGridEditMaterial.Columns["CategoryName"].HeaderText = "Category"; dataGridEditMaterial.Columns["CategoryName"].Width = 100; dataGridEditMaterial.Columns["ColourName"].HeaderText = "Colour"; dataGridEditMaterial.Columns["ColourName"].Width = 125; dataGridEditMaterial.Columns["Description"].Width = 175; DatabaseAssist.ConnectToLexlets.Close(); } } }
private void AddToIncome(DataTable orders) { SqlDataAdapter adapter = new SqlDataAdapter(); String sql = ""; string invoice = orders.Rows[0]["Order"].ToString(); string fname = orders.Rows[0]["FirstName"].ToString(); string sname = orders.Rows[0]["Surname"].ToString(); string description = "Invoice No: " + invoice + " - " + fname + " " + sname; double price; double fee; if (orders.Rows[0]["PaymentMethod"].ToString() == "PayPal") { fee = (3.4 / 100) * Convert.ToDouble(orders.Rows[0]["Total"]) + 0.20; } else { fee = (1.4 / 100) * Convert.ToDouble(orders.Rows[0]["Total"]) + 0.20; } fee = Math.Round(fee, 2); price = (Convert.ToDouble(orders.Rows[0]["Subtotal"]) + Convert.ToDouble(orders.Rows[0]["Shipping"])); try { price -= Convert.ToDouble(orders.Rows[0]["CouponCode"]); } catch { } try { price -= Convert.ToDouble(orders.Rows[0]["CouponCode"]); } catch { } price -= fee; sql = "INSERT into tblincome VALUES (@date, @category, @description, @amount, @method, @receipt)"; SqlCommand com = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@date", Convert.ToDateTime(orders.Rows[0]["Date"])); com.Parameters.AddWithValue("@category", "Sale"); com.Parameters.AddWithValue("@description", description); com.Parameters.AddWithValue("@amount", price); com.Parameters.AddWithValue("@method", orders.Rows[0]["PaymentMethod"]); com.Parameters.AddWithValue("@receipt", "No"); if (DatabaseAssist.ConnectToDatabase() == true) { com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void CopyToRefundTable() { if (DatabaseAssist.ConnectToDatabase() == true) { if (radFullRefund.Checked == true) { SqlCommand cmd = new SqlCommand("INSERT tblRefundedInvoiceData SELECT * FROM tblInvoiceData WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets); SqlCommand cmd2 = new SqlCommand("INSERT tblRefundedInvoiceProducts SELECT * FROM tblInvoiceProducts WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets); cmd.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice); cmd2.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice); cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); cmd.Dispose(); cmd2.Dispose(); } else if (radPartRefund.Checked == true) { for (int i = 0; i < dgvRefunds.Rows.Count - 1; i++) { int invoice = (Convert.ToInt32(dgvRefunds.Rows[i].Cells[1].Value)); int sku = (Convert.ToInt16(dgvRefunds.Rows[i].Cells[2].Value)); int qtyToRemove = (Convert.ToInt16(dgvRefunds.Rows[i].Cells[7].Value)); int quantity = Convert.ToInt16(DatabaseAssist.GetOneCellValue("Select Quantity From tblInvoiceProducts " + "WHERE InvoiceNumber = @param AND SKU = @param2", invoice, sku, "Quantity")); int quantityLeft = quantity - qtyToRemove; SqlCommand cmd = new SqlCommand("INSERT INTO tblRefundedInvoiceProducts SELECT * FROM tblInvoiceProducts WHERE InvoiceNumber = @invoice AND SKU=@sku", DatabaseAssist.ConnectToLexlets); cmd.Parameters.AddWithValue("@invoice", invoice); cmd.Parameters.AddWithValue("@sku", sku); SqlCommand cmd2 = new SqlCommand("INSERT tblRefundedInvoiceData SELECT * FROM tblInvoiceData WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets); cmd2.Parameters.AddWithValue("@invoice", invoice); if (DatabaseAssist.ConnectToDatabase() == true) { cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); UpdateRow(invoice, sku, qtyToRemove); // Updates invoice by reducing the quantity sold if (quantityLeft == 0) { RemoveRow(invoice, sku); // Removes row as there is none left } } } } } }
private void LoadData() { dataGridView1.DataSource = null; dataGridView2.DataSource = null; dataGridView3.DataSource = null; dataGridView4.DataSource = null; dataGridView1.Update(); dataGridView2.Update(); dataGridView3.Update(); dataGridView4.Update(); dataGridView1.Refresh(); dataGridView2.Refresh(); dataGridView3.Refresh(); dataGridView4.Refresh(); SqlCommand cmd, cmd2, cmd3, cmd4; SqlDataAdapter adapter = new SqlDataAdapter(); SqlDataAdapter adapter2 = new SqlDataAdapter(); SqlDataAdapter adapter3 = new SqlDataAdapter(); SqlDataAdapter adapter4 = new SqlDataAdapter(); if (DatabaseAssist.ConnectToDatabase() == true) { cmd = new SqlCommand("SELECT * FROM tblInvoiceData", DatabaseAssist.ConnectToLexlets); cmd2 = new SqlCommand("SELECT * FROM tblInvoiceProducts", DatabaseAssist.ConnectToLexlets); cmd3 = new SqlCommand("SELECT * FROM tblRefundedInvoiceData", DatabaseAssist.ConnectToLexlets); cmd4 = new SqlCommand("SELECT * FROM tblRefundedInvoiceProducts", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand = cmd; adapter2.SelectCommand = cmd2; adapter3.SelectCommand = cmd3; adapter4.SelectCommand = cmd4; DataSet ds = new DataSet(); DataSet ds2 = new DataSet(); DataSet ds3 = new DataSet(); DataSet ds4 = new DataSet(); adapter.Fill(ds, "tbl1"); adapter2.Fill(ds2, "tbl2"); adapter3.Fill(ds3, "tbl3"); adapter4.Fill(ds4, "tbl4"); dataGridView1.DataSource = ds; dataGridView2.DataSource = ds2; dataGridView3.DataSource = ds3; dataGridView4.DataSource = ds4; dataGridView1.DataMember = "tbl1"; dataGridView2.DataMember = "tbl2"; dataGridView3.DataMember = "tbl3"; dataGridView4.DataMember = "tbl4"; DatabaseAssist.ConnectToLexlets.Close(); } }
private void BtnSave_Click(object sender, EventArgs e) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter getcatID = new SqlDataAdapter("SELECT CategoryId FROM tblMaterialCategory WHERE CategoryName = @category", DatabaseAssist.ConnectToLexlets); getcatID.SelectCommand.Parameters.AddWithValue("@category", cmbCategory.SelectedItem.ToString()); DataTable dt = new DataTable(); getcatID.Fill(dt); int catid = Convert.ToInt32((dt.Rows[0]["CategoryId"])); SqlDataAdapter getcolID = new SqlDataAdapter("SELECT ColourId FROM tblColours WHERE ColourName = @cols", DatabaseAssist.ConnectToLexlets); getcolID.SelectCommand.Parameters.AddWithValue("@cols", cmbColour.SelectedItem.ToString()); DataTable dt1 = new DataTable(); getcolID.Fill(dt1); int colid = Convert.ToInt32((dt1.Rows[0]["ColourId"])); SqlDataAdapter getsupID = new SqlDataAdapter("SELECT SupplierId FROM tblSuppliers WHERE SupplierName = @sup", DatabaseAssist.ConnectToLexlets); getsupID.SelectCommand.Parameters.AddWithValue("@sup", cmbSupplier.SelectedItem.ToString()); DataTable dt2 = new DataTable(); getsupID.Fill(dt2); int supid = Convert.ToInt32((dt2.Rows[0]["SupplierId"])); dt.Dispose(); dt1.Dispose(); dt2.Dispose(); SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblMaterials SET Category = @cat, ColourID = @cols, Description = @desc, QTYinStock = @qty, LowLevelWarning = @low, CostPerItem = @cost, SizeOnBracelet = @size, SupplierID = @supplier Where MaterialID = @id", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@cat", catid); adapter.SelectCommand.Parameters.AddWithValue("@cols", colid); adapter.SelectCommand.Parameters.AddWithValue("@desc", descriptionTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@qty", qTYinStockTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@Low", lowLevelWarningTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@cost", costPerItemTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@size", sizeonBraceletTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text); adapter.SelectCommand.Parameters.AddWithValue("@supplier", supid); adapter.SelectCommand.ExecuteNonQuery(); if (!String.IsNullOrEmpty(txtImagePath.Text)) { byte[] imagebt = null; FileStream fstream = new FileStream(txtImagePath.Text, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fstream); imagebt = br.ReadBytes((int)fstream.Length); SqlDataAdapter adapter1 = new SqlDataAdapter("UPDATE tblMaterials SET Image =@img Where MaterialID = @id", DatabaseAssist.ConnectToLexlets); adapter1.SelectCommand.Parameters.AddWithValue("@img", imagebt); adapter1.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text); adapter1.SelectCommand.ExecuteNonQuery(); } DatabaseAssist.ConnectToLexlets.Close(); User.AddToUserLog("Update Material", User.Username + " Updated Material (" + cmbCategory.SelectedItem.ToString() + " - " + descriptionTextBox.Text + ")"); MessageBox.Show("Material Updated"); ClearAll(); } }
private void DeleteRow(int id) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlCommand com = new SqlCommand("DELETE FROM tblCustomers WHERE CustomerId = @id", DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@id", id); com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void BtnSave_Click(object sender, EventArgs e) { if (validateEntry() != 8) { MessageBox.Show("Please Fill In All Fields Correctly"); } else { byte[] imagebt = null; FileStream fstream = new FileStream(txtImagePath.Text, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fstream); imagebt = br.ReadBytes((int)fstream.Length); SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); String sql = ""; sql = "INSERT into tblMaterials (Category, ColourID, Description, QTYinStock, LowLevelWarning, CostPerItem, SizeOnBracelet, Image, SupplierID) VALUES (@category, @colourID, @description, @qty, @lowlevel, @costperitem, @sizeon, @image, @supplierID)"; if (DatabaseAssist.ConnectToDatabase() == true) { command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@category", cmbCategory.SelectedValue.ToString()); command.Parameters.AddWithValue("@colourID", cmbColour.SelectedValue.ToString()); command.Parameters.AddWithValue("@description", descriptionTextBox.Text); command.Parameters.AddWithValue("@qty", qTYinStockTextBox.Text); command.Parameters.AddWithValue("@lowlevel", lowLevelWarningTextBox.Text); command.Parameters.AddWithValue("@costPerItem", costPerItemTextBox.Text); command.Parameters.AddWithValue("@sizeon", sizeonBraceletTextBox.Text); command.Parameters.AddWithValue("@image", imagebt); command.Parameters.AddWithValue("@supplierID", (cmbSupplier.SelectedItem as ComboBoxItem).Value.ToString()); int i = command.ExecuteNonQuery(); command.Dispose(); DatabaseAssist.ConnectToLexlets.Close(); if (i != 0) { User.AddToUserLog("Added Material", User.Username + " Added " + cmbCategory.SelectedText + " " + descriptionTextBox.Text + " To the system"); DialogResult result = MessageBox.Show("Material Saved - Would you like to add another?", "Success", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { ClearAll(); } else { this.Close(); } } else { MessageBox.Show("Error Please Try Again", "Error"); } } } }
private void btnUpdate_Click(object sender, EventArgs e) { DatabaseAssist.ConnectToDatabase(); SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblUsers SET AccessLevel = @access Where UserId = @user", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@access", cmbAccess.SelectedIndex + 1); adapter.SelectCommand.Parameters.AddWithValue("@user", lblUserId.Text); adapter.SelectCommand.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); MessageBox.Show("User Access Level Updated", "Success"); }
private void AlterIdFromInvoices(int newid, int oldId) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlCommand com = new SqlCommand("Update tblInvoiceData set CustomerId = @newId WHERE CustomerId = @oldId", DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@newId", newid); com.Parameters.AddWithValue("@oldId", oldId); com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void BtnDelete_Click(object sender, EventArgs e) { User.AddToUserLog("Delete Material", User.Username + " Deleted Material (" + cmbCategory.SelectedItem.ToString() + " - " + descriptionTextBox.Text + ")"); if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter adapter = new SqlDataAdapter("Delete From tblMaterials WHERE MaterialID = @id", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text); adapter.SelectCommand.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); MessageBox.Show("Material Deleted"); ClearAll(); } }
private void CopyToOldAdresses(int id, string add1, string add2, string postcode) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlCommand com = new SqlCommand("INSERT INTO tblCustomersOtherAdresses VALUES(@id, @add1, @add2, @postcode)", DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@id", id); com.Parameters.AddWithValue("@add1", add1); com.Parameters.AddWithValue("@add2", add2); com.Parameters.AddWithValue("@postcode", postcode); com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void RemoveRow(int invoice, int sku) { SqlCommand cmd = new SqlCommand("DELETE From tblInvoiceProducts WHERE InvoiceNumber = @invoice AND SKU = @sku", DatabaseAssist.ConnectToLexlets); cmd.Parameters.AddWithValue("@invoice", invoice); cmd.Parameters.AddWithValue("@sku", sku); if (DatabaseAssist.ConnectToDatabase() == true) { cmd.ExecuteNonQuery(); cmd.Dispose(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void BtnUnlock_Click(object sender, EventArgs e) { DatabaseAssist.ConnectToDatabase(); SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblUsers SET AccountLocked = @locked, FailedLoginAttempts = @attempts Where UserId = @user", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@locked", "false"); adapter.SelectCommand.Parameters.AddWithValue("@attempts", 0); adapter.SelectCommand.Parameters.AddWithValue("@user", lblUserId.Text); adapter.SelectCommand.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); MessageBox.Show("Account unlocked", "Success"); LoadUser(); }
private void BtnDelete_Click(object sender, EventArgs e) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter adapter = new SqlDataAdapter("Delete From tblUsers WHERE UserId = @id", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@id", lblUserId.Text); adapter.SelectCommand.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); MessageBox.Show(cmbUser.SelectedItem.ToString() + " has been removed", "User Deleted"); pnlUserInfo.Visible = false; cmbUser.Text = ""; Helper.FillCombo(cmbUser, "Select Username from tblUsers", "Username"); } }
private void AddNewUserToDatabase(string hashed, string salt) { DatabaseAssist.ConnectToDatabase(); SqlCommand command = new SqlCommand(@"INSERT into tblUsers (Username, Password, AccessLevel, FailedLoginAttempts, AccountLocked, Salt, Email) VALUES (@user, @password, @access, @failed, @locked, @salt, @email)", DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@user", txtUsername.Text.ToCamelCase()); command.Parameters.AddWithValue("@password", hashed); command.Parameters.AddWithValue("@access", 3); command.Parameters.AddWithValue("@failed", 0); command.Parameters.AddWithValue("@locked", "false"); command.Parameters.AddWithValue("@salt", salt); command.Parameters.AddWithValue("@email", txtEmail.Text.ToLower()); command.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); }
private void DataGridEditMaterial_CellClick(object sender, DataGridViewCellEventArgs e) { try { int id = Convert.ToInt32(dataGridEditMaterial.Rows[e.RowIndex].Cells["MaterialId"].Value.ToString()); BtnCancel.Enabled = true; BtnUpdate.Enabled = true; BtnDelete.Enabled = true; if (DatabaseAssist.ConnectToDatabase() == true) { SqlCommand cmd = DatabaseAssist.ConnectToLexlets.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from tblMaterials INNER JOIN tblColours on tblColours.ColourId = tblMaterials.ColourID INNER JOIN tblMaterialCategory on tblMaterialCategory.CategoryId = tblMaterials.Category INNER JOIN tblSuppliers on tblSuppliers.SupplierId = tblMaterials.SupplierID WHERE tblMaterials.MaterialID= @id "; cmd.Parameters.AddWithValue("@id", id); cmd.ExecuteNonQuery(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); foreach (DataRow dr in dt.Rows) { materialIdTextBox.Text = dr["MaterialID"].ToString(); cmbCategory.Text = dr["CategoryName"].ToString(); cmbColour.Text = dr["ColourName"].ToString(); descriptionTextBox.Text = dr["Description"].ToString(); qTYinStockTextBox.Text = dr["QTYinStock"].ToString(); lowLevelWarningTextBox.Text = dr["LowLevelWarning"].ToString(); costPerItemTextBox.Text = dr["CostPerItem"].ToString(); sizeonBraceletTextBox.Text = dr["SizeOnBracelet"].ToString(); cmbSupplier.Text = dr["SupplierName"].ToString(); Image x = (Bitmap)((new ImageConverter()).ConvertFrom(dr["Image"])); picLoadImage.Image = x; } DatabaseAssist.ConnectToLexlets.Close(); gbxEditMaterial.Enabled = true; gbxFindMaterial.Enabled = false; btnChange.Visible = true; } } catch { MessageBox.Show("Please Select a Row, Not a Column"); return; } }
private void lblNewUser_Click(object sender, EventArgs e) { if (cmbDatabase.SelectedItem == null) { MessageBox.Show("Please select a database to register too", "No database selected"); return; } DatabaseAssist.SetConnectionStringAsync(cmbDatabase.SelectedItem.ToString()); if (DatabaseAssist.ConnectToDatabase() == true) { FrmRegisterNewUser newUser = new FrmRegisterNewUser(); newUser.ShowDialog(); } }
private void UpdateInvoiceData(int invoice) { double invoiceTotal = Convert.ToDouble(DatabaseAssist.GetOneCellValue("Select InvoiceTotal From tblInvoiceData " + "WHERE InvoiceNumber = @param", invoice, "InvoiceTotal")); double newPrice = invoiceTotal - Convert.ToDouble(Helper.RemovePoundSign(lblRefundTotal.Text)); if (DatabaseAssist.ConnectToDatabase() == true) { SqlCommand cmd3 = new SqlCommand("UPDATE tblInvoiceData set InvoiceTotal = @price WHERE InvoiceNumber =@invoice", DatabaseAssist.ConnectToLexlets); cmd3.Parameters.AddWithValue("@invoice", invoice); cmd3.Parameters.AddWithValue("@price", newPrice); cmd3.ExecuteNonQuery(); cmd3.Dispose(); DatabaseAssist.ConnectToLexlets.Close(); } }
private void BtnSave_Click(object sender, EventArgs e) { if (ValidateEntries() > 0) { MessageBox.Show("Please correct highlighted fields", "Error"); } else { SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); String sql = ""; sql = "INSERT into tblCustomers VALUES (@fname, @sname, @add1, @add2, @pcode, @email, @sub)"; if (DatabaseAssist.ConnectToDatabase() == true) { command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@fname", TxtFirstName.Text); command.Parameters.AddWithValue("@sname", TxtSurName.Text); command.Parameters.AddWithValue("@add1", TxtAdd1.Text); command.Parameters.AddWithValue("@add2", TxtAdd2.Text); command.Parameters.AddWithValue("@pcode", TxtPostCode.Text); command.Parameters.AddWithValue("@email", TxtEmail.Text); command.Parameters.AddWithValue("@sub", CmbSub.SelectedItem); int i = command.ExecuteNonQuery(); command.Dispose(); DatabaseAssist.ConnectToLexlets.Close(); if (i != 0) { MessageBox.Show("Customer Saved"); TxtFirstName.Text = ""; TxtSurName.Text = ""; TxtAdd1.Text = ""; TxtAdd2.Text = ""; TxtEmail.Text = ""; TxtPostCode.Text = ""; CmbSub.Text = ""; } else { MessageBox.Show("Error"); } } } }
public void GetColID(ComboBox colID, Label labelID) { if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter da = new SqlDataAdapter("Select ColourID FROM tblColours WHERE ColourName = @name", DatabaseAssist.ConnectToLexlets); da.SelectCommand.Parameters.AddWithValue("@name", colID.SelectedItem); DataTable dt = new DataTable(); da.Fill(dt); labelID.Text = (dt.Rows[0]["ColourID"].ToString()); DatabaseAssist.ConnectToLexlets.Close(); da.Dispose(); dt.Dispose(); dt.Clear(); } }