void LoadItems() { //empty all display data InvoiceItemsList.Clear(); InvoiceItemListbox.Items.Clear(); SubTotTxtbox.Clear(); PstTxtbox.Clear(); GstTxtbox.Clear(); TotalTxtbox.Clear(); //to create first list as a heading column InvoiceItemListbox.Items.Add(String.Format("{0,5}{1,30}{2,50}{3,60}{4,30}", "Item ID ", "Item Name", "Item Description", "Item Price", "Quantity")); decimal SubTotal = 0;//assign subtotal //Create and open a connection using (SqlConnection connection = new SqlConnection()) { if (CurrentSelectedInvoice != null) { // replace absolute file path with |DataDirectory| connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True"; connection.Open(); //Create SQL command object string sqlCommand = $"Select * From InvoiceItems WHERE InvoiceID = {CurrentSelectedInvoice.InvoiceID}"; SqlCommand myCommand = new SqlCommand(sqlCommand, connection); using (SqlDataReader Reader = myCommand.ExecuteReader()) { while (Reader.Read()) { //Create new InvoiceItems Object from the record InvoiceItems newInvoiceItems = new InvoiceItems((int)Reader[0], (int)Reader[1], (String)Reader[2], (string)Reader[3], (decimal)Reader[4], (int)Reader[5]); //Add to list InvoiceItemsList.Add(newInvoiceItems); //Add to listbox InvoiceItemListbox.Items.Add(newInvoiceItems); //when items are loaded(loop), multiply quantity and price and add up in subtoal SubTotal += newInvoiceItems.ItemPrice * newInvoiceItems.ItemQuantity; //calculate based on subtotal decimal PST = SubTotal * 6 / 100; decimal GST = SubTotal * 5 / 100; decimal Total = SubTotal + PST + GST; //display in textboxes SubTotTxtbox.Text = SubTotal.ToString("C2"); PstTxtbox.Text = PST.ToString("C2"); GstTxtbox.Text = GST.ToString("C2"); TotalTxtbox.Text = Total.ToString("C2"); } } } } }
void SaveItems() { string sql; //create a new object from form data InvoiceItems newItems = new InvoiceItems(); //get changed data from form newItems.ItemName = ItemNameTxtbox.Text; newItems.ItemDescription = ItemDescTxtbox.Text; newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text); newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text); using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True"; //open connection connection.Open(); //find the last primary key used sql = $"Select MAX(ItemID) FROM InvoiceItems;"; int NewItemID; using (SqlCommand Selection = new SqlCommand(sql, connection)) { NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1; newItems.ItemID = NewItemID; } sql = $"INSERT INTO InvoiceItems " + "(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " + "VALUES " + $"('{NewItemID}'," + $"'{CurrentSelectedInvoice.InvoiceID}'," + $"'{newItems.ItemName}'," + $"'{newItems.ItemDescription}'," + $"'{newItems.ItemPrice.ToString()}'," + $"'{newItems.ItemQuantity.ToString()}')"; using (SqlCommand InsertCommand = new SqlCommand(sql, connection)) { InsertCommand.ExecuteNonQuery(); } LoadItems(); int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems); InvoiceItemListbox.SelectedIndex = NewItemIndex; InvoiceItemListbox.ScrollIntoView(newItems); } isNewRecord = false; }
private void InvoiceItemListbox_SelectionChanged(object sender, SelectionChangedEventArgs e) { if (InvoiceItemListbox.SelectedIndex == 0) { InvoiceItemListbox.SelectedIndex = 1; } CurrentSeledtedItems = (InvoiceItems)InvoiceItemListbox.SelectedItem; CurrentSelectedItemIndex = InvoiceItemListbox.SelectedIndex; DisplayItems(); isNewRecord = false; }
private void InvoiceItemListbox_SelectionChanged(object sender, SelectionChangedEventArgs e) { //To fix first item as heading column int the item listbox if (InvoiceItemListbox.SelectedIndex == 0) { InvoiceItemListbox.SelectedIndex = 1; } CurrentSeledtedItems = (InvoiceItems)InvoiceItemListbox.SelectedItem; CurrentSelectedItemIndex = InvoiceItemListbox.SelectedIndex; DisplayItems(); isNewRecord = false; }
void LoadItems() { //emply all display data InvoiceItemsList.Clear(); InvoiceItemListbox.Items.Clear(); SubTotTxtbox.Clear(); PstTxtbox.Clear(); GstTxtbox.Clear(); TotalTxtbox.Clear(); InvoiceItemListbox.Items.Add(String.Format("{0,5}{1,20}{2,40}{3,80}{4,40}", "Item ID ", "Item Name", "Item Description", "Item Price", "Price")); decimal SubTotal = 0;//assign subtotal using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True"; connection.Open(); string sqlCommand = $"Select * From InvoiceItems WHERE InvoiceID = {CurrentSelectedInvoice.InvoiceID}"; SqlCommand myCommand = new SqlCommand(sqlCommand, connection); using (SqlDataReader Reader = myCommand.ExecuteReader()) { while (Reader.Read()) { InvoiceItems newInvoiceItems = new InvoiceItems((int)Reader[0], (int)Reader[1], (String)Reader[2], (string)Reader[3], (decimal)Reader[4], (int)Reader[5]); InvoiceItemsList.Add(newInvoiceItems); InvoiceItemListbox.Items.Add(newInvoiceItems); //when items are loaded, multiply quantity and price and add up in subtoal SubTotal += newInvoiceItems.ItemPrice * newInvoiceItems.ItemQuantity; //calculate based on subtotal decimal PST = SubTotal * 6 / 100; decimal GST = SubTotal * 5 / 100; decimal Total = SubTotal + PST + GST; //display in textboxes SubTotTxtbox.Text = Convert.ToString(SubTotal); PstTxtbox.Text = Convert.ToString(PST); GstTxtbox.Text = Convert.ToString(GST); TotalTxtbox.Text = Convert.ToString(Total); } } } }
void SaveItems() { string sql; //create a new object from form data InvoiceItems newItems = new InvoiceItems(); //get changed data from form newItems.ItemName = ItemNameTxtbox.Text; newItems.ItemDescription = ItemDescTxtbox.Text; newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text); newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text); using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True"; //open connection connection.Open(); //find the last primary key used sql = $"Select MAX(ItemID) FROM InvoiceItems;"; //assing new item ID int NewItemID; //to create new primary key(item ID) using (SqlCommand Selection = new SqlCommand(sql, connection)) { NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1; newItems.ItemID = NewItemID; } //insert new data into database sql = $"INSERT INTO InvoiceItems " + "(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " + "VALUES " + $"('{NewItemID}'," + $"'{CurrentSelectedInvoice.InvoiceID}'," + $"'{newItems.ItemName}'," + $"'{newItems.ItemDescription}'," + $"'{newItems.ItemPrice.ToString()}'," + $"'{newItems.ItemQuantity.ToString()}')"; using (SqlCommand InsertCommand = new SqlCommand(sql, connection)) { InsertCommand.ExecuteNonQuery(); } //Update List<InvoiceItemsList> InvoiceItemsList.Add(newItems); //clear all data displayed and update data InvoiceItemListbox.Items.Clear(); LoadItems(); //finde index of new item int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems); //selec the new item InvoiceItemListbox.SelectedIndex = NewItemIndex; InvoiceItemListbox.ScrollIntoView(newItems); } //close saving function until users click add button isNewRecord = false; }