How to fix this error on my Window Application?

Hello,

Your database connection was closed that's why it throw an error.

Please refer the below code.

if(con.State == ConnectionState.Closed)
{
	con.Open();
}

string queryPremix = "SELECT ids FROM [dbo].[Ard_Batch_Materials] WHERE code = 'PREMIX'";
SqlCommand cmdPremix = new SqlCommand(queryPremix, con);
MatIdx = Convert.ToInt32(cmdPremix.ExecuteScalar());

Thanks

Try this:

string connectionString = "your_connection_string_here";
string queryPremix = "SELECT idx FROM [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX'";
using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand cmdPremix = new SqlCommand(queryPremix, con);

    try
    {
        con.Open();
        object result = cmdPremix.ExecuteScalar();
        int MatIdx = (result != null) ? Convert.ToInt32(result) : 0;
        Console.WriteLine("MatIdx: " + MatIdx);
    }
    catch (Exception ex)
    {
        Console.WriteLine("An error occurred: " + ex.Message);
    }
}

Hi Gcobani,
As i can review your code, the error you're encountering when using ExecuteScalar() with the query SELECT idx FROM [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX' is likely due to the query potentially returning multiple rows.

The ExecuteScalar() method is designed to retrieve a single value.

Solution 1:
If you expecting only single record as per your WHERE condition, you shoud check your data there might duplicate record exist in the table [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX'

Solution 2.:

Modify the your query if you expecting single value return by your query.

SELECT TOP 1 idx FROM [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX'

OR

SELECT COUNT(idx) FROM [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX'

You can take refrence from this sample code:

string connectionString = "your_connection_string_here";
string query = "SELECT COUNT(idx) FROM [dbo].[Adr_Batch_Materials] WHERE Code = 'PREMIX'";  // Example query

      using (SqlConnection connection = new SqlConnection(connectionString))
      {
          SqlCommand command = new SqlCommand(query, connection);
          try
             {
                connection.Open();
                int count = (int)command.ExecuteScalar();
                //MessageBox.Show("Count is: " + count);
             }
           catch (Exception ex)
             {
                MessageBox.Show("An error occurred: " + ex.Message);
             }
       }

Note:

When you expect your query to return multiple rows, you should use methods that are designed to handle multiple results, such as ExecuteReader() in ADO.NET. This method provides a forward-only, read-only iterator over the result set that allows you to handle each row returned by the query one at a time. Here’s how you can use ExecuteReader() to process multiple rows.

Hello,

As per the exception message, you need to open a connection before executing the command. Try the below code.

con.Open();
SqlCommand cmdPremix = new SqlCommand(queryPremix, con);
MatIdx = Convert.ToInt32(cmdPremix.ExecuteScalar());

Thanks

Make sure you are opening the connection before executing the command.