Member Avatar for Tank50

Hi

I wrote below coding ,and last Message "Test" never appear in my project ,it means while loop never works.Plase tell me what is problem in my coding

public partial class Form1 : Form
    {
        public static int Number_Records;
        public static DataSet ds;
        public static DataTable dt;
        public static int Next_Number;
        public Form1()
        {
            InitializeComponent();

    
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt1 = new DataTable();
            SqlConnection con = new SqlConnection("Server=IT-Test;Database=NTY;user id=sa;password=12345");
            try
            {
                con.Open();
                string str = "select ExecutiveCode from dbo.RDExecutive order by ExecutiveCode";
                SqlCommand cmd = new SqlCommand(str, con);
                SqlDataReader CurrentOutLet1 = cmd.ExecuteReader();

                int i = 0;
                while (CurrentOutLet1.Read())
                {

                  Number_Records= i++;
                }
             
                for (int y1 = 0; y1 <  CurrentOutLet1.FieldCount; y1++)
                {
                  //  MessageBox.Show(CurrentOutLet1.FieldCount.ToString());
                    //MessageBox.Show(CurrentOutLet1.GetName(y1).ToString());
                   dt1.Columns.Add(CurrentOutLet1.GetName(y1),CurrentOutLet1.GetFieldType(y1));
                                   
                }

               
                while (CurrentOutLet1.Read())
                {
                    MessageBox.Show("Test");

              
                   }

No "Test" message.

Thanks
Tank50

  • 4 Contributors
  • 3 Replies
  • 242 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by djloser

Dani AI

Generated

Short expert note: the SqlDataReader is forward-only and once a Read loop runs to the end there are no rows left for subsequent Read calls. correctly pointed this out; echoed it. The simplest fixes are to either load the schema+rows into a DataTable once, or re-execute the command when a second pass is required. FieldCount is the number of columns (not rows), and the pattern used to count rows in the original loop also has an off-by-one issue because of i++ being used in the assignment.

A compact, robust approach that captures schema and data in one step is DataTable.Load:

using (var cmd = new SqlCommand(query, connection))
using (var reader = cmd.ExecuteReader())
{
    var table = new DataTable();
    table.Load(reader); // loads columns and all rows
    // inspect schema: table.Columns
    // iterate rows: foreach (DataRow r in table.Rows) { ... }
}

If the goal is only to populate a DataTable for UI or further processing, SqlDataAdapter.Fill is equally simple:

using (var da = new SqlDataAdapter(query, connection))
{
    var table = new DataTable();
    da.Fill(table);
    // use table.Columns and table.Rows as needed
}

Extra troubleshooting and best practices: for a row count, run SELECT COUNT(*) (fast) or read table.Rows.Count after filling. Avoid assigning Number_Records = i++ inside the loop; either increment then assign (Number_Records = ++i;) or assign once after the loop (Number_Records = i;). Always wrap connections/readers in using to ensure disposal, use parameterized queries, and avoid hard‑coding privileged credentials in source code. These changes preserve resources and make the logic deterministic when revisiting this thread months or years later.

Recommended Answers

All 3 Replies

Member Avatar for JerryShaw

Simple, the first while loop already read through the entire data reader, so there is nothing left to read by the time you get to the last while loop.

Seems that you are going about it the hard way. Why not just use an SqlDataAdapter and fill the dtl table. That way, it automatically will get all of the columns, and the data in one method call.

commented: Correct +7
Member Avatar for Ramy Mahrous

Jerry is well answered.

Member Avatar for djloser

+1

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.