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;
}
}
}
