C# How to output multiple sql data to text? (Single query is working)

Question

I'm searching for a tip or a code sample to output multiple records within my SQL database with the same name, called Auftragsnummer but differently second values called Stellplatz, to a textbox.

This is my working code, to output one search query to two textboxes:

private void search_btn_Click(object sender, EventArgs e)
 {
 con.Open();
 cmd.Connection = con;
 SqlCommand myCommand = new SqlCommand("SELECT * FROM[FAUF] WHERE Auftragsnummer = '" + txt_search.Text + "'", con);
 mdr = myCommand.ExecuteReader();
 if (mdr.Read())
 {
 txt_searchNr.Text = mdr["Auftragsnummer"].ToString();
 txt_searchSF.Text = mdr["Stellplatz"].ToString();
 txt_search.SelectAll();
 }
 else
 {
 txt_searchNr.Text = "---";
 txt_searchSF.Text = "---";
 txt_search.SelectAll();
 MessageBox.Show("Keine Daten gefunden - Bitte überprüfen Sie Ihre Eingaben!");
 }
 con.Close();
 }

The user can add the Auftragsnummer multiple times with differently second values named Stellplatz.

Example:
102555 - > 3
102555 - > 7

How I get the second record from my SQL database into a second output within my Code?

• jeroenh: As explained in the documentation: while (reader.Read()) { /* a new row is available */ }
• Draken: Please don't add an extra question into your original post, instead if you have a new question, ask a new one and link it to this post

Answer 1

You while loop until mdr.Read() is unavailable to resolve your problem:

 private void search_btn_Click(object sender, EventArgs e)
 {
 con.Open();
 cmd.Connection = con;
 SqlCommand myCommand = new SqlCommand("SELECT * FROM[FAUF] WHERE Auftragsnummer = '" + txt_search.Text + "'", con);
 mdr = myCommand.ExecuteReader();
 if (!mdr.Read())
 {
 txt_searchNr.Text = "---";
 txt_searchSF.Text = "---";
 txt_search.SelectAll();
 MessageBox.Show("Keine Daten gefunden - Bitte überprüfen Sie Ihre Eingaben!");
 con.Close();
 return;
 }
 while (mdr.Read())
 {
 //Do your code here
 }
 con.Close();
 }

• Seron: Thank you for your sample :) My problem is now, that the latest record is shown in my output, but the second record is not shown in the second texboxes. I want to fill in with the first record TextAuftrag1 and TextST1 and with the second record another pair named like TextAuftrag2 and TextST2.

Answer 2

Close Con and open it again and write new query

Answer 3

The code below is now working for two records but when there is only one record in the database, the output seems to be empty.

 if (!mdr.Read())
 {
 txt_searchNr.Text = "---";
 txt_searchSF.Text = "---";
 txt_searchNr1.Text = "---";
 txt_searchSF1.Text = "---";
 MessageBox.Show("Keine Daten gefunden - Bitte überprüfen Sie Ihre Eingaben!");
 con.Close();
 return;
 }
 if (mdr.HasRows)
 {
 while (mdr.Read())
 {
 txt_searchNr.Text = mdr["Auftragsnummer"].ToString();
 txt_searchSF.Text = mdr["Stellplatz"].ToString();
 while (mdr.Read())
 {
 txt_searchNr1.Text = mdr["Auftragsnummer"].ToString();
 txt_searchSF1.Text = mdr["Stellplatz"].ToString();
 }
 mdr.NextResult();
 }
 }
 con.Close();