How to fix the error: cStudentID always reset to "0"

Question

Dear all,

I encountered a bug that cStudentID is always reset to "0". I know that I should use the modifier static instead of string at the first line of the RetrieveData() function. Could you show me how?

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

namespace NewTims
{
    public partial class Student_Form : Form
    {
        public int Conn;
        public Student_Form()
        {
            InitializeComponent();
            RetrieveData();
        }

        public void RetrieveData()
        {
            { string cStudentID = "0";
                string cConnectionString = "Data Source=(local); Initial Catalog=Tims; Integrated Security=True";

                using (SqlConnection connection = new SqlConnection(cConnectionString))
                {
                    string cEngName = " ";
                    string PreSelectQry = "Select top 1 StudentID, EngName from student order by "
                        + (cStudentID == "0" ? "StudentID" : "EngName, StudentID where EngName > "
                        + cEngName + " and studentid > " + cStudentID);
                    SqlCommand command = new SqlCommand(PreSelectQry, connection);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                        //string PreSelectQry = "Select top 1 StudentID from student order by " 
                        //    + cStudentID=="0" ? "StudentID" : "EngName, StudentID";
                        //SqlCommand command = new SqlCommand(PreSelectQry, connection);
                        //SqlDataAdapter adapter = new SqlDataAdapter(PreSelectQry);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "PreStudent");
                    DataTable dt = ds.Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        cStudentID = dt.Rows[0].Field<int>("StudentID").ToString();
                        cEngName = dt.Rows[0].Field<string>("EngName").ToString();
                    }

                    string SelectQuery = @"select s.*, g.EngName as GroupName, c.SubjectID"
                        + " from student s"
                        + " left Join student g on s.memberOf = g.studentid"
                        + " left join studentCourse c on s.studentid = c.studentid"
                        + " left join course o on c.CourseID = o.CourseID"
                        + " where s.StudentID = " + cStudentID
                        + " order by s.StudentID";

                    command = new SqlCommand(SelectQuery, connection);
                    adapter = new SqlDataAdapter(command);
                    ds = new DataSet();
                    adapter.Fill(ds, "Student");

                    dt = ds.Tables["Student"];
                    //string cEngName;
                    if (dt.Rows.Count > 0)
                    {
                        DataRow r = dt.Rows[0];
                        cEngName = r.Field<string>("EngName").Trim();
                        string Sex = r.Field<string>("Sex");
                        if (Sex.Trim() != "O")
                        {
                            int iFirstBlank = cEngName.IndexOf(" ", 0);
                            Surname_textBox.Text = cEngName.Substring(0, iFirstBlank - 1);
                            GivenName_textBox.Text = cEngName.Substring(iFirstBlank + 1);
                        }
                        else
                        {
                            Surname_textBox.Text = cEngName;
                        }
                        Chinese_textBox.Text = r.Field<string>("Chiname");
                        StuID_textBox.Text = string.Format("{0}", r.Field<int>("StudentID").ToString("000000"));
                        HKIDCard_textBox.Text = r.Field<string>("IdCard");
                        TelEmail_textBox.Text = r.Field<string>("TelEmail");
                        MemberOf_xtextBoxButton.MyText = r.Field<string>("GroupName");
                        cStudentID = StuID_textBox.Text;
                        cEngName = Surname_textBox.Text.Trim() + ", " + GivenName_textBox.Text.Trim();
                    }
                    else
                    {
                        // Handle case when no data is returned
                    }
                }
            }
        }
    }
}

Answer 1

Hi @BenTam-3003 , Welcome to Microsoft Q&A,

You really should use private static string. You can modify the code like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace NewTims
{
    public partial class Student_Form : Form
    {
        private static string cStudentID = "0"; //Declare it as a private field and use the static modifier

        public Student_Form()
        {
            InitializeComponent();
            RetrieveData();
        }

        public void RetrieveData()
        {
            string cConnectionString = "Data Source=(local); Initial Catalog=Tims; Integrated Security=True";

            using (SqlConnection connection = new SqlConnection(cConnectionString))
            {
                string cEngName = " ";
                string PreSelectQry = "Select top 1 StudentID, EngName from student order by "
                    + (cStudentID == "0" ? "StudentID" : "EngName, StudentID where EngName > "
                    + cEngName + " and studentid > " + cStudentID);
                SqlCommand command = new SqlCommand(PreSelectQry, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "PreStudent");
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    cStudentID = dt.Rows[0].Field<int>("StudentID").ToString();
                    cEngName = dt.Rows[0].Field<string>("EngName").ToString();
                }

                string SelectQuery = @"select s.*, g.EngName as GroupName, c.SubjectID"
                    + " from student s"
                    + " left Join student g on s.memberOf = g.studentid"
                    + " left join studentCourse c on s.studentid = c.studentid"
                    + " left join course o on c.CourseID = o.CourseID"
                    + " where s.StudentID = " + cStudentID
                    + " order by s.StudentID";

                command = new SqlCommand(SelectQuery, connection);
                adapter = new SqlDataAdapter(command);
                ds = new DataSet();
                adapter.Fill(ds, "Student");

                dt = ds.Tables["Student"];
                if (dt.Rows.Count > 0)
                {
                    DataRow r = dt.Rows[0];
                    cEngName = r.Field<string>("EngName").Trim();
                    string Sex = r.Field<string>("Sex");
                    if (Sex.Trim() != "O")
                    {
                        int iFirstBlank = cEngName.IndexOf(" ", 0);
                        Surname_textBox.Text = cEngName.Substring(0, iFirstBlank - 1);
                        GivenName_textBox.Text = cEngName.Substring(iFirstBlank + 1);
                    }
                    else
                    {
                        Surname_textBox.Text = cEngName;
                    }
                    Chinese_textBox.Text = r.Field<string>("Chiname");
                    StuID_textBox.Text = string.Format("{0}", r.Field<int>("StudentID").ToString("000000"));
                    HKIDCard_textBox.Text = r.Field<string>("IdCard");
                    TelEmail_textBox.Text = r.Field<string>("TelEmail");
                    MemberOf_xtextBoxButton.MyText = r.Field<string>("GroupName");
                    cStudentID = StuID_textBox.Text;
                    cEngName = Surname_textBox.Text.Trim() + ", " + GivenName_textBox.Text.Trim();
                }
                else
                {
                    // Handle case when no data is returned
                }
            }
        }
    }
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.