How to sum number from database using DataReader in ASP.NET Web Form

Tri Setia

5y1.4k.NET

Hi Guys,, I'm try to sum number from database using DataReader and then the value will showing to textbox. If from database the value row is zero will retrun value 1 in textbox, If from database the value row is 1 will retrun value 2 and so on in textbox by automatic read exist value from database. How to solve this problem. Anny help could be appriciate.

this is is the code bihind

private void CariCicilanKe()
{
    using (SqlConnection con = new SqlConnection(koneksi))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select Count(*) from tbl_pembayaran where id_pinjaman = '" + txtid_pinjaman.Text.Trim() + "'";
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                rdr.Read();
                if(rdr.HasRows)
                {
		    //If from database the value row is zero will retrun value 1 in textbox
                    txtcicilan_ke.Text = Convert.ToString(1);
                }
                else
                {
		    //If from database the value row is 1will retrun value 2 and so on in textbox by automatic read exist value from database
		    //How to solve this problem, this code not working.
                    txtcicilan_ke.Text = rdr["cicilan_ke"].ToString() + 1;
                }
            }
            cmd.Connection.Close();
            con.Close();
        }
    }
}

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tri Setia5y ago
Problem Solved
private void CariCicilanKe()
    {
        using (SqlConnection con = new SqlConnection(koneksi))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select Count(*) from tbl_pembayaran where id_pinjaman = '" + txtid_pinjaman.Text.Trim() + "'";
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        if (!rdr.HasRows)
                        {
                            int CountRow1 = Convert.ToInt32(rdr[0].ToString());
                            int Add1 = 0;
                            int hasil1 = CountRow1 + Add1;
                            txtcicilan_ke.Text = Convert.ToString(hasil1);
                        }
                        else if (rdr.HasRows)
                        {
                            int CountRow2 = Convert.ToInt32(rdr[0].ToString());
                            int Add2 = 1;
                            int hasil2 = CountRow2 + Add2;
                            txtcicilan_ke.Text = Convert.ToString(hasil2);
                        }
                    }
                }
            }
        }
    }
+1
Tri Setia5y ago
@Sachin Singh
yes sir thats right..
I'm has been try the code sir, but first time code run directly show value 2 in textbox. It should show value 1 in textbox when value on row 0.
+1
Sachin Singh5y ago
so if number of rows=1000, it should display 1001 then
private void CariCicilanKe()
{
    using (SqlConnection con = new SqlConnection(koneksi))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select Count(*) from tbl_pembayaran where id_pinjaman = '" + txtid_pinjaman.Text.Trim() + "'";
         object obj= command.ExecuteScalar()
if (obj!= null)
{
txtcicilan_ke.Text = (Convert.ToInt32(obj)+1).ToString();;
}else
{
txtcicilan_ke.Text ="1";
}

        }
    }
}
Note:- when you are using the using statement then there is no need to close the connection/command explicitely.
+2

Tri Setia5y ago

@Nitin Sontakke

I has been try the code sir, but directly retruning value 2 in textbox when code running.

when I'm entry data 0 on field cicilan_ke it should show value is 1 on textbox and then save the data to database. after that code read again the value on field cicilan_ke and will show 2 on textbox.

CREATE TABLE [dbo].[tbl_pembayaran](  
    [id_pembayaran] [varchar](50) NOT NULL,  
    [tanggal_pembayaran] [datetime] NULL,  
    [id_pinjaman] [varchar](50) NULL,  
    [cicilan_ke] [int] NULL,  
    [denda] [numeric](18, 0) NULL,  
    [jumlah_pembayaran] [numeric](18, 0) NULL,  
    [keterangan] [text] NULL,  
    [id_user] [varchar](50) NULL,  
    [id_perusahaan] [varchar](50) NULL,  
 CONSTRAINT [PK_tbl_pembayaran] PRIMARY KEY CLUSTERED

private void CariCicilanKe()  
{  
    using (SqlConnection con = new SqlConnection(koneksi))  
    {  
        using (SqlCommand cmd = new SqlCommand())  
        {  
            con.Open();  
            cmd.Connection = con;  
            cmd.CommandType = CommandType.Text;  
            cmd.CommandText = "Select Count(*) from tbl_pembayaran where id_pinjaman = '" + txtid_pinjaman.Text.Trim() + "'";
            Object _Object = cmd.ExecuteScalar();  
            Int32 value = Int32.Parse(_Object.ToString());  
                  
            int hasil = value + 1;  
            txtcicilan_ke.Text = Convert.ToString(hasil);  
            cmd.Connection.Close();  
            con.Close();  
        }  
    }  
}

Tri Setia5y ago
@Jignesh Kumar

exactly is return value of rows sir,, I want the textbox fill automatic number, with this condition If from database the value row is zero will retrun value 1 in textbox and If from database the value row is 1 will return value 2 and so on in textbox by automatic read exist value from database.
+1
Nitin Sontakke5y ago
Furthermore, you don't quite need DataReader if you are receiving only single value. Use ExecuteScalar instead, as demonstrated below:
private void CariCicilanKe()
{
    using (SqlConnection con = new SqlConnection(koneksi))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select Count(1) from tbl_pembayaran where id_pinjaman = '" + txtid_pinjaman.Text.Trim() + "'";
            Object o = cmd.ExecuteScalar();
            Int32 value = Int32.Parse(o.ToString())
            txtcicilan_ke.Text = value + 1;
        }
        con.Close();
    }
}
+1
Jignesh Kumar5y ago
Hi,

Do you want to do sum of your table column value. You are using count which will return number of rows in table not sum of column. If you want to do sum then please use below link as refernece and change your query,

https://stackoverflow.com/questions/20999601/sqldatareader-and-sqlcommand-using-sum
+2