C# Display SQL output into textbox

December 8, 2021

I am having issues trying to display the result of an SQL query into a textbox in my WPF program. My code looks like this:

private void btnCompare_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                string commandText = "SELECT ID, FirstName, LastName, Email, City FROM ( SELECT ID, FirstName, LastName, Email, City FROM CompareTable UNION ALL SELECT MainTable.ID, MainTable.FirstName, MainTable.LastName, MainTable.Email, MainTable.City FROM MainTable )  CompareTable GROUP BY ID, FirstName, LastName, Email, City HAVING COUNT(*) = 1";
                SqlConnection conn = new SqlConnection(connectionstring);
                SqlCommand comm = new SqlCommand(commandText, conn);
                conn.Open();
                txtResult.Text = (string)comm.ExecuteScalar();
                conn.Close();
            }catch(Exception d)
            {
                MessageBox.Show(d.ToString());
            }
                
        }

When I run the query in the Azure DB, I get the output I’m looking for.
But the for some reason, the result output is an Int32 variable, this is the error I get:

Can someone explain why the output is not a string?

>Solution :

ExecuteScalar() returns a single value, which is the first column of the first row if the query returns a table. So it’s trying to "cast" the first ID from an integer to a string and fails. I suspect you want ExecuteReader instead, loop through the results, and build a string somehow.

Or you could fill a DataTable and get the data from the rows and columns of that. There are plenty of examples out there of both methods.