locked
C# - Comparing of cell content clicked to all the content of the row RRS feed

  • Question

  • User-2085049302 posted

    hi.. i am an IT student..we have a system for thesis about an inventory system. My problem is about the expiration

    date of the products, i should be displaying all the products in a listbox where in if the user clicked a cell for example 'milk' , the system will search

    that column of item names in the Mysql database of all the name same with the one clicked/ search all milk with the same unit(ex: 500g), after scaning all the content,

    it will get the sum of all their quantity and display it on a textbox..

    ---------------for the expiry dates it will group all 'milk' with the same expiration dates and display it in a table and together with their quantity...

    i need the code logic and i would appreciate any help ....THANKS :)))))))) 

    Thursday, September 19, 2013 10:46 AM

Answers

  • User1143442848 posted

    Hi,

    Firstly, you can save all the records to the sql server database.

    Secondly, you can select the records that meet your requirement to display in the web form.

    Here is a code snippet for you to refer to:

    1, code in .aspx

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox_item" runat="server" OnSelectedIndexChanged="ListBox_item_SelectedIndexChanged" AutoPostBack="true"></asp:ListBox>
            <asp:TextBox ID="TextBox_item_name" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBox_unit" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBox_quantity" runat="server"></asp:TextBox>
            <asp:ListBox ID="ListBox_expiry_dates" runat="server"></asp:ListBox>
            <asp:GridView ID="GridView1" runat="server" ></asp:GridView>
        </div>
        </form>
    </body>
    </html>
    

    2. code in .cs

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DataSet ds = DataBindByDataSet();
                    int num = ds.Tables[0].Rows.Count;
                    this.GridView1.DataSource = ds;
                    this.GridView1.DataBind();
    
                    //listbox
                    DataTable dt = getItemName().Tables[0];
                    int count = dt.Rows.Count;
                    for(int i=0;i<count;i++)
                    {
                        this.ListBox_item.Items.Add(dt.Rows[i][0].ToString());
                    }
                 }
            }
            private DataSet DataBindByDataSet()
            {
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select * from t_items";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                return ds;
            }
    
            private DataSet getItemName()
            {
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select distinct Item_Name from t_items";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                return ds;
            }
    
            protected void ListBox_item_SelectedIndexChanged(object sender, EventArgs e)
            {
                this.TextBox_item_name.Text = this.ListBox_item.SelectedValue;
    
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select  Item_Name,[Transaction], unit, quantity from t_items where Item_Name='" + this.ListBox_item.SelectedValue + "'";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                DataTable dt = ds.Tables[0];
                int count = dt.Rows.Count;
                int quantitySum = 0;
                for (int i = 0; i < count; i++)
                {               
                    if (dt.Rows[i][1].ToString() == "add")
                    {
                        quantitySum += Convert.ToInt32(dt.Rows[i]["quantity"].ToString());
                    }
                    else if(dt.Rows[i][1].ToString() == "release")
                    {
                        quantitySum -= Convert.ToInt32(dt.Rows[i]["quantity"].ToString());
                    }               
                }
                this.TextBox_quantity.Text = quantitySum.ToString();
                this.TextBox_unit.Text = dt.Rows[0][2].ToString();         
               
            }
    

     For the listbox_expiry, it is recommended to use the GridView instead of listbox.

    And you can also query the records and bind them to GridView.

     

    Hope it can help you.

    Best Regards,

    Lisa Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 21, 2013 9:37 AM

All replies

  • User1080700311 posted

    I would suggest breaking the problem down. First worry about the backend. Create your database. Then play around in query analzyer to see what quries you will need to build to extract your data to be placed in the dialog listbox. After your database is correct then set your focus on the C# that will execute your sql. Either inline sql or you sql stored in a stored procedure. Here is some database sql which will build a table for you. I included an insert statement to insert data as need.

     

    GO

    /****** Object:  Table [dbo].[Inventory]    Script Date: 09/19/2013 19:55:11 ******/

    SET NSI_NULLS ON
    GO
    SET
    QUOTED_IDENTIFIER ON
    GO
    CREATE

    TABLE [dbo].[Inventory](

    [ProductNumber] [int]

    NULL,
    [ProductName] [nvarchar]

    (50) NULL,

    [Grams] [int]

    NULL,

    [ExperationDate] [datetime]

    NULL

    )

    ON [PRIMARY]

    Here

    is the Sql to insert the code into your database:

    insert into Inventory (ProductNumber,ProductName,Grams,ExperationDate) VALUES (1,'Milk',300,'03/02/2014')

     

    Thursday, September 19, 2013 10:03 PM
  • User-2085049302 posted

    ...i don't get it well :P   ....can you kindly make it simplier... like some example of codes to compare every data in the column of item... thanks :)

    Thursday, September 19, 2013 10:56 PM
  • User1143442848 posted

    Hi,

    According to your description, you want to compare the cell content to all the column content and then display them in the GridView.

    I suggest you follow the steps below:

    1,  Add a GridView control to the web form to display the records.

    2, In the button_click event, you can search it using sql query in the sql server database.

    3, Bind the result to the GridView.

    Here is an example for you to refer to and it works well in my lab machine.

    1, code in .aspx

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
         <asp:TextBox ID="txt_firstName" runat="server"></asp:TextBox>      
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click"/>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="ID">
                  <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID"  />
                    <asp:BoundField DataField="FirstName" HeaderText="FirstName"  />
                  <asp:BoundField DataField="LastName" HeaderText="LastName"  />
                  <asp:BoundField DataField="Address" HeaderText="Address"  />          
              </Columns>
            </asp:GridView>
        </div>
        </form>
    </body>
    </html>
    

    2, code in .cs

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    this.GridView1.DataSource = DataBindByDataSet();
                    this.GridView1.DataBind();
                }
            }
    
            private DataSet DataBindByDataSet()
            {
                string conStr = "Data Source=yourlocalmachine;Initial Catalog=testDB;Integrated Security=True";
                string sqlStr = "select ID, FirstName,LastName,Address from t_person";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
                con.Close();
                return ds;
            }
    
            private void BindGrid(string firstName)
            {
                DataTable dt = new DataTable();
    
                //Check if the string has the valid date format
                if (!String.IsNullOrEmpty(firstName) )
                {
                    SqlConnection connection = new SqlConnection("Data Source=youlocalmachine;Initial Catalog=testDB;Integrated Security=True");
                    try
                    {
                        connection.Open();
                        string sqlStatement = "select ID, FirstName,LastName,Address from t_person WHERE FirstName like '%'+@s_firstName+'%' ";
                        SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
                        sqlCmd.Parameters.AddWithValue("@s_firstName", firstName);
                        string s = sqlCmd.CommandText;
                        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                        sqlDa.Fill(dt);
    
                        if (dt.Rows.Count > 0)
                        {
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                        else
                        {
                            //Show no Records found
                            this.GridView1.DataSource = null;
                            GridView1.DataBind();
                            Response.Write("no records found");
                        }
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        string msg = "Fetch Error:";
                        msg += ex.Message;
                        throw new Exception(msg);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                BindGrid(this.txt_firstName.Text.Trim());
            }
    

    As an alternative, you can also search what you want in the GridView instead of sql server database.

    Here are some examples for you to refer to:

    # ASP.NET GridView with search option (SearchableGridView)

    http://www.codeproject.com/Articles/43018/ASP-NET-GridView-with-search-option-SearchableGrid

    # Display Large Amount of Data in GridView with Search Functionality

    http://www.codeproject.com/Articles/106678/Display-Large-Amount-of-Data-in-GridView-with-Sear

     

    If you need more assistance, please try to let me know.

    Best Regards,

    Lisa Zhang

    Friday, September 20, 2013 2:27 AM
  • User-2085049302 posted

    hi liza..thanks for the help in comparing cell content of the row... but its not answered all yet :D ..i will explain more clearly if u can help. ::))

    in a form(monitoring stocks)  there will be a

    listbox_item,

    textbox_item_name

    , txtbox_unit,

    txtbox_quantity,             and

    listbox_expiry_dates

    .................this is the content of the tbl_foodstocks :

              * ITEM_NAME            TRANSACTION         UNIT         QUANTITY            EXPIRY_DATE

                  milk(500g)                    add                   can                 7                     12/ 1/ 2014              (row1)

                    juice                          add                   box                 8                     4/ 3/ 2014                (row2)

                   milk(500g)                release                  can                 2                    12/ 1/2014                (row3)

                   milk(500g)                   add                   can                 8                    3/ 4/ 2015                (row4)

                   milk(1000g)                  add                   box                3                    5/ 1 /2016                (row5)

                   milk(500g)                   add                   can                5                     12/1/2014                 (row6)


             *****uppon loading the form:

                    - the listbox_item will display all the content of column ITEM_NAME without repeating the same items

                     | LISTBOX_ITEM |

                     |     milk (500g)  |

                     |       juice         |

            ***** when the listbox_item is clicked (for example milk(500g) is clicked):

                   - "milk (500g)" will be displayed in the textbox_item_name

                   - "can"  will be displayed in the txtbox_unit

                   - "18"  will be displayed in the txtbox_quantity

                         (****** in here      - it will search for all milk(500g) with the same unit  in the database (rows 1, 3, 4 & 6)

                                                   -it will sum up all their quantity with transaction = "add"  (it will sum up the quantity of rows 1 & 6)

                                                   - it will deduct the quantity with transaction = "release"  (deduct row 3)             )

                   - for listbox_expiry_date

                           ******* all milk(500g) with the same unit and expiry_date will be separated  (rows 1, 3 & 6)   (row4)

                           ******* it will sump up all thier quantity with transaction="add" each forevery different expiry dates

                            ****** and will deduct those with transaction "release" for every different expiry dates

                     ...........so listbox_expiry_date will display this:

                       LISTBOX_EXPIRY_DATE:

                     |  EXPIRY_DATE       |          QUANTITY    |

                         12/ 1/ 2014                          10

                          3/ 4/ 2015                           8


    .....can u kindly help me with the coding logic for these ... :))))))))...THANKS IN ADVANCE!!!!                    

    Friday, September 20, 2013 6:14 AM
  • User1143442848 posted

    Hi,

    Firstly, you can save all the records to the sql server database.

    Secondly, you can select the records that meet your requirement to display in the web form.

    Here is a code snippet for you to refer to:

    1, code in .aspx

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="ListBox_item" runat="server" OnSelectedIndexChanged="ListBox_item_SelectedIndexChanged" AutoPostBack="true"></asp:ListBox>
            <asp:TextBox ID="TextBox_item_name" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBox_unit" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBox_quantity" runat="server"></asp:TextBox>
            <asp:ListBox ID="ListBox_expiry_dates" runat="server"></asp:ListBox>
            <asp:GridView ID="GridView1" runat="server" ></asp:GridView>
        </div>
        </form>
    </body>
    </html>
    

    2. code in .cs

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DataSet ds = DataBindByDataSet();
                    int num = ds.Tables[0].Rows.Count;
                    this.GridView1.DataSource = ds;
                    this.GridView1.DataBind();
    
                    //listbox
                    DataTable dt = getItemName().Tables[0];
                    int count = dt.Rows.Count;
                    for(int i=0;i<count;i++)
                    {
                        this.ListBox_item.Items.Add(dt.Rows[i][0].ToString());
                    }
                 }
            }
            private DataSet DataBindByDataSet()
            {
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select * from t_items";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                return ds;
            }
    
            private DataSet getItemName()
            {
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select distinct Item_Name from t_items";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                return ds;
            }
    
            protected void ListBox_item_SelectedIndexChanged(object sender, EventArgs e)
            {
                this.TextBox_item_name.Text = this.ListBox_item.SelectedValue;
    
                string conStr = ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString;
                string sqlStr = "select  Item_Name,[Transaction], unit, quantity from t_items where Item_Name='" + this.ListBox_item.SelectedValue + "'";
                DataSet ds = new DataSet();
                SqlConnection con = new SqlConnection(conStr);
    
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlStr, con);
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(ds);
    
                con.Close();
    
                DataTable dt = ds.Tables[0];
                int count = dt.Rows.Count;
                int quantitySum = 0;
                for (int i = 0; i < count; i++)
                {               
                    if (dt.Rows[i][1].ToString() == "add")
                    {
                        quantitySum += Convert.ToInt32(dt.Rows[i]["quantity"].ToString());
                    }
                    else if(dt.Rows[i][1].ToString() == "release")
                    {
                        quantitySum -= Convert.ToInt32(dt.Rows[i]["quantity"].ToString());
                    }               
                }
                this.TextBox_quantity.Text = quantitySum.ToString();
                this.TextBox_unit.Text = dt.Rows[0][2].ToString();         
               
            }
    

     For the listbox_expiry, it is recommended to use the GridView instead of listbox.

    And you can also query the records and bind them to GridView.

     

    Hope it can help you.

    Best Regards,

    Lisa Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 21, 2013 9:37 AM
  • User-2085049302 posted

    THANKs much Lisa!!!      

    more power

    :))))))))

    Saturday, September 21, 2013 10:27 AM