C# select from SQL Server database

Question

I want to make a extra control in my C# application if the record exist.

I have got the following code - but it keeps returning a result of -1 even though the record does exist in the SQL Server database.

Can someone help me with this? I have added --> for where it went wrong

private void btnVerwijderen_Click(object sender, RoutedEventArgs e)
{
    if (autonrTextBox.Text == "")
    {
        MessageBox.Show("Waarschuwing u kunt geen auto verwijderen indien er GEEN autonr is ingevuld");
    }
    else
    {
    --> SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-RSEBNR7;Initial Catalog=AudiDealer;Integrated Security=True");
    --> string check = "SELECT autonr FROM auto WHERE autonr =@autonr";

    --> SqlCommand command1 = new SqlCommand(check, con);
    --> command1.Parameters.AddWithValue("@autonr", autonrTextBox.Text);

        con.Open();
        int auto = command1.ExecuteNonQuery();
        con.Close();

--> X - 1   MessageBox.Show(auto.ToString());

        if (auto > 0)
        {
            try
            {
                con.Open();

                using (SqlCommand command = new SqlCommand("DELETE FROM auto WHERE autonr =" + autonrTextBox.Text, con))
                {
                    command.ExecuteNonQuery();
                }

                con.Close();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
            }
        }
        else
        {
            MessageBox.Show("Het opgegeven autonr komt niet voor in de database. controleer deze.");
        }
    }
}

Try casting your autonrTextBox.Text to an int when setting it as the parameter value. — krillgar
– krillgar, Commented Dec 15, 2016 at 14:48

Joel Coehoorn · Accepted Answer · 2016-12-15 15:13:45Z

6

The ExecuteNonQuery() method doesn't work like you think it does. The return value for this method is the number of rows changed, not anything from the result set. SELECT queries don't change rows, so -1 is the expected result. 0 rows would imply a WHERE clause that matched no rows in an UPDATE, DELETE, or INSERT. -1 is used to indicate a different situation... either a statement that doesn't change rows or a rollback. Check the remarks section in the documentation for the method.

You want to use the ExecuteScalar() method instead.

int auto = -1;
using (var con = new SqlConnection(@"Data Source=DESKTOP-RSEBNR7;Initial Catalog=AudiDealer;Integrated Security=True"))
using (var cmd = new SqlCommand("SELECT autonr FROM auto WHERE autonr =@autonr", con))
{
    cmd.Parameters.Add("@autonr", SqlDbType.Int).Value = int.Parse(autonrTextBox.Text);
    con.Open();
    auto = (int)cmd.ExecuteScalar();
}

Finally... why check before deleting? This is just wasteful. Just issue the DELETE statement. There's no need to do a SELECT first. Your try/catch and the if() checks already handle situations where the record doesn't exist just fine.

int autonr = 0;
if (!int.TryParse(autonrTextBox.Text, autonr))
{
    MessageBox.Show("Waarschuwing u kunt geen auto verwijderen indien er GEEN autonr is ingevuld");
}
else
{
    try 
    {
        using (var con = new SqlConnection(@"Data Source=DESKTOP-RSEBNR7;Initial Catalog=AudiDealer;Integrated Security=True"))
        using (var cmd = new SqlCommand("DELETE FROM auto WHERE autonr = @autonr;", con))
        {
            cmd.Parameters.Add("@autonr", SqlDbType.Int).Value = autonr;
            con.Open();
            int result = cmd.ExecuteNonQuery();
            if (result <= 0)
            {
                 MessageBox.Show("Het opgegeven autonr komt niet voor in de database. controleer deze.");
            }
        }
    }
    catch (SystemException ex)
    {
        MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
    }
}

edited Dec 15, 2016 at 15:13

answered Dec 15, 2016 at 14:52

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

2 Comments

Max Clasener Over a year ago

Thank you so much!!

Max Clasener Over a year ago

how can i do this for a string type?

Kim Hoang · Accepted Answer · 2016-12-15 14:53:52Z

0

Please use ExecuteScalar, ExecuteNonQuery will not return the result.

answered Dec 15, 2016 at 14:53

Kim Hoang

1,3689 silver badges24 bronze badges

Comments

Tzach Oren · Accepted Answer · 2016-12-15 14:54:55Z

0

ExecuteNonQuery return only the the row that was change/add/remove if you want to know how many you have use in the query Count and get the rows'number

SELECT Count(*) as CountAutonr  FROM auto WHERE autonr =@autonr

and then you will get the from the CountAutonr the number of Rows

answered Dec 15, 2016 at 14:54

Tzach Oren

10611 bronze badges

Comments

Héctor Espí Hernández · Accepted Answer · 2016-12-15 14:57:24Z

0

There're many things wrong in that piece of code, I really recommend you to encapsulate those database queries inside a business class that will connect to the database, retrieve the data and return as a DAO object... but that won't answer your question.

The issue is in the select command execution, ExecuteNonQuery is meant for executing UPDATE, INSERT and DELETE statements, returning the number of affected rows:

con.Open();
**int auto = command1.ExecuteNonQuery();**
con.Close();

You must use ExecuteReader method to retrieve the SELECT results as explained in the following article:

Retrieving Data Using a DataReader

answered Dec 15, 2016 at 14:57

Héctor Espí Hernández

90010 silver badges11 bronze badges

Comments

Alex · Accepted Answer · 2016-12-15 15:03:08Z

0

The problem is in command1.ExecuteNonQuery() which returns the number of modified rows. Your query doesn't modify anything but only reads data from database, so the return value will be always -1.

So use ExecuteScalar instead - it will return your autonr value. Just remember to check it for null and cast it to correct type:

int auto = 0;
object result = command1.ExecuteScalar();
if (result != null)
    auto = (int)result;

edited Dec 15, 2016 at 15:03

answered Dec 15, 2016 at 14:53

Alex

1,4519 silver badges18 bronze badges

Collectives™ on Stack Overflow

C# select from SQL Server database

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related