CODEWITHSUNDEEP
c#sql-server
Max Clasener
Max Clasener

Reputation: 49

C# select from SQL Server database

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.");
        }
    }
}

Upvotes: 0

Views: 5493

Answers (5)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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));
    }
}

Upvotes: 6

Alex
Alex

Reputation: 1461

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;

Upvotes: 0

H&#233;ctor Esp&#237; Hern&#225;ndez
H&#233;ctor Esp&#237; Hern&#225;ndez

Reputation: 900

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

Upvotes: 0

Tzach Oren
Tzach Oren

Reputation: 106

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

Upvotes: 0

Kim Hoang
Kim Hoang

Reputation: 1368

Please use ExecuteScalar, ExecuteNonQuery will not return the result.

Upvotes: 0

Related Questions