Issue with the connection

Question

Issue with the connection

Peter Liang 3,266

Hi,

I got the issue like

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

on the last line below. Any help?

User's image

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer accepted by question author

1 additional answer

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Jack Dang (WICLOUD CORPORATION) 15,870 Microsoft External Staff Moderator

Hi @Peter Liang ,

Thanks for reaching out.

The error ExecuteScalar requires an open and available Connection. The connection's current state is closed appears when the SqlCommand doesn’t know which database connection to use, or the connection isn’t open when the command runs.

One way to fix this is to make sure your command is linked to an open connection and use using blocks so the connection is managed safely. For example:

int cnt5 = 0;

if (string.IsNullOrWhiteSpace(tb_cust_code.Text))
{
    MessageBox.Show("Please enter a customer code");
    return;
}

using (SqlConnection conn = new SqlConnection(yourConnectionString))
{
    conn.Open();

    using (SqlCommand cmd5 = new SqlCommand(
        "SELECT COUNT(*) FROM customer3 WHERE No_=@custcode",
        conn
    ))
    {
        cmd5.Parameters.Add("@custcode", SqlDbType.VarChar).Value = tb_cust_code.Text;
        cnt5 = (int)cmd5.ExecuteScalar();
    }
}

MessageBox.Show($"Matching rows: {cnt5}");

You can take this snippet as a reference and adjust it to fit your project’s structure.

Hope this helps! If my answer was helpful - kindly follow the instructions here so others with the same problem can benefit as well.

Peter Liang 3,266 Reputation points

2026-03-03T08:25:22.3566667+00:00

I made the change but I still got

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

on the parameter add line below.
Jack Dang (WICLOUD CORPORATION) 15,870 Reputation points Microsoft External Staff Moderator

2026-03-04T08:38:03.4366667+00:00
Hi @Peter_1985 ,

Thanks for the clarification.

The problem is that the connection is never opened. Creating a SqlConnection does not open it automatically. It starts in a closed state, and ExecuteScalar() requires it to be open.

You need to add conn2.Open(); before executing the command.

Like this:

using (SqlConnection conn2 = new SqlConnection( ConfigurationManager.ConnectionStrings["Mss..."].ToString())) { conn2.Open(); // <-- This was missing using (SqlCommand cmd5 = new SqlCommand( "SELECT COUNT(*) FROM customer3 WHERE curr_code=@custcode AND ISNULL(isdelete,0)=0", conn2)) { cmd5.Parameters.Add("@custcode", SqlDbType.VarChar) .Value = tb_cust_code.Text.Trim(); cnt5 = Convert.ToInt32(cmd5.ExecuteScalar()); } }

You can treat this as a reference and adjust it to fit your project.
Peter Liang 3,266 Reputation points

2026-03-04T09:09:46.8833333+00:00

See the issue below.
AgaveJoe 31,186 Reputation points

2026-03-04T11:21:16.4666667+00:00

C# has always been case sensitive. Your screenshot clearly shows the an error on the line conn2.open. The O in Open should be capitalized!

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Issue with the connection

1 additional answer

Your answer