I want to put a feature in my Inventory system where a warning message box will appear when the program detects that the quantity of an item in the database is below 15. I don't know what method/command to use and where to put it.
Here 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.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace inventoryForm
{
public partial class inventory : Form
{
private OleDbConnection connection = new OleDbConnection();
public inventory()
{
InitializeComponent();
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eziera Yvanne\Documents\Inventory.accdb; Persist Security Info = False";
}
private void inventory_Load(object sender, EventArgs e)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select * from Inventory";
command.CommandText = query;
// Load Inventory table to ComboBox
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
combo_items.Items.Add(reader["Item"].ToString());
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Inventory ComboBox
private void combo_inventory_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select * from Inventory where Item='" + combo_items.Text + "'";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
text_quantity.Text = reader["Quantity"].ToString();
label_itemID.Text = reader["ItemID"].ToString();
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Button Load/Refresh the table
private void btn_loadTable_Click(object sender, EventArgs e)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select * from Inventory";
command.CommandText = query;
int itemCount = 0;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
itemCount++;
int quantity;
if (quantity > 15)
{
}
}
// Connect Inventory table to Grid
OleDbDataAdapter data = new OleDbDataAdapter(command);
DataTable inventory_table = new DataTable();
data.Fill(inventory_table);
dataGridView_inventory.DataSource = inventory_table;
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Button Update
private void btn_update_Click(object sender, EventArgs e)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "update Inventory set Quantity='" + text_quantity.Text + "' where ItemID=" + label_itemID.Text + "";
command.CommandText = query;
command.ExecuteNonQuery();
MessageBox.Show("Update Successful");
text_quantity.Text="";
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
}
}
And
According to your code, you should put a test in your combo_inventory_SelectedIndexChanged method, when you get the quantity for the selected item.
This would only popup a warning if the user happens to select the article which has a quantity less than 15.
Otherwise, if you mean that you'd like some automated way, then you'll need to execute a query for quantities less than 15, either at fixed intervals (timer), or at fixed locations in the application workflow.
As for the query, it would be very similar to what you already do to fill the ComboBox, but you'd simply add a WHERE clause to return items with qty < 15, and then you'd either popup a messagebox for each item, or popup a single messagebox showing the list of "faulty" items.
If you need a more specific answer, please update your question and specify which method (timer or workflow) you'd prefer implementing.
Cheers
EDIT:
Based on your comments, I put together this proof of concept code. Part of it is in the Program class which I assume you have as part of your Windows Forms application:
static class Program
{
public static event EventHandler CheckInventoryEvent;
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
System.Timers.Timer tmr = new System.Timers.Timer(300000); //Every 5 minutes
tmr.Elapsed += Tmr_Elapsed;
tmr.Start();
Application.Run(new MainForm());
tmr.Stop();
}
private static void Tmr_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
OnCheckInventoryEvent();
}
public static void OnCheckInventoryEvent()
{
if (CheckInventoryEvent != null)
CheckInventoryEvent(null, EventArgs.Empty);
}
}
The other part of the solution will be within any Form that you show to your user.
For example, let's say this is the Inventory form (could be any form in your application):
public InventoryForm()
{
InitializeComponent();
Program.CheckInventoryEvent += Program_CheckInventoryEvent;
}
private void Program_CheckInventoryEvent(object sender, EventArgs e)
{
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select Item, Quantity from Inventory WHERE Quantity < 15";
command.CommandText = query;
// Find low Inventory items and display in dialog
StringBuilder sb = new StringBuilder();
OleDbDataReader reader = command.Execute();
while (reader.Read())
{
sb.AppendLine(string.Format("{0} qty: {1}"), reader["Item"].ToString(), reader["Quantity"].ToString());
}
connection.Close();
MessageBox.Show(sb.ToString(), "Low Inventory Items");
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
This is crude, and only meant as a POC, so feel free to adapt.
The reason I have the timer in the Program class is so that it doesn't matter which windows/GUI your user is currently using, the timer will fire in any case.
A refinement would be to move the check and accompanying dialog to its own class so you don't actually repeat the same code in each Form.
I hope this puts you on the right track.
Cheers