How to count records loaded by data reader

Question

Good day, I am currently trying to count all books that is currently issued and not returned yet to a student and view it to a datagridview

I have tried

public void LoadRecords()
    {
        frmAddEditStudent frm = new frmAddEditStudent(this);
        int i = 0;
        gunaDataGridView1.Rows.Clear();
        cn.Open();
        cm = new SqlCommand("SELECT * FROM tblStudent WHERE lastName LIKE '" + txtSearch.Text + "%'", cn);
        SqlCommand booksOnHand = new SqlCommand("SELECT COUNT(*) FROM tblBorrowedBook WHERE status = 'Not Retuned' AND studentID = '" + frm.txtStudNo.Text + "'", cn);
        dr = cm.ExecuteReader();
        while (dr.Read())
        {
            i += 1;
            gunaDataGridView1.Rows.Add(i, dr["studentID"].ToString(), dr["studentNum"].ToString(), dr["lastName"].ToString(), dr["firstName"].ToString(), dr["course"].ToString(), dr["year"].ToString(), dr["gender"].ToString(), dr["contact"].ToString(), dr["email"].ToString(), dr["address"].ToString(), dr["image"], booksOnHand);
        }
        dr.Close();
        cn.Close();
    }

And the output I can't seem to know how to do it in the right way.

Lear to use parameterized queries. Your program is open to SQL injection. — sticky bit, Mar 26 '20 at 02:09
Also research DataBinding - converting data to string and poking it into a control is a tedious and inefficient way to display DB data. But parameterized queries first because that has not been the way to do it in NET ever. — Ňɏssa Pøngjǣrdenlarp, Mar 26 '20 at 02:11
@stickybit I am using paramaters in inserting data to tha database, this is just for loading the data to datagridview, how to load data using parameterized queries? — kmandrew, Mar 26 '20 at 02:16
What's the issue you're having? Is the issue with `dr["image"]`? What is the type of that column in db? — CodingYoshi, Mar 26 '20 at 02:21
**Pro Tip** : Your question is not about counting books in a library, its about counting rows in a database conditionally. The reason i make this distinction is because there is no future user in the world that will have your issue with counting books in a library and has no use on stackoverflow in that language. You have a programming problem, not a library problem — TheGeneral, Mar 26 '20 at 02:21
@MichaelRandall yes, that was i mean. but I do learned how to count columns it just that i can't find a way to show it on a datagridview. I'm sorry i am new to programming — kmandrew, Mar 26 '20 at 02:25
@kemchan: You'll find a lot on that if you use a search engine with something like "C# parameterized queries". One example: https://stackoverflow.com/questions/25820944/how-do-i-re-write-a-sql-query-as-a-parameterized-query — sticky bit, Mar 26 '20 at 02:25
Unfortunately you have not posted WHAT exactly is wrong with your code and HOW you want output to look — T.S., Mar 26 '20 at 02:44
@T.S. i have mentioned that i do want to count all the books currently on hand of a student, and I want the output under the Possession column is the number of books currently on the hand of a student — kmandrew, Mar 26 '20 at 02:58

T.S. · Accepted Answer · 2020-03-26T03:57:51.583

1

The simplest way for you is to create a query with a subquery

SELECT s.*,
       (SELECT COUNT(*) 
        FROM tblBorrowedBook b 
        WHERE b.status = 'Not Retuned' AND b.studentID = s.StudentId) as BookCount
FROM tblStudent s 
WHERE lastName LIKE 'abc%'

This will get you all the fields and the count

edited Mar 26 '20 at 03:57

answered Mar 26 '20 at 03:17

T.S.

18,195
11
58
78

Thank you so much, this solved my problem. I didn't know there is a thing called subquery as i am very new to databases and programming. I will practice how to use parameters as the others say. Thank you so much – kmandrew Mar 26 '20 at 03:54

How to count records loaded by data reader

1 Answers1