public static int AddStatements(Statements s) { int rowsinserted = 0; string connectionString = null; OleDbConnection conn = null; connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DPE2.mdb;"; conn = new OleDbConnection(connectionString); try { conn.Open(); OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; comm.CommandText = "INSERT INTO STATEMENTS VALUES (@invNum, @date, @amount, @custID)"; comm.Parameters.AddWithValue("@invNum", s.InvoiceNumber); comm.Parameters.AddWithValue("@custID", s.Id); comm.Parameters.AddWithValue("@date", s.Date); comm.Parameters.AddWithValue("@amount", s.Currency); rowsinserted = comm.ExecuteNonQuery(); conn.Close(); } catch (OleDbException e) { throw e; } return rowsinserted; }
private void button5_Click(object sender, EventArgs e)//add to invoice button { TextBox[] invoiceNumberArr = new TextBox[] { invoiceNumber1, invoiceNumber2, invoiceNumber3, invoiceNumber4, invoiceNumber5, invoiceNumber6, invoiceNumber7, invoiceNumber8 }; string one = dtpCD1.Text; string two = dtpCD2.Text; string three = dtpCD3.Text; string four = dtpCD4.Text; string five = dtpCD5.Text; string six = dtpCD6.Text; string seven = dtpCD7.Text; string eight = dtpCD8.Text; string[] DateArr = new string[] { one, two, three, four, five, six, seven, eight }; TextBox[] AmountArr = new TextBox[] { Amount1, Amount2, Amount3, Amount4, Amount5, Amount6, Amount7, Amount8 }; for (int i = 0; i < 8; i++) { // get invoice number string amt = AmountArr[i].Text.ToString(); string st = DateArr[i].ToString(); string invoiceNumber = DBManager.GetInvoiceNum(invoiceNumberArr[i].Text); int invoice = 0; double amount = 0.00; if (invoiceNumberArr[i].Text == "") { i++; } else if (comboBox3.Text == "") { MessageBox.Show("Empty Company name"); break; } else if (invoiceNumberArr[i].Text.Equals(invoiceNumber)) { lblStatus.Visible = true; lblStatus.Text = "Please check the invoice is already in database at Row:" + (i + 1); break; } else if (!int.TryParse(invoiceNumberArr[i].Text, out invoice)) { foreach (char c in invoiceNumberArr[i].Text) { if (!Char.IsDigit(c)) lblStatus.Visible = true; lblStatus.Text = "Please check the invoice number is correct in Row:" + (i + 1); } break; } else if (st.Equals("01-01-1753")) { lblStatus.Visible = true; lblStatus.Text = "Please check the date is correct in Row:" + (i + 1); break; } else if (!double.TryParse(AmountArr[i].Text, out amount)) { lblStatus.Visible = true; lblStatus.Text = "Please check the amount is correct in Row:" + (i + 1); break; } else if (invoiceNumberArr[i].Text != "" && st != "1-1-1753" && amt != "") { DateTime dt = Convert.ToDateTime(DateArr[i].ToString()); double amT = Convert.ToDouble(amt); Statements s = new Statements(invoiceNumberArr[i].Text, DBManager.GetCustomerID(comboBox3.Text),dt, amT); if (DBManager.AddStatements(s) == 1) { lblStatus.Visible = true; lblStatus.Text = "Registration Successful"; } else { lblStatus.Visible = true; lblStatus.Text = "Registration Unsuccessful"; } } else { i++; } } // 1 // Open connection\ string companyName = comboBox3.Text; if (companyName == "") { MessageBox.Show("Empty Company"); } string connectionString = null; OleDbConnection conn = null; connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DPE2.mdb;"; conn = new OleDbConnection(connectionString); { conn.Open(); // 2 // Create new DataAdapter using (OleDbDataAdapter a = new OleDbDataAdapter( "SELECT [Invoice Number] ,[Date] ,[Amount] FROM Statements Where Customer_name ='" + companyName + "' Order By Date", conn)) { // 3 // Use DataAdapter to fill DataTable DataTable t = new DataTable(); a.Fill(t); // 4 // Render data onto the screen dataGridView4.DataSource = t; } } }