loop through all values in sql table using sql data reader

Question

I want to fetch all rows that related to the query below, my problem that only one row retrived not all rows , iam using asp.net with c# and ado.net and my code logic is

if (!IsPostBack)
{
    string username = Session["username"].ToString();
    con.Open();
    string strqryScript = "select * from dbo.teachers where user_id = '" + username + "'";
    SqlCommand cmd = new SqlCommand(strqryScript, con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    SqlDataReader rdr = cmd.ExecuteReader();
    rdr.Read();         
    string name  = rdr["teach_id"].ToString();
    rdr.Close();

    string query = "select * from dbo.teacher_classes where teach_id = '" + name + "' ORDER BY class_id";
    SqlCommand cmd2 = new SqlCommand(query, con);
    SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    SqlDataReader rdr2 = cmd2.ExecuteReader();
    while (rdr2.Read())
    {
        classname.Text = rdr2["class_id"].ToString();
    }
    con.Close();
}

extra note that i can use gridview to bind data but i want to fill my table with custom information from many tables , so i want to use an html table and fill it with my custom data. any help please! and thanks ..

Please, don't create your queries composing strings!!! Use query parameters to avoid problems with international settings on dates and numbers and, above all, Sql injection!! — Marco
– Marco, Commented May 17, 2013 at 15:47
Consider the following blog post about writing core ADO.NET code: lostechies.com/jimmybogard/2012/07/24/dont-write-your-own-orm — Mike Cole
– Mike Cole, Commented May 17, 2013 at 15:48
@DavidKemp The homework tag has been officially deprecated. meta.stackexchange.com/questions/147100/… — Jesse Webb
– Jesse Webb, Commented May 17, 2013 at 15:49

Community · Accepted Answer · 2017-05-23 12:05:22Z

4

While looping on the second reader, you write the value extracted from the reader on the Text property of the classname label. This will overwrite the previous text and leave you with the name of the last teacher retrieved. You need to add to the previous text or use a List.

classname.Text += rdr2["class_id"].ToString();

Said that, let me point you to a big problem in your code. String concatenation is really bad when you build sql commands. It gives you back syntax errors (if your input text contains single quotes) or Sql Injection as explained here

You should use parameterized queries like this (just for your first command)

string strqryScript = "select * from dbo.teachers where user_id = @id";
SqlCommand cmd = new SqlCommand(strqryScript, con);
cmd.Parameters.AddWitValue("@id", username);
....

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered May 17, 2013 at 15:45

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Fabian Bigler · Accepted Answer · 2013-05-17 15:49:51Z

0

This is the issue you need to fix:

classname.Text = rdr2["class_id"].ToString(); <== always setting the same text!!

You need to make sure, you fill a list, a dataset or whatever, when reading the data!

answered May 17, 2013 at 15:49

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

loop through all values in sql table using sql data reader

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