I have no idea what to do anymore with this code. I did review the opening and closing of my connections and I think all connections are okay.
I have to tables here that I'm trying to insert data. The imf table and the imf_products table. My goal is as soon as I have inserted data to my imf table I should insert data again to my imf_products.
This is my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
namespace Natasha_POS
{
public partial class m2 : Form
{
public SQLiteConnection con = new SQLiteConnection(@"Data Source=default.db");
public m2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (combo_compname.Text.Length <= 0)
{
MessageBox.Show("Please specify the company name.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (combo_artname.Text.Length <= 0)
{
MessageBox.Show("Please specify the product name.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (combo_color.Text.Length <= 0)
{
MessageBox.Show("Please specify the product color.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (combo_size.Text.Length <= 0)
{
MessageBox.Show("Please specify the product size.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox8.Text.Length <= 0)
{
MessageBox.Show("Please specify the product price.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (box_numeric.Text.Length <= 0)
{
MessageBox.Show("Please specify the number of products you're acquiring.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
dGrid.Rows.Add(combo_artname.Text,
combo_color.Text,
combo_size.Text,
Convert.ToInt32(box_numeric.Value),
Convert.ToInt32( textBox8.Text),
combo_discount.Text,"","",
Convert.ToInt32(combo_artname.SelectedValue),
Convert.ToInt32(combo_color.SelectedValue),
Convert.ToInt32(combo_size.SelectedValue),
Convert.ToInt32(combo_discount.SelectedValue)
);
////new
combo_compname.Text = "--Select Company--";
combo_cat.Text = "--Select Category--";
combo_artname.Text = "--Select Product--";
combo_color.Text = "--Select Color--";
combo_size.Text = "--Select Size--";
textBox8.Clear();
box_numeric.Value = 0;
combo_custno.Focus();
}
}
private void m2_Load(object sender, EventArgs e)
{
dateTimePicker.Enabled = false;
box_custname.Text = null;
SQLiteConnection conn1 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt = new DataTable();
conn1.Open();
SQLiteCommand cmd = conn1.CreateCommand();
cmd.CommandText = "SELECT * from customer";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(dt);
combo_custno.DataSource = dt;
combo_custno.ValueMember = "cp_id";
combo_custno.DisplayMember = "customer_no";
combo_custno.Text = "--Select Customer--";
/*IList<string> CustNo = new List<string>();
foreach (DataRow row in dt.Rows)
{
CustNo.Add(row.Field<string>("cp_id"));
}
combo_custno.Items.AddRange(CustNo.ToArray<string>());
combo_custno.AutoCompleteMode = AutoCompleteMode.Suggest;
combo_custno.AutoCompleteSource = AutoCompleteSource.ListItems; */
conn1.Close();
SQLiteConnection conn2 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt2 = new DataTable();
conn2.Open();
SQLiteCommand cmd2 = conn2.CreateCommand();
cmd2.CommandText = "SELECT * from company";
SQLiteDataAdapter adapter2 = new SQLiteDataAdapter(cmd2);
adapter2.Fill(dt2);
combo_compname.DataSource = dt2;
combo_compname.ValueMember = "company_id";
combo_compname.DisplayMember = "company_name";
combo_compname.Text = "--Select Company--";
conn2.Close();
SQLiteConnection conn3 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt3 = new DataTable();
conn3.Open();
SQLiteCommand cmd3 = conn3.CreateCommand();
cmd3.CommandText = "SELECT category_id, category_name FROM category";
SQLiteDataAdapter adapter3 = new SQLiteDataAdapter(cmd3);
adapter3.Fill(dt3);
combo_cat.DataSource = dt3;
combo_cat.ValueMember = "category_id";
combo_cat.DisplayMember = "category_name";
combo_cat.Text = "--Select Category--";
conn3.Close();
SQLiteConnection conn4 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt4 = new DataTable();
conn4.Open();
SQLiteCommand cmd4 = conn4.CreateCommand();
cmd4.CommandText = "SELECT * FROM colors";
SQLiteDataAdapter adapter4 = new SQLiteDataAdapter(cmd4);
adapter4.Fill(dt4);
combo_color.DataSource = dt4;
combo_color.ValueMember = "color_id";
combo_color.DisplayMember = "color_name";
combo_color.Text = "--Select Color--";
conn4.Close();
SQLiteConnection conn5 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt5 = new DataTable();
conn5.Open();
SQLiteCommand cmd5 = conn5.CreateCommand();
cmd5.CommandText = "SELECT * FROM discount";
SQLiteDataAdapter adapter5 = new SQLiteDataAdapter(cmd5);
adapter5.Fill(dt5);
combo_discount.DataSource = dt5;
combo_discount.ValueMember = "discount_id";
combo_discount.DisplayMember = "discount_name";
combo_discount.Text = "";
conn5.Close();
//this.reportViewer1.RefreshReport();
}
private void combo_custno_SelectedIndexChanged(object sender, EventArgs e)
{
// int x = Convert.ToInt32(combo_custno.Text);
//////////textboxes
SQLiteConnection conn4 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt4 = new DataTable();
conn4.Open();
SQLiteCommand cmd4 = conn4.CreateCommand();
cmd4.CommandText = "SELECT * FROM customer where cp_id = '" + combo_custno.SelectedValue + "'";
SQLiteDataReader dr = cmd4.ExecuteReader();
if (dr.Read())
{
//string fname = Convert.ToString(dr["firstname"]);
//string mname = Convert.ToString(dr["firstname"]);
box_custname.Text = Convert.ToString(dr["firstname"]) + " " + Convert.ToString(dr["middlename"]) + " " + Convert.ToString(dr["lastname"]);
box_mse.Text = Convert.ToString(dr["msediscount"]);
box_nfc.Text = Convert.ToString(dr["nfcdiscount"]);
box_rating.Text = Convert.ToString(dr["rating"]);
box_avcredline.Text = Convert.ToString(dr["available_credit_line"]);
}
conn4.Close();
}
private void combo_cat_SelectedIndexChanged(object sender, EventArgs e)
{
//int x = Convert.ToInt32(combo_cat.Text);
SQLiteConnection conn2 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt2 = new DataTable();
conn2.Open();
SQLiteCommand cmd2 = conn2.CreateCommand();
cmd2.CommandText = "SELECT * FROM product where category_id = '" + combo_cat.SelectedValue+ "'";
SQLiteDataAdapter adapter2 = new SQLiteDataAdapter(cmd2);
adapter2.Fill(dt2);
combo_artname.DataSource = dt2;
combo_artname.ValueMember = "product_id";
combo_artname.DisplayMember = "product_name";
combo_artname.Text = "--Select Product--";
conn2.Close();
SQLiteConnection conn3 = new SQLiteConnection(@"Data Source=default.db");
DataTable dt3 = new DataTable();
conn3.Open();
SQLiteCommand cmd3 = conn3.CreateCommand();
cmd3.CommandText = "SELECT * FROM size where category_id = '" + combo_cat.SelectedValue + "'";
SQLiteDataAdapter adapter3 = new SQLiteDataAdapter(cmd3);
adapter3.Fill(dt3);
combo_size.DataSource = dt3;
combo_size.ValueMember = "size_id";
combo_size.DisplayMember = "size_name";
combo_size.Text = "--Select Size--";
conn3.Close();
}
private void groupBox2_Enter(object sender, EventArgs e)
{
}
private void inserttoSO(string custno)
{
con.Open();
SQLiteCommand cmd = con.CreateCommand();
cmd.Parameters.AddWithValue("@custno", combo_custno.Text);
cmd.Parameters.AddWithValue("@date", dateTimePicker.Value);
//cmd.Parameters.AddWithValue("@comment", box_commment.Text);
cmd.CommandText = "INSERT INTO sales_order (customer_no,date) VALUES (@custno,@date)";
cmd.ExecuteNonQuery();
con.Close();
}
private void button3_Click(object sender, EventArgs e)
{
//inserttoSO(combo_custno.Text);
var results = MessageBox.Show("Are you sure you want to generate this inventory report?",
"Inventory report is successfully made.",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (results == DialogResult.Yes)
{
inserttoSO(combo_custno.Text);
string StrQuery = "";
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=default.db"))
{
using (SQLiteCommand cmd1 = new SQLiteCommand())
{
cmd1.Connection = conn;
conn.Open();
for (int i = 0; i < dGrid.Rows.Count; i++)
{
/*"INSERT INTO imf_products(imf_id, company_id,product_id, color_id,size_id,price,qty) VALUES (1,1,1,1,1,1,1)";*/
//(SELECT max(imf_id) FROM imf) StrQuery
cmd1.CommandText =
"INSERT INTO orders(salesorder_id, product_id, quantity,price,cash, discount_id) VALUES ((SELECT max(salesorder_id) FROM sales_order),'"
+ dGrid.Rows[i].Cells["product_id"].Value + "', '" + dGrid.Rows[i].Cells["qty"].Value + "', '"
+ dGrid.Rows[i].Cells["price"].Value + "', '" + dGrid.Rows[i].Cells["cash"].Value + "', '"
+ dGrid.Rows[i].Cells["discount_id"].Value + "')";
// cmd.CommandText = StrQuery;
cmd1.ExecuteNonQuery();
MessageBox.Show("Sales report is successfully made.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
// conn.Close();
}
}
}
else
{ MessageBox.Show("Inventory report is not made.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); }
}
private void textBox8_KeyPress(object sender, KeyPressEventArgs e)
{
if (!char.IsControl(e.KeyChar)
&& !char.IsDigit(e.KeyChar)
&& e.KeyChar != '.' && e.KeyChar != ',')
{
e.Handled = true;
}
//check if '.' , ',' pressed
char sepratorChar = 's';
if (e.KeyChar == '.' || e.KeyChar == ',')
{
// check if it's in the beginning of text not accept
if (textBox8.Text.Length == 0) e.Handled = true;
// check if it's in the beginning of text not accept
if (textBox8.SelectionStart == 0) e.Handled = true;
// check if there is already exist a '.' , ','
if (alreadyExist(textBox8.Text, ref sepratorChar)) e.Handled = true;
//check if '.' or ',' is in middle of a number and after it is not a number greater than 99
if (textBox8.SelectionStart != textBox8.Text.Length && e.Handled == false)
{
// '.' or ',' is in the middle
string AfterDotString = textBox8.Text.Substring(textBox8.SelectionStart);
if (AfterDotString.Length > 2)
{
e.Handled = true;
}
}
}
//check if a number pressed
if (Char.IsDigit(e.KeyChar))
{
//check if a coma or dot exist
if (alreadyExist(textBox8.Text, ref sepratorChar))
{
int sepratorPosition = textBox8.Text.IndexOf(sepratorChar);
string afterSepratorString = textBox8.Text.Substring(sepratorPosition + 1);
if (textBox8.SelectionStart > sepratorPosition && afterSepratorString.Length > 1)
{
e.Handled = true;
}
}
}
}
private bool alreadyExist(string _text, ref char KeyChar)
{
if (_text.IndexOf('.') > -1)
{
KeyChar = '.';
return true;
}
if (_text.IndexOf(',') > -1)
{
KeyChar = ',';
return true;
}
return false;
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
dateTimePicker.Enabled = false;
}
private void groupBox1_Enter(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
inserttoSO(combo_custno.Text);
}
}
}