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!!";
            }


        }

Possible duplicate of [sqldatareader row count](https://stackoverflow.com/questions/5502863/sqldatareader-row-count) — H.Mikhaeljan, Mar 20 '18 at 15:46
it is when i'm inserting i would like to count. that didn't provide me with much assistance but thanks thou appreciate it — , Mar 20 '18 at 16:07
If its a small use. You can add a count in your query and just read that out. — H.Mikhaeljan, Mar 21 '18 at 07:24
insert into CrateHistory values(@crateno,@crateLocation,@username, @activityType) that's my query how do add the count to that query — , Mar 21 '18 at 14:51
because I had use the value from ExecuteNonQuery() and it still give a count of 1 — , Mar 21 '18 at 14:54
"SELECT count(*) as amount FROM TABLENAME". read out amount > this wil be the amount of rows it will iterate through — H.Mikhaeljan, Mar 21 '18 at 14:56
but it is not increment now; so how would this allow increment — , Mar 21 '18 at 14:58
are you visualizing the increments? It will give you straight the result of how many rows you have that would normally be the result of the increments. so instead it will just give you straight the 5 instead of doing 5 loops and increasing it. — H.Mikhaeljan, Mar 21 '18 at 15:01
but i would like to do 5 loops and increasing it so the user see how many entered; it's not possible to do the increment like that? — , Mar 21 '18 at 15:08
Normally the user will only receive the total count at the end. As i know you can't update the numbers continuously from the back end in webforms. It goes through the page life cycle and then returns to the client. — H.Mikhaeljan, Mar 21 '18 at 15:26
But if I use a count wouldn't that count all the items inserted and not what was inserted say in that session. or maybe i'm not understanding clearly. based on my code can show me the changes i would make — , Mar 21 '18 at 15:36

score 0 · Answer 1 · answered Mar 22 '18 at 15:14

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!!";
                    }
                }
            }
        }
    }
}

score 0 · Accepted Answer · answered Mar 22 '18 at 15:26

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.

Using datareader to count Asp.net

2 Answers2