Why "connection" is "Null"?

Question

Dear all,

Why "connection" is "Null"?

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

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

Answer 1

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

Opening the connection connection.Open() method ensures that a connection to the database is established before executing any commands.

From the code you have shown so far, it is not clear whether this is caused by code you have not shown.

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

try
{
    using (SqlConnection connection = new SqlConnection(cConnectionString))
    {
        connection.Open(); // Ensure the connection is opened before using it

        string PreSelectQry = "Select top 1 StudentID, EngName from student where EngName >= @EngName and studentid > @StudentID order by @OrderByColumn";
        SqlCommand command = new SqlCommand(PreSelectQry, connection);
        
        // Adding parameters to avoid SQL injection and errors
        command.Parameters.AddWithValue("@EngName", cEngName);
        command.Parameters.AddWithValue("@StudentID", nStudentID);
        command.Parameters.AddWithValue("@OrderByColumn", nStudentID == 0 ? "StudentID" : "EngName, StudentID");
        
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds, "PreStudent");

        DataTable dt = ds.Tables[0];
        // Process DataTable 'dt' as needed
    }
}
catch (Exception ex)
{
    // Log the exception or handle it as necessary
    Console.WriteLine("An error occurred: " + ex.Message);
}

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.