Using datareader to count Asp.net

Question

I'm checking if a barcode from a database table(using a select query) exists and insert the details into another database table else the barcode does not exist. The inserts fine but I would like to count the number of barcodes entered. Say within a session an user enters 5 barcodes then the total count is 5 but my code keeps returning 1 and not incrementing.

protected void btnReturn_Click(object sender, EventArgs e)
{
    string barcode = txtBarcode.Text;
    string location = lblLocation.Text;
    string user = lblUsername.Text;
    string actType = lblAct.Text;
    string date = lblDate.Text;



    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TWCL_OPERATIONSConnectionString"].ToString());

    //commands identifying the stored procedure
    SqlCommand cmd = new SqlCommand("selectCrate", conn);

    SqlCommand cmd1 = new SqlCommand("CreateCrateBox", con);

    // execute the stored procedures
    cmd.CommandType = CommandType.StoredProcedure;
    cmd1.CommandType = CommandType.StoredProcedure;


    cmd.Parameters.Add(new SqlParameter("@crateno", barcode);


    conn.Open();


 using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows) {
                while (reader.Read())
                {

                    lblResult.Text = reader[0].ToString();
                    lblResult1.Text = reader[1].ToString();


                    cmd1.Parameters.Add("@crateno", SqlDbType.NVarChar).Value = barcode);
                    cmd1.Parameters.Add("@CurrentLocation", SqlDbType.NVarChar).Value = location;

                    cmd1.Parameters.Add("@Username", SqlDbType.NVarChar).Value = user;
                    cmd1.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;
                    cmd1.Parameters.Add("@status", SqlDbType.NVarChar).Value = actType;





                    counter = counter + 1;

                }

    reader.Close();
                cmd1.ExecuteNonQuery();

                txtCount.Text = counter.ToString();
                lblCount.Text = string.Format("Number of rows: {0}", counter);
            }
            else
            {

                lblError.Text = barcode + " does not exist!!";
            }


        }

it is when i'm inserting i would like to count. that didn't provide me with much assistance but thanks thou appreciate it — user9356915
– user9356915, Commented Mar 20, 2018 at 16:07
If its a small use. You can add a count in your query and just read that out. — H.Mikhaeljan
– H.Mikhaeljan, Commented Mar 21, 2018 at 7:24
insert into CrateHistory values(@crateno,@crateLocation,@username, @activityType) that's my query how do add the count to that query — user9356915
– user9356915, Commented Mar 21, 2018 at 14:51

John Paul Smithdeal · Accepted Answer · 2018-03-22 15:26:20Z

0

You can store the number in session then do something with it when the session ends (store it in a database or whatever you want). Declare a session variable:

Session[“NumInserts”] = 0;

Then update it with each insert:

Session[“NumInserts”] = (int) Session[“NumInserts”] + 1;

That variable will be maintained as long as the session exists. Also, make sure you only declare the session variable once and don’t ever allow it to reset during the session’s life cycle. Otherwise, it will go back to 0 and give you inaccurate results.

answered Mar 22, 2018 at 15:26

John Paul Smithdeal

469 bronze badges

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

Comments

H.Mikhaeljan · Accepted Answer · 2018-03-22 15:14:18Z

i rechecked to make sure and i misunderstood your question which led to some confusion.

And to answer your question i don't think there is any easy solution to update realtime the numbers. Any solution of i can think of is websocket connection which I personally have no knowledge of inside webforms(Don't even know if its possible).

I formatted your code. This should give you the total rows back in one go(no realtime update on screen).

protected void btnReturn_Click(object sender, EventArgs e)
{
    int counter = 0;
    string barcode = txtBarcode.Text;
    string location = lblLocation.Text;
    string user = lblUsername.Text;
    string actType = lblAct.Text;
    string date = lblDate.Text;

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TWCL_OPERATIONSConnectionString"].ToString()))
    {
        con.Open();
        //commands identifying the stored procedure
        using (SqlCommand cmd = new SqlCommand("selectCrate", con))
        {
            using (SqlCommand cmd1 = new SqlCommand("CreateCrateBox", con))
            {
                // execute the stored procedures
                cmd.CommandType = CommandType.StoredProcedure;
                cmd1.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@crateno", barcode));

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            lblResult.Text = reader[0].ToString();
                            lblResult1.Text = reader[1].ToString();

                            cmd1.Parameters.Add("@crateno", SqlDbType.NVarChar).Value = barcode;
                            cmd1.Parameters.Add("@CurrentLocation", SqlDbType.NVarChar).Value = location;
                            cmd1.Parameters.Add("@Username", SqlDbType.NVarChar).Value = user;
                            cmd1.Parameters.Add("@Date", SqlDbType.DateTime).Value = date;
                            cmd1.Parameters.Add("@status", SqlDbType.NVarChar).Value = actType;
                            counter++;
                        }
                        cmd1.ExecuteNonQuery();

                        txtCount.Text = counter.ToString();
                        lblCount.Text = string.Format("Number of rows: {0}", counter);
                    }
                    else
                    {
                        lblError.Text = barcode + " does not exist!!";
                    }
                }
            }
        }
    }
}

Collectives™ on Stack Overflow

Using datareader to count Asp.net

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related