How to get message from SQL query into rich textbox in c#

Question

In SQL Server Management studio, when you do a SELECT query, you also get a message aswell as the results. That message i would like to use in a Rich Textbox. Is that possible? The Message i'm talking about is this:

I have this code so far, that also fills comboboxes with results, but i would like my rich textbox to show the message aswell:

using (SqlConnection Con = new SqlConnection(Connectionstring.selectedBrugerString))
{
    Con.Open();
    SqlCommand cmd = new SqlCommand("SELECT USERNAME FROM PERSONAL", Con);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        fromComboBox.Items.Add(reader[0]);
        toComboBox.Items.Add(reader[0]);
    }
    Con.Close();
}

The GUI looks like this:

Side note; when you use the "using" keyword, like you did, you don't need to close your connection, like you did. The destructor fires when the bracket closes, and closes the connection as well. — WynDiesel, Aug 24 '18 at 07:13
When you say "message", I assume you mean the Username field from your query. If so, I don't quite understand what your problem/question is; you're already setting the values of other controls to that field, why would you ask how to set the textblock? Is your question rather : how to set the value of a textbox? — WynDiesel, Aug 24 '18 at 07:29
I've updated the post with what i would like to be shown in the Rich Textbox — Michael Ejvind Therkildsen, Aug 24 '18 at 07:43
Are you looking for that exact message, or do you want to know how many results you had in the select? — WynDiesel, Aug 24 '18 at 07:47
I think i have found out to do this.. I want to show how many rows effected..I've found out to use the ExecuteNonQuery.. but now i'm stumbled into get that converted to string instead of Int. — Michael Ejvind Therkildsen, Aug 24 '18 at 07:58
You could simply count the number of items in the Items in the combobox — ChrisBint, Aug 24 '18 at 08:45
What I havent told in the post is that i want to use the window like the message window in the SSMS. The button have to edit some records in a table, and a want to show how many rows that have been affected by a UPDATE query :) — Michael Ejvind Therkildsen, Aug 24 '18 at 09:09

score 0 · Answer 1 · answered Aug 24 '18 at 07:47

0

Use SqlConnection.InfoMessage event,

This event occurs when SQL Servers returns a warning or informational message.

answered Aug 24 '18 at 07:47

Jack Gajanan

1,596
14
18

score 0 · Answer 2 · answered Aug 24 '18 at 07:59

0

You don't need to look at the message. You can just look at the reader's RowsEffected, like this :

using (SqlConnection Con = new SqlConnection(Connectionstring.selectedBrugerString))
{
    Con.Open();
    SqlCommand cmd = new SqlCommand("SELECT USERNAME FROM PERSONAL", Con);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        fromComboBox.Items.Add(reader[0]);
        toComboBox.Items.Add(reader[0]);
    }
    var recordsEffected = reader.RecordsAffected;
}

answered Aug 24 '18 at 07:59

WynDiesel

1,104
7
38

I also have some code that have to run when i push the button i have made, with multiple SQL queries. How will that work, if i want to have the Affected Records text shown? In SSMS the message shows affected rows for each SELECT query – Michael Ejvind Therkildsen Aug 24 '18 at 08:19
@MichaelEjvindTherkildsen I would recommend doing that in different commands. Fire one command, get the rows effected, do the next. Etc. Don't try to do everything in one go. You can do it your way, where you have to navigate through the reader's result sets. I can't give you an exact answer on how to do this, as I always work with one command per query. – WynDiesel Aug 24 '18 at 08:26
1

If I use this method, I get -1 rows affected even if the comboboxes shows lets say 4 records from my table. That's odd :S – Michael Ejvind Therkildsen Aug 24 '18 at 08:56
You're completely right, it seems. Please see https://stackoverflow.com/questions/1383315/how-to-get-number-of-rows-using-sqldatareader-in-c-sharp – WynDiesel Aug 24 '18 at 09:01

Salvatore · Answer 3 · 2018-08-24T10:20:41.470

0

ExecuteNonQuery() - returns the number of rows affected.

SqlCommand comm;
// random code
int numberOfRecords = comm.ExecuteNonQuery();

EDIT:

For select queries you can use ExecuteScalar() -

String sqlQuery = "SELECT count(USERNAME) FROM PERSONAL";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
try
{
    conn.Open();
    //Since return type is System.Object, a typecast is must
    count = (Int32)cmd.ExecuteScalar();
}

edited Aug 24 '18 at 10:20

answered Aug 24 '18 at 08:23

Salvatore

1,435
1
10
21

I finally got this to not throw an exception. But it only returns -1 rows. I've now read that this only work on Insert, Update, and Delete queries.. So probably this will work for the update i will make when i push the button =) – Michael Ejvind Therkildsen Aug 24 '18 at 10:08
@MichaelEjvindTherkildsen I've updated my post for `select` queries as well now. I hope it helps :) – Salvatore Aug 24 '18 at 10:21

score 0 · Answer 4 · answered Aug 24 '18 at 12:44

I got it all to work. Thanks for the big help. Much apprieciated! I will show it all if any can use any off it :D

Ended up with this:

using (SqlConnection Con = new SqlConnection(Connectionstring.selectedBrugerString))
        {
            Con.Open();
            SqlCommand cmd = new SqlCommand("SELECT USERNAME FROM PERSONAL", Con);
            using (SqlDataReader reader = cmd.ExecuteReader())
                {
                while (reader.Read())
                {
                    fromComboBox.Items.Add(reader[0]);
                    toComboBox.Items.Add(reader[0]);

                }
            }
            var countUsers = fromComboBox.Items.Count;
            movePostMessage.Text = countUsers + " brugere er fundet";
        }

    }

And on the button click i've made this:

using (SqlConnection Con = new SqlConnection(Connectionstring.selectedBrugerString))
        {
            Con.Open();
            string SQL1 = "UPDATE DEBSALDO SET PERSONALID = (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + toComboBox.Text + "') WHERE PERSONALID IN (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + fromComboBox.Text + "')";
            string SQL2 = "UPDATE SERVHEAD SET PERSONALID = (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + toComboBox.Text + "') WHERE PERSONALID IN (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + fromComboBox.Text + "')";
            string SQL3 = "UPDATE SERVICEB SET PERSONALID = (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + toComboBox.Text + "') WHERE PERSONALID IN (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + fromComboBox.Text + "')";
            string SQL4 = "UPDATE PERSONAL SET ACOUNTCYID = NULL WHERE PERSONALID IN (SELECT PERSONALID FROM PERSONAL WHERE USERNAME = '" + fromComboBox.Text + "')";
            SqlCommand cmd1 = new SqlCommand(SQL1, Con);
            SqlCommand cmd2 = new SqlCommand(SQL2, Con);
            SqlCommand cmd3 = new SqlCommand(SQL3, Con);
            SqlCommand cmd4 = new SqlCommand(SQL4, Con);
            Int32 rowsAffectedOnUpdate1 = cmd1.ExecuteNonQuery();
            Int32 rowsAffectedOnUpdate2 = cmd2.ExecuteNonQuery();
            Int32 rowsAffectedOnUpdate3 = cmd3.ExecuteNonQuery();
            Int32 rowsAffectedOnUpdate4 = cmd4.ExecuteNonQuery();
            movePostMessage.Text = rowsAffectedOnUpdate1 + " regninger flyttet fra tidligere brugers debitor saldo \r\n" + rowsAffectedOnUpdate2 + " regninger flyttet med tidligere bruger som ejer \r\n" + rowsAffectedOnUpdate3 + " ydelser flyttet med tidligere bruger som ejer \r\n" + rowsAffectedOnUpdate4 + " kontoplan nulstillet";
        }

And the GUI to it:

How to get message from SQL query into rich textbox in c#

4 Answers4