CODEWITHSUNDEEP
c#mysqlwinformsvisual-studio
ClementB
ClementB

Reputation: 23

Comparing Windows Form Textbox input value to value in database C# SQL

I am looking to compare the input on a windows form textbox to a field in a database. The table is 'products' and the field is 'ReOrderlevel'. If the value entered into the text box is less than the ReOrderlevel, I need a message box to show 'Please Order Now'. I am completely new to C# and not sure if any of this will make sense but I will put my effort below in case anyone can work out what I am going on about! Thank you so much in advance !

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 MySql.Data.MySqlClient;
using System.Text.RegularExpressions;


namespace rescue_centre
{
public partial class FrmStock : Form
{
    public FrmStock()
    {
        InitializeComponent();
    }

    private void FrmStock_Load(object sender, EventArgs e)
    {

        refresh_data();
    }
    public void refresh_data()
    {
        string Query = "select * from stock";
        MySqlConnection mycon = new     MySqlConnection("datasource=localhost;username=root;password='';database=fsd;");            MySqlCommand cmd = new MySqlCommand(Query, mycon);
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        MySqlDataReader Mdr;

        adapter.SelectCommand = cmd;
        DataTable dTable1 = new DataTable();
        adapter.Fill(dTable1);
        DtgStock.DataSource = dTable1;

        mycon.Open();
        Query = "Select * from stock";
        cmd = new MySqlCommand(Query, mycon);
        Mdr = cmd.ExecuteReader();

        CmbPrd.Items.Clear();
        while (Mdr.Read())
        {
            CmbPrd.Items.Add(Mdr.GetString("ProdCode"));
        }
        mycon.Close();

        mycon.Open();
        Query = "Select * from stock";
        cmd = new MySqlCommand(Query, mycon);
        Mdr = cmd.ExecuteReader();

        CmbLoc.Items.Clear();
        while (Mdr.Read())
        {
            CmbLoc.Items.Add(Mdr.GetString("LocCode"));
        }
        mycon.Close();
    }
   private void QtyTxt_Validating(object sender, CancelEventArgs e)
    {
        string query = "Select ReOrderlevel from Product where (ProdCode)='" + CmbPrd.Text + "';";

        MySqlConnection mycon = new MySqlConnection("datasource=localhost;username=root;password='';database=fsd");
        MySqlCommand cmd = new MySqlCommand(query, mycon);

        if
            QtyTxt.Text<ReOrderLevel
            MessageBox.Show("Stock Low Please Order Now")

        mycon.Open();
        cmd.ExecuteNonQuery();
        mycon.Close();
        refresh_data();

Upvotes: 2

Views: 1453

Answers (1)

Jaime Macias
Jaime Macias

Reputation: 847

You need to execute the query before the if statement. I'm going to assume that ReOrderlevel is an integer and your query always returns one row or more.

MySqlCommand cmd = new MySqlCommand(query, mycon);
int ReOrderLevel = Convert.ToInt32(cmd.ExecuteScalar());

if(Convert.ToInt32(QtyTxt.Text) < ReOrderLevel)
{
    MessageBox.Show("Stock Low Please Order Now");
}

As a side note, you don't want to query the database 3 times for the Stock table. On the refresh_data method after using adapter.Fill(dTable1); you could load the combo boxes like this instead:

CmbPrd.Items.Clear();
CmbLoc.Items.Clear();
foreach (DataRow row in dTable1.Rows)
{
   CmbPrd.Items.Add((string)row["ProdCode"]);
   CmbLoc.Items.Add((string)row["LocCode"]);
}

And please use parameterized queries to avoid SQL injection. Code sample

string query = "Select ReOrderlevel from Product where (ProdCode)= @prodcode;";
cmd.Parameters.AddWithValue("@prodcode", CmbPrd.Text);

Upvotes: 4

Related Questions