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
– WynDiesel, Commented Aug 24, 2018 at 7: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
– WynDiesel, Commented Aug 24, 2018 at 7:29
I've updated the post with what i would like to be shown in the Rich Textbox — Michael Ejvind Therkildsen
– Michael Ejvind Therkildsen, Commented Aug 24, 2018 at 7:43
Are you looking for that exact message, or do you want to know how many results you had in the select? — WynDiesel
– WynDiesel, Commented Aug 24, 2018 at 7:47

Jack Gajanan · Accepted Answer · 2018-08-24 07:47:13Z

0

Use SqlConnection.InfoMessage event,

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

answered Aug 24, 2018 at 7:47

Jack Gajanan

1,69016 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WynDiesel · Accepted Answer · 2018-08-24 07:59:42Z

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, 2018 at 7:59

WynDiesel

1,2141 gold badge10 silver badges44 bronze badges

4 Comments

Michael Ejvind Therkildsen Over a year ago

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

WynDiesel Over a year ago

@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.

Michael Ejvind Therkildsen Over a year ago

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

WynDiesel Over a year ago

You're completely right, it seems. Please see stackoverflow.com/questions/1383315/…

Salvatore · Accepted Answer · 2018-08-24 10:20:41Z

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, 2018 at 10:20

answered Aug 24, 2018 at 8:23

Salvatore

1,4311 gold badge12 silver badges21 bronze badges

2 Comments

Michael Ejvind Therkildsen Over a year ago

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 =)

Salvatore Over a year ago

@MichaelEjvindTherkildsen I've updated my post for select queries as well now. I hope it helps :)

Michael Ejvind Therkildsen · Accepted Answer · 2018-08-24 12:44:13Z

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:

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related