mysql SQL statement doesn't work C#

Question

I have been trying to make this code block work, but I can't seem to find the error in it. It doesn't return the value that I am looking for. I would try to run the SQL statement in workbench and it returns my desired number of rows(which is 3).

        try
        {
            using (MySqlConnection con = new MySqlConnection(GlobalValues.ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandText =
                        @"
                            SELECT strDetPremID, strPremiseName
                                    FROM tbldetailprem 
                                    INNER JOIN tblpremise ON strDetPremID = strPremiseID
                                    WHERE strDetPremContID = @id;
                            ;
                            "
                        ;

                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.Connection = con;
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        con.Open();
                        cmd.ExecuteNonQuery();
                        this.Clear();
                        da.Fill(this);
                        con.Close();
                    }
                }
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.ToString() + " - " + ex.Number);
        }

Can you please tell me what's wrong with this code

Your running a select statement, and calling for 2 values (strDetPremID and strPremiseName) and you're expecting a int back? How is that statement only returning 3? You're also not affecting any rows by selecting. — Darren Wainwright
– Darren Wainwright, Commented Oct 11, 2016 at 17:02
What kind of application is this? Typically what I do is execute a datareader, then assign the variable based on the datareader's property. Your SQL is a SELECT statement, so why are you doing ExecuteNonQuery? — Christine
– Christine, Commented Oct 11, 2016 at 17:06
@kalamazoowho - It appears to be an instance of MySqlDataAdapter. — Christine
– Christine, Commented Oct 11, 2016 at 17:07
A select statement is a query, but you use ExecuteNonQuery. Also it's not clear what part of that query should give you a result of only 3. — juharr
– juharr, Commented Oct 11, 2016 at 17:07

Bouke · Accepted Answer · 2016-10-11 17:18:54Z

1

When you want to count the number of rows, use the following query:

SELECT COUNT(*) FROM tbldetailprem 
INNER JOIN tblpremise ON strDetPremID = strPremiseID
WHERE strDetPremContID = @id;

Then use the ExecuteScalar() method on the command object to get the value of COUNT(*). The ExecuteNonQuery() is used for adding, updating and deleting records, not for selecting them.

More information can be found here: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

edited Oct 11, 2016 at 17:18

answered Oct 11, 2016 at 17:13

Bouke

1,6151 gold badge16 silver badges26 bronze badges

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

Comments

Quentin · Accepted Answer · 2016-10-11 17:10:47Z

0

This may serve more as a comment but I lack the rep to comment.

It's not exactly clear, to me, in the code where the issue may be coming from so my assumption is that there's a bug that's not being caught as a MySQLException.

Have you tried removing the try/catch statement in order to catch any bugs that would prevent affectedRows being updated?

Also, I'm not sure if it's causing a SyntaxError or not but it seems you have an extra semicolon in cmd.CommandText... I think.

answered Oct 11, 2016 at 17:10

Quentin

7004 silver badges10 bronze badges

Collectives™ on Stack Overflow

mysql SQL statement doesn't work C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related