How to display total quantity available in stock for a item on TextBox using SQL Server, C# and VB.Net in ASP.Net

Last Reply on Sep 01, 2017 06:59 AM By dharmendr

Posted on Sep 01, 2017 05:22 AM

I have a form called Customer Order, i want to be able to display the total quantity of a product and display it on textbox called quantity in stock. See html below

<div class="box col-md-6">
    <h3 style="margin-top: 4px">
        <asp:Label ID="Label2" runat="server" Text="" CssClass="glyphicon glyphicon-shopping-cart"></asp:Label>
        Customer Order
    </h3>
    <div class="box-inner homepage-box" style="height: auto">
        <div class="box-header well">
            <h2>
                <i class="glyphicon glyphicon-th"></i>Block Production
            </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="">
                                        <asp:DropDownList ID="ddlLocation" runat="server" CssClass="btn btn-default dropdown-toggle"
                                            Width="100%" Height="50px" Font-Bold="False" Font-Size="Larger">
                                            <asp:ListItem>Please Select Location </asp:ListItem>
                                            <asp:ListItem>Nkwere Branch </asp:ListItem>
                                            <asp:ListItem>Gabbson Onitsha  </asp:ListItem>
                                        </asp:DropDownList>
                                    </p>
                                </div>
                                <div class="clearfix">
                                </div>
                                <div id="" class="">
                                    <p class="">
                                        <asp:DropDownList ID="ddlInches" runat="server" CssClass="btn btn-default dropdown-toggle"
                                            Width="100%" Font-Bold="False" Font-Size="Larger">
                                            <asp:ListItem>Please Select Item</asp:ListItem>
                                        </asp:DropDownList>
                                    </p>
                                </div>
                                <div class="clearfix">
                                </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>Please Select Category </asp:ListItem>
                                        </asp:DropDownList>
                                    </p>
                                </div>
                                <div class="clearfix">
                                </div>
                                <p class="">
                                    <asp:TextBox ID="txtstock" runat="server" CssClass="form-control " placeholder="Quantity in store "
                                        ReadOnly="True"></asp:TextBox>
                                </p>
                                <div class="clearfix">
                                </div>
                                <p class="">
                                    <asp:TextBox ID="txtprice" runat="server" CssClass="form-control " placeholder="Selling 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="Reset"
                                        Height="50px" Width="100px" OnClick="btnReset2_Click" />
                                    <asp:Label ID="Label1" runat="server"></asp:Label>
                                </p>
                            </div>
                        </div>
                    </div>
                    <div class="">
                    </div>
                    <!--/#content.col-md-0-->
                    <div class="row" style="width: 100%">
                        <!--/span-->
                    </div>
                    <!--/span-->
                </div>
            </div>
        </div>
    </div>
</div>

Now the store total quantity will be displayed on textbox based on these Inches selected and Categories selected. The inches and Categories are stored in table  called Inches table and Category table.

Both will be displayed and bind on dropdown on Customer order table, when the user selects both the inches and categor it will show the total quantity of that inches and category selected.

Results 1 - 5 of 8 12
Posted on Sep 01, 2017 05:23 AM

Hi micah,

Please explain with proper example by providing some sample data.


Posted on Sep 01, 2017 05:45 AM

 Ok here is example below Table Inches

 

ID UserName Inches
----------------------------
1  dav44    1
------------------------------- 
2  dav44    2
------------------------------- 
3  dav44    3
------------------------------- 

Table Category

 

ID UserName Category
----------------------------
1  dav44    medium
------------------------------- 
2  dav44    small
------------------------------- 
3  dav44    larg
------------------------------- 

Production table

 

CREATE TABLE [dbo].[BlockProduction](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NULL,
	
	[Item] [nvarchar](50) NULL,
	[Inches] [nvarchar](50) NULL,
	[Category] [nvarchar](500) NULL,
	
	[Quantity] [float] NULL,
	[ProductionDate] [date] NULL,

so on order page the user must select the inches and category to show Quantity from Production table   ones the user selects his choice on category and inches dropdown it will show the quantity from production table on the textbox example below

 






<div class="box col-md-6">
             <h3 style="margin-top:4px">
                 <asp:Label ID="Label2" runat="server" Text="" CssClass="glyphicon glyphicon-shopping-cart"></asp:Label>
              Customer Order
           </h3>
                    <div class="box-inner homepage-box" style="height:auto">
                <div class="box-header well">
                    <h2><i class="glyphicon glyphicon-th"></i>Block Production </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="">

                                                    <asp:DropDownList ID="ddlInches" runat="server" CssClass="btn btn-default dropdown-toggle" Width="100%" Font-Bold="False" Font-Size="Larger">
                                                         <asp:ListItem>Please Select Item</asp:ListItem>
                                                
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                     <div class="clearfix"></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>Please Select Category </asp:ListItem>
                                               
                                                    </asp:DropDownList>
                                       
                                                   </p>
                                             </div>
                     <div class="clearfix"></div>
                                          
                                        
                    

                                          <p class="">

                                              <asp:TextBox ID="txtstock" runat="server" CssClass="form-control " placeholder="Quantity in store " ReadOnly="True"></asp:TextBox>
                                              
                                               </p>
                    
                     <div class="clearfix"></div>
                                          <p class="">

                                              <asp:TextBox ID="txtprice" runat="server" CssClass="form-control " placeholder="Selling 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="Reset" Height="50px" Width="100px" OnClick="btnReset2_Click"/>
                                                       <asp:Label ID="Label1" runat="server"></asp:Label>
                                                   </p>


 
                                           
 
              




                                 
            
                                        </div>


                


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

            

            


            
        </div>
  
    <!--/#content.col-md-0-->

   
        <div class="row" style="width: 100%">

            <!--/span-->

            
        </div>
        <!--/span-->
     </div>
                          
</div>

                    
                   
                </div>
            </div>



        
        </div>

Please is it clear to you now?


Posted on Sep 01, 2017 06:16 AM

Hi micah,

Refer the below sample query. You need to call the query in DropDownList selection to display the total quantity in TextBox for this use ExecuteScalar and passs the DropDownList SelectedText to SqlCommand Parameter. Refer the below article.

Using ADO.Net ExecuteScalar method in ASP.Net with examples in C# and VB.Net

SQL

DECLARE @Inches AS TABLE (ID INT,UserName VARCHAR(50),Inches INT)
INSERT INTO @Inches VALUES(1,'dav44',1)
INSERT INTO @Inches VALUES(2,'dav44',2)
INSERT INTO @Inches VALUES(3,'dav44',3)

DECLARE @Category AS TABLE (ID INT,UserName VARCHAR(50),Category VARCHAR(50))
INSERT INTO @Category VALUES(1,'dav44','medium')
INSERT INTO @Category VALUES(2,'dav44','small')
INSERT INTO @Category VALUES(3,'dav44','larg')

DECLARE @BlockProduction AS TABLE
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [NVARCHAR](50) NULL,     
    [Item] [NVARCHAR](50) NULL,
    [Inches] [NVARCHAR](50) NULL,
    [Category] [NVARCHAR](500) NULL,     
    [Quantity] [FLOAT] NULL,
    [ProductionDate] [DATE] NULL
)
INSERT INTO @BlockProduction VALUES('dav44','Item 1','1','medium',10,GETDATE())
INSERT INTO @BlockProduction VALUES('dav44','Item 1','1','small',20,GETDATE())
INSERT INTO @BlockProduction VALUES('dav44','Item 1','2','larg',30,GETDATE())
INSERT INTO @BlockProduction VALUES('dav44','Item 1','1','medium',40,GETDATE())
INSERT INTO @BlockProduction VALUES('dav44','Item 1','1','small',50,GETDATE())

DECLARE @Inch VARCHAR(20)
DECLARE @Cat VARCHAR(20)
SET @Inch = '1'
SET @Cat = 'small'
SELECT SUM(Quantity) 'total quantity'
FROM @BlockProduction 
WHERE (Inches = @Inch OR @Inch IS NULL) AND (Category = @Cat OR @Cat IS NULL)

Output

total quantity

      70


Posted on Sep 01, 2017 06:26 AM

on the example below the code am i suppose to creat it as procedure, i have to remove the insert because i have the datas in table as i showed you.

DECLARE @Inches AS TABLE (ID INT,UserName VARCHAR(50),Inches INT)

 
DECLARE @Category AS TABLE (ID INT,UserName VARCHAR(50),Category VARCHAR(50))

 
DECLARE @BlockProduction AS TABLE
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [NVARCHAR](50) NULL,    
    [Item] [NVARCHAR](50) NULL,
    [Inches] [NVARCHAR](50) NULL,
    [Category] [NVARCHAR](500) NULL,    
    [Quantity] [FLOAT] NULL,
    [ProductionDate] [DATE] NULL
)

 
DECLARE @Inch VARCHAR(20)
DECLARE @Cat VARCHAR(20)

SELECT SUM(Quantity) 'total quantity'
FROM @BlockProduction
WHERE (Inches = @Inch OR @Inch IS NULL) AND (Category = @Cat OR @Cat IS NULL)


Posted on Sep 01, 2017 06:43 AM

Below is the procedure you have to use.

CREATE PROCEDURE GetTotalQuantity
	@Inch VARCHAR(20) = NULL
	@Cat VARCHAR(20) = NULL
AS
BEGIN
	SELECT SUM(Quantity) 'total quantity'
	FROM BlockProduction
	WHERE (Inches = @Inch OR @Inch IS NULL) AND (Category = @Cat OR @Cat IS NULL)
END