Update Stock when new order insert into Database in SQL Server

Last Reply on Sep 11, 2017 11:47 PM By dharmendr

Posted on Sep 11, 2017 12:47 AM

Am trying to u submit customer orders into order table, on submiting the order record the quantity ordered by customer will be minused from quantity of stock. But this update procedure is not working

 

<asp:Content ID="Content1" runat="server" contentplaceholderid="ContentPlaceHolder4">
    <div class="row">
        <div class="">
            <div class="box col-md-12">
                <div class="pull-right">
                 <div class="btn-group open" >
                    <button class="btn btn-default btn-lg">MOLD CATEGORY</button>
                    <button class="btn dropdown-toggle btn-default btn-lg" data-toggle="dropdown"><span class="caret"></span></button>
                    <ul class="dropdown-menu" style="width:100%">
                        <li><a href="#"><i class="glyphicon glyphicon-user"></i> Register Customer</a></li>
                        <li><a href="#"><i class="glyphicon glyphicon-tag"></i> 5 Inches Solid MM</a></li>
                         <li><a href="#"><i class="glyphicon glyphicon-tag"></i> 5 Inches Solid HM</a></li>
                         <li><a href="#"><i class="glyphicon glyphicon-tag"></i> 6 Inches Solid MM</a></li>
                         <li><a href="#"><i class="glyphicon glyphicon-tag"></i> 5 Inches Solid MM</a></li>
                         <li><a href="#"><i class="glyphicon glyphicon-tag"></i> 5 Inches Solid MM</a></li>
                        <li><a href="#"><i class="glyphicon glyphicon-download-alt"></i> Something else here</a></li>
                        <li class="divider"></li>
                        <li><a href="#"><i class="glyphicon glyphicon-tint"></i> Separated link</a></li>
                    </ul>
                </div>
            </div>
                 <div class="clearfix"></div>
            <hr />
               <h3 style="margin-top:4px">
                 <asp:Label ID="Label2" runat="server" Text="" CssClass="glyphicon glyphicon-shopping-cart"></asp:Label>
               5 Inches Solid Machine Mold Order
           </h3>
                </div>
           
             <div class="clearfix"></div>
        
           <div class="col-md-6">

              <div class="clearfix"></div>
            <br />
           

            <div id="" class=" ">
                                              

                                           
                <div class="clearfix"></div>
               
                                             </div>
              
                    
                
             

            <div class="clearfix"></div>
         
                    <div class="box-inner homepage-box" style="height:auto">
                <div class="box-header well">
                    <h2><i class="glyphicon glyphicon-th"></i>Customer Order Form </h2>
                    <div class="box-icon">
                        <a href="#" class="btn btn-setting btn-round btn-default"><i
                            class="glyphicon glyphicon-cog"></i></a><a href="#" class="btn btn-minimize btn-round btn-default"><i
                            class="glyphicon glyphicon-chevron-up"></i></a><a href="#" class="btn btn-close btn-round btn-default"><i
                            class="glyphicon glyphicon-remove"></i></a>
                    </div>
                </div>
                <div class="box-content" >
                  
                            
                            <div>
                              
                         
                              <div id="" class="">
                   
        <div class=" ">
            
            <div class="">
                <div class=" ">
                                       <div class="clearfix"></div>
                                         <div id="" class="">
                                             <p class="input-group">
                       <asp:TextBox ID="txtname" runat="server"   placeholder="Customer Name "  class="form-control" ></asp:TextBox>
                        
                        <span class="input-group-btn">
                            <asp:Button ID="LoginButton" runat="server" Text="Search" class="btn btn-default" type="button"  Font-Bold="True"  />
                           
                                <span class="glyphicon glyphicon-search"></span>

                        </span>
                                               
                    </p>
                    
                   
                     
                    
                   
                    
                   
                     
                     <div class="clearfix"></div>
                                             <p class="">

                                                    <asp:DropDownList ID="ddlLocation" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                         <asp:ListItem>Please Select Location </asp:ListItem>
                                               <asp:ListItem>Nkwere Branch </asp:ListItem>
                                             
                                               
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>  
                  <div class="clearfix"></div>
                                        
                  
                                        
                       <div id="" class="">
                                              

                                             <p class="">

                                                    <asp:DropDownList ID="ddlItems" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                           <asp:ListItem Text="Select Item" Value="Select Item" />
                                                         <asp:ListItem Text="Block" Value="Block" />
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                    
                                        
                     <div id="" class="">
                                              

                                             <p class="">
                                                 
                                                    <asp:DropDownList ID="ddlCategory" runat="server"   CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                          <asp:ListItem Text="Select" Value="Select" />
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                     <div class="clearfix"></div>

                      

                  
                                            <div id="" class="">
                                              

                                             <p class="">

                                                    <asp:DropDownList ID="ddlpaymentmethod" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                         <asp:ListItem>Select Payment Method</asp:ListItem>
                                                         <asp:ListItem>Cash</asp:ListItem>
                                                         <asp:ListItem>Bank Payment</asp:ListItem>

                                                
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                     <div class="clearfix"></div>
                                            <div id="" class="">
                                              

                                             <p class="">

                                                    <asp:DropDownList ID="ddlorderstatus" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                         <asp:ListItem>Select Order Status</asp:ListItem>
                                                         <asp:ListItem>Supplied</asp:ListItem>
                                                         <asp:ListItem>Pending </asp:ListItem>

                                                
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                                       

                     
                  
                                         
                     <div class="clearfix"></div>
                                          <p class="">

                                              <asp:TextBox ID="txtcustomerqty" runat="server" CssClass="form-control " placeholder="Customer Quantity "></asp:TextBox>
                                              
                                               </p>
                     <div class="clearfix"></div>
                                          <p class="">

                                              <asp:TextBox ID="txtdiscount" runat="server" CssClass="form-control " placeholder="Discount "></asp:TextBox>
                                              
                                               </p>
                     <div class="clearfix"></div>
                                          <p class="">

                                              <asp:TextBox ID="txtunitprice" runat="server" CssClass="form-control " placeholder="Unit Price "></asp:TextBox>
                                              
                                               </p>
                    
                   
                     <div class="clearfix"></div>

                                      

                    




                                                   <p class=" pull-right">
                                                       <asp:LinkButton ID="btnSave" runat="server" CssClass="btn btn-info btn-circle" Text="" Height="50px" Width="100px" CausesValidation="true" OnClick="btnSave_Click"> <asp:Label ID="Label5" runat="server" Text="" CssClass=" glyphicon glyphicon-cloud-upload" Font-Size="Large"></asp:Label></asp:LinkButton>
                                                       
                                                        <asp:Button ID="btnReset2" runat="server" CssClass="btn btn-default btn-circle" Text="Edit Record" Height="50px" Width="100px" OnClick="btnReset2_Click" Font-Bold="True"/>
                                                       <asp:Label ID="Label1" runat="server"></asp:Label>
                                                   </p>


 
                                           
 
              




                                 
            
                                        </div>


                


            </div>
            
            
        </div>
        <div class="" >

            

            


--------------------------------

 

protected void btnSave_Click(object sender, EventArgs e)
    {
        lblMessage2.Visible = false;

        if (txtname.Text == "")
        {
            //   MessageBox1.ShowError("Enter Date Please");
            ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Enter Customer Name ... !!')</script>", false);
        }
        else
        {
            lblMessage2.Text = "";


            if (ddlLocation.SelectedIndex < 1)
            {
                ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Select Location ... !!')</script>", false);
            }
            else
            {
                lblMessage2.Text = "";

                   if (ddlItems.SelectedIndex < 1)
                        {
                            ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Select Item ... !!')</script>", false);
                        }
                        else
                        {
                            lblMessage2.Text = "";



              

                   

                        if (ddlCategory.SelectedIndex < 1)
                        {
                            ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Select Block Category ... !!')</script>", false);
                        }
                        else
                        {
                            lblMessage2.Text = "";


                            if (ddlpaymentmethod.SelectedIndex < 1)
                            {
                                ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Select Payment Method  ... !!')</script>", false);
                            }
                            else
                            {
                                lblMessage2.Text = "";

                                if (ddlorderstatus.SelectedIndex < 1)
                                {
                                    ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Select Order Status  ... !!')</script>", false);
                                }
                                else
                                {
                                    lblMessage2.Text = "";
                                    if (txtcustomerqty.Text == "")
                                    {
                                        //   MessageBox1.ShowError("Enter Date Please");
                                        ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Enter Customer Quantity ... !!')</script>", false);
                                    }
                                    else
                                    {
                                        lblMessage2.Text = "";
                                        if (txtunitprice.Text == "")
                                        {
                                            //   MessageBox1.ShowError("Enter Date Please");
                                            ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Enter Selling Price ... !!')</script>", false);
                                        }
                                        else
                                        {
                                            lblMessage2.Text = "";

                                            if (txtunitprice.Text.Length > 0 && txtdiscount.Text.Length > 0)
                                            {

                                                double unitprice = Convert.ToDouble(txtunitprice.Text);
                                                double discount = Convert.ToDouble(txtdiscount.Text);
                                                double sum = unitprice - discount;
                                                double sumb = sum;
                                               
                                                    //if (txtunitprice.Text.Length > 0 && txtquantity.Text.Length > 0)
                                                    {
                                                        //  int availableInventoryQty = AvailableInventoryQty();
                                                        //  if (availableInventoryQty >= Convert.ToDouble(txtQuantityByCustomer.Text))
                                                        {

                                                            string Currency = ("N");
                                                            string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
                                                            using (SqlConnection con = new SqlConnection(constr))
                                                            {
                                                                using (SqlCommand cmd = new SqlCommand("InsertFiveInchesMMORDERNkwereSore"))
                                                                {
                                                                    cmd.CommandType = CommandType.StoredProcedure;
                                                                    cmd.Parameters.AddWithValue("@UserName", Session["userName"]);
                                                                    cmd.Parameters.AddWithValue("@Name", txtname.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@Location", ddlLocation.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@Item", ddlItems.Text.Trim());
                                                                  //  cmd.Parameters.AddWithValue("@Inches", ddlInches.Text.Trim());     //Name of rep

                                                                    // cmd.Parameters.AddWithValue("@MouldType", ddlmouldcategory.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@Category", ddlCategory.Text.Trim());

                                                                    cmd.Parameters.AddWithValue("@PaymentMethod", ddlpaymentmethod.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@OrderStatus", ddlorderstatus.Text.Trim());
                                                                    //cmd.Parameters.AddWithValue("@Stock", suma);       //Address
                                                                    cmd.Parameters.AddWithValue("@QuantityOrdered", txtcustomerqty.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@Discount", txtdiscount.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@UnitPrice", txtunitprice.Text.Trim());
                                                                    cmd.Parameters.AddWithValue("@OrderDate", DateTime.Today);
                                                                    //  cmd.Parameters.AddWithValue("@Location", ddlLocation.Text.Trim());
                                                                    cmd.Connection = con;
                                                                    con.Open();
                                                                    cmd.ExecuteNonQuery();
                                                                    con.Close();
                                                                    string message = string.Empty;
                                                                    {
                                                                        lblMessage2.Visible = true;
                                                                        lblMessage2.Text = "Data Submitted Successfully";
                                                                    }
                                                                    ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Data Submitted Successfully ... !!')</script>", false);
                                                                }
                                                            }

                                                        }
                                                        //   else

                                                        string a = "33a";
                                                        if (a == "a")
                                                        {

                                                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "btnModalPopup();", true);
                                                        }
                                                        else if (a != "a")
                                                        {

                                                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "btnModalPopup();", true);
                                                        }
                                                        // {
                                                        // ScriptManager.RegisterClientScriptBlock(btnSave, this.GetType(), "alert", "<script>alert('Please quantity required is more than the stock, Stock is 1 or less than 1, update stock ... !!')</script>", false);

                                                        // lbl.Text = "Data Submitted Sucessfully";
                                                        // btnSave.Show();
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }

                            }
                        }
                    }
                }
            
        }

    

-------------------------------------------

 

ALTER PROCEDURE [dbo].[InsertFiveInchesMMORDERNkwereSore]
    @UserName nvarchar (100),
    @Name nvarchar (100),
    @Location nvarchar (50),
    @Item nvarchar (100),
  
    @Category nvarchar (500),
    @PaymentMethod  nvarchar (500),
    @OrderStatus nvarchar(500),
   
    @QuantityOrdered float,
    @Discount money,
    @UnitPrice money,
    @OrderDate date
AS

            -- Insert 
            INSERT INTO [AllSales]
                        ([UserName]
                            ,[Name]
                            ,[Location]
                            ,[Item]
                            
                            ,[Category]
                            ,[PaymentMethod]
                            ,[OrderStatus]
                            
                            ,[QuantityOrdered]
                            ,[Discount]
                            ,[UnitPrice]
                            ,[OrderDate])
                VALUES
                       (
                            @UserName,
                            @Name,
                            @Location,
                            @Item,
                            
                            @Category,
                            @PaymentMethod,
                            @OrderStatus,
                            
                            @QuantityOrdered,
                            @Discount,
                            @UnitPrice,
                            @OrderDate
                        )
   
UPDATE AllStore SET [Quantity] = [Quantity]- @QuantityOrdered WHERE [Location]= @Location AND [Item] = @Item  AND [Category] =@Category AND UserName=@UserName
                

    SELECT SCOPE_IDENTITY() AS UserID

 

Posted on Sep 11, 2017 04:12 AM Modified on on Sep 11, 2017 05:55 AM

Hi micah,

Refer the below sample query.

SQL

CREATE TABLE StockAvailable(ID INT,UserName VARCHAR(100),Category NVARCHAR(500),Item NVARCHAR(100),Quantity FLOAT,Location NVARCHAR(50))
GO
INSERT INTO StockAvailable VALUES(1,'test','5inches-Machin mold-block','Chai',30,'south africa')
INSERT INTO StockAvailable VALUES(2,'test1','6inches-Hand mold-Interlock','Bread',70,'south africa')
INSERT INTO StockAvailable VALUES(3,'test','9inches-Hollow-block','Suggar',120,'south africa')
GO
CREATE TABLE AllSales
(
    UserID INT IDENTITY
    ,UserName NVARCHAR(100) PRIMARY KEY NOT NULL
    ,Name NVARCHAR(100)
    ,Location NVARCHAR(50)
    ,Item NVARCHAR(100)
    ,Category NVARCHAR(500)
    ,PaymentMethod NVARCHAR(500)
    ,OrderStatus NVARCHAR(500)
    ,QuantityOrdered FLOAT
    ,Discount MONEY
    ,UnitPrice MONEY
    ,OrderDate DATETIME
)
GO
CREATE PROCEDURE dbo.InsertFiveInchesMMORDERNkwereSore
    @UserName NVARCHAR (100),
    @Name NVARCHAR (100),
    @Location NVARCHAR (50),
    @Item NVARCHAR (100), 
    @Category NVARCHAR (500),
    @PaymentMethod  NVARCHAR (500),
    @OrderStatus NVARCHAR(500),
    @QuantityOrdered FLOAT,
    @Discount MONEY,
    @UnitPrice MONEY,
    @OrderDate DATETIME
AS
BEGIN
        -- Insert Sales
        INSERT INTO AllSales
                    (
                        UserName
                        ,Name
                        ,Location
                        ,Item
                        ,Category
                        ,PaymentMethod
                        ,OrderStatus
                        ,QuantityOrdered
                        ,Discount
                        ,UnitPrice
                        ,OrderDate
                    )
            VALUES
                    (
                        @UserName
                        ,@Name
                        ,@Location
                        ,@Item
                        ,@Category
                        ,@PaymentMethod
                        ,@OrderStatus
                        ,@QuantityOrdered
                        ,@Discount
                        ,@UnitPrice
                        ,@OrderDate
                    )
       
        -- Update Stock         
        UPDATE StockAvailable
        SET Quantity = Quantity - @QuantityOrdered 
		WHERE Location = @Location 
		AND Item = @Item 
		AND Category = @Category 
		AND UserName = @UserName
END
GO
SELECT * FROM StockAvailable
SELECT * FROM AllSales
EXEC dbo.InsertFiveInchesMMORDERNkwereSore 'test','Test','Test','Chai','5inches-Machin mold-block','NEFT','Ordered',10,10,50,'2017-09-11 15:21:06.317'
SELECT * FROM StockAvailable
SELECT * FROM AllSales

Screenshot


Posted on Sep 11, 2017 11:47 PM

Refer the below modified query.

ALTER PROCEDURE [dbo].[InsertFiveInchesMMORDERNkwereSore]
    @UserName nvarchar (100),
    @Name nvarchar (100),
    @Location nvarchar (50),
    @Item nvarchar (100),   
    @Category nvarchar (500),
    @PaymentMethod  nvarchar (500),
    @OrderStatus nvarchar(500),    
    @QuantityOrdered float,
    @Discount money,
    @UnitPrice money,
    @OrderDate date
AS
BEGIN
    -- Insert
    INSERT INTO [AllSales](
                [UserName]
                ,[Name]
                ,[Location]
                ,[Item]                         
                ,[Category]
                ,[PaymentMethod]
                ,[OrderStatus]                         
                ,[QuantityOrdered]
                ,[Discount]
                ,[UnitPrice]
                ,[OrderDate])
        VALUES(
                @UserName,
                @Name,
                @Location,
                @Item,                         
                @Category,
                @PaymentMethod,
                @OrderStatus,                         
                @QuantityOrdered,
                @Discount,
                @UnitPrice,
                @OrderDate)
        -- Update Stock       
        UPDATE AllStore
        SET Quantity = Quantity - @QuantityOrdered
        WHERE Location = @Location
        AND Item = @Item
        AND Category = @Category
        AND UserName = @UserName

		SELECT SCOPE_IDENTITY() AS UserID
END

And while executing the procedure please pass required parameters to the procedure.

SELECT * FROM AllStore
SELECT * FROM AllSales
EXEC dbo.InsertFiveInchesMMORDERNkwereSore 'test','Test','Test','Chai','5inches-Machin mold-block','NEFT','Ordered',10,10,50,'2017-09-11 15:21:06.317'
SELECT * FROM AllStore
SELECT * FROM AllSales