Check if entered quantity is greater than available stock using C# and VB.Net in ASP.Net

micah
 
on Oct 31, 2021 10:52 PM
733 Views

Hi

after modifying the update code i noticed that the code doesn't check if the qty inserted is greater than the stock and if so this line of code supposed to fire to the user but what i see is that the clientscript that shows when the inserted qty is greater than the stock fires it still submits the update with minus  like -2

ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);

complete code

string model = ddlbrand.SelectedItem.Text.Trim();
 
// decimal unitPrice = Convert.ToDecimal(txtprice.Text.Trim());
int availableQuantity = Convert.ToInt32(txtstock.Text.Trim());
int quantity = Convert.ToInt32(txtqty.Text.Trim());
int inserted = 0;
 
//  int availableInventoryQty = AvailableInventoryQty();
if (txtqty.Text.Length > 0 && txtstock.Text.Length > 0)
{
 
    double stock = Convert.ToDouble(txtstock.Text);
    double customerqty = Convert.ToDouble(txtqty.Text);
    double tqty = stock - customerqty;
    double gqty = tqty;
    {
        {
            if (availableQuantity > 0 && quantity > 0)
            {
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = str;
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;
 
 
                        cmd.CommandText = "INSERT INTO Damaged_Table (Store,Item, Stock,Qty_Damaged) VALUES(@Store,@Item, @Stock,@Qty_Damaged)";
                        // cmd.Parameters.AddWithValue("@SellsPerson", HttpContext.Current.User.Identity.Name);
                        cmd.Parameters.AddWithValue("@Store", Department.SelectedItem.Text.Trim());
                        cmd.Parameters.AddWithValue("@Item", ddlbrand.SelectedItem.Text.Trim());
                        // cmd.Parameters.AddWithValue("@Item", ddlitem.SelectedItem.Text.Trim());
 
                        //   cmd.Parameters.AddWithValue("@Item", model);
                        // cmd.Parameters.AddWithValue("@Price", txtprice.Text.Trim());
                        cmd.Parameters.AddWithValue("@Stock", txtstock.Text.Trim());
 
                        cmd.Parameters.AddWithValue("@Qty_Damaged", txtqty.Text);
                        // cmd.Parameters.AddWithValue("@Sum", txttotalamount.Text.Trim());
                        con.Open();
                        inserted = cmd.ExecuteNonQuery();
 
                        // txtphone.Text = sdr["Phone"].ToString();
                        con.Close();
                        //BindGridreport();
                    }
                }
            }
            if (Convert.ToInt32(quantity) > 0)
            {
                if (Convert.ToInt32(quantity) > availableQuantity)
                {
                    ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
                }
                else
                {
                    if (inserted > 0)
                        ScriptManager.RegisterClientScriptBlock(btncustomer, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
                }
 
            }
            if (inserted > 0)
            {
                int updatedStock = availableQuantity - quantity;
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = str;
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;
                        cmd.CommandText = "UPDATE Store_Table SET Damages= " + customerqty + ", Qty_Remaining = " + updatedStock + " WHERE Item = '" + model + "'";
                        con.Open();
                        inserted = cmd.ExecuteNonQuery();
 
                        con.Close();
                    }
                }
            }
            // BindProductsSells();
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Nov 01, 2021 01:59 AM
on Nov 01, 2021 06:56 AM

Dear Micah,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<table>
    <tr>
        <td>Product Name:</td>
        <td><asp:TextBox runat="server" ID="txtName"></asp:TextBox></td>
        <td></td>
    </tr>
    <tr>
        <td>Quantity:</td>
        <td><asp:TextBox runat="server" ID="txtQty"></asp:TextBox></td>
        <td></td>
    </tr>
    <tr>
        <td><asp:Button Text="Submit" ID="btnSubmit" runat="server" OnClick="OnSubmit" /></td>
    </tr>
</table>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void OnSubmit(object sender, EventArgs e)
{
    int availableQuantity = 0;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT UnitsInStock FROM Products WHERE ProductName  = @name";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@name", txtName.Text);
            con.Open();
            availableQuantity = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
        }
        if (availableQuantity >= Convert.ToInt32(txtQty.Text))
        {
            ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('Data Submitted Successfully ... !!')", true);
        }
        else
        {
            ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
        }
    }
}

VB.Net

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Dim availableQuantity As Integer = 0
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim query As String = "SELECT UnitsInStock FROM Products WHERE ProductName  = @name"
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@name", txtName.Text)
            con.Open()
            availableQuantity = Convert.ToInt32(cmd.ExecuteScalar())
            con.Close()
        End Using

        If availableQuantity >= Convert.ToInt32(txtQty.Text) Then
            ClientScript.RegisterClientScriptBlock(Me.[GetType](), "", "alert('Data Submitted Successfully ... !!')", True)
        Else
            ClientScript.RegisterClientScriptBlock(Me.[GetType](), "", "alert('No of Quantity entered is not available in Stock')")
        End If
    End Using
End Sub

Screenshot

SQL

Output