CODEWITHSUNDEEP
c#sqlwinforms
user6767011
user6767011

Reputation: 19

How to get each data in each row C# & SQL

Let's say I have a table with 3 columns like this:

ID | NAME | Subject
---+------+----------
 1 | Mark | English
 1 | Mark | Math
 2 | Matt | Math
 2 | Matt | English
 1 | Mark | History

How to get each subject of "Mark" like English, Math, History (order by) that will match to their id in every row in column subject? Because I only get the first subject which is "English" or the first row.

string sql = "select * from tbl_Student where ID like '"+ID.Text+"'";

cm = new SqlCommand(sql, cn);
dr = cm.ExecuteReader();

while (dr.Read())
{
    Subject1.Text = dr["Subject"].ToString();
    Subject2.Text = dr["Subject"].ToString();
    Subject3.Text = dr["Subject"].ToString();
}

dr.Close();

Upvotes: 0

Views: 920

Answers (3)

SᴇM
SᴇM

Reputation: 7213

Use StringBuilder:

StringBuilder sb = new StringBuilder();

while (dr.Read())
{
    sb.Append(dr["Subject"].ToString());
    sb.Append(",");

}

result = sb.ToString().TrimEnd(',');

UPDATE

use switch/case then, to determine your id and assign its value to proper TextBox:

while (dr.Read())
{
    string subject = dr["Subject"].ToString();
    switch (dr["ID"].ToString()) 
    {
        case "1":
            Subject1.Text += subject + " ";//$"{subject} "; //or use string builder as I've showed above 
            break;
        case "2":
            Subject2.Text += subject + " ";//$"{subject} ";
            break;
        case "3":
            Subject3.Text += subject + " ";//$"{subject} ";
            break;
        default:
            break;
    }
}

Also, please use Parameterized Queries.

Upvotes: 0

Armin Lizde
Armin Lizde

Reputation: 1

I would definitely change the like operator to an equal(=) operator. And you are having always one value in the loop, concatenate the strings.

Upvotes: 0

Antoine V
Antoine V

Reputation: 7204

You replace the value of Subject.Text in each loop. That means it contains only the last value.

You should concatenate the string 

Subject.Text += dr["Subject"].ToString();

Upvotes: 3

Related Questions