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

Question

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();

Your queries are open to SQL injection, please use parametrized queries — Sybren
– Sybren, Commented Oct 28, 2016 at 22:35
Firstly, you should create a HelperClass to open DB and execute your queries. And where is your ReOrderLevel result to compare QtyTxt.Text<ReOrderLevel? — Neo
– Neo, Commented Oct 29, 2016 at 1:08

Jaime Macias · Accepted Answer · 2016-10-29 19:59:07Z

4

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

edited Oct 29, 2016 at 19:59

answered Oct 28, 2016 at 23:02

Jaime Macias

8476 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ClementB Over a year ago

HI, thanks so much for your help. Its now giving me an SQL error on this line: int ReOrderLevel = (int)cmd.ExecuteScalar(); Error: An exception of type 'System.NullReferenceException' occurred in MySql.Data.dll but was not handled in user code Additional information: Object reference not set to an instance of an object. - Any ideas what I have done wrong? Sorry I am very new to this

Jaime Macias Over a year ago

I have updated the answer, this line specifically int ReOrderLevel = Convert.ToInt32(cmd.ExecuteScalar()); you won't get that exception anymore, but your query is not returning anything try running it first in MySQL workbench.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related