How to fix this error on my Window Application?

Question

Hi Team

I have a barcode app, somehow my connection string are correct, but when i try to run this app its giving me this weird and dont know how to resolve it. See below the screen show for more details,

Nikunj Satasiya · Answer

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.

Jaimin Shethiya · Answer

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

Amit Mohanty · Answer

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); } }

Vishal Joshi · Answer

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

Amit Mohanty · Answer

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