CODEWITHSUNDEEP
c#sqlsql-server
Aravind Bharathy
Aravind Bharathy

Reputation: 1610

How to check whether a SQL query is successful with C#

I am new to C# and SQL. Now from a form I access a function in a class.

My code is

public void updateSupplierInformation(string id, string name, string balance, string place, string address, string phone, string bankname, string bankbranch, string accountno)
{
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        SqlCommand NewCmd = conn.CreateCommand();
        NewCmd.Connection = conn;
        NewCmd.CommandType = CommandType.Text;
        NewCmd.CommandText = " update supplier set " + " ID = " + "'" + id + "'" + " , NAME = " + "'" + name + "'" + " , BALANCE = " + "'" + balance + "'" + " , PLACE = " + "'" + place + "'" + "  , LOCATION = " + "'" + address + "'" + ",  PHONE = " + "'" + phone + "'" + " , BANK_NAME = " + "'" + bankname + "'" + " , BANK_BRANCH = " + "'" + bankbranch + "'" + ", ACCOUNT_NO = " + "'" + accountno + "'" + " where ID = " + "@id";
        NewCmd.Parameters.AddWithValue("@id",id);
        NewCmd.ExecuteNonQuery(); 
        conn.Close();
    }

Now if a record doesn't exist in the database with the given id the application stops immediately. How can I handle this? I want to show a message that the data entered is wrong and ask the user to enter another data

Upvotes: 15

Views: 72138

Answers (4)

Dilip Kumar Choudhary
Dilip Kumar Choudhary

Reputation: 469

Check the condition

int a=NewCmd.ExecuteNonQuery(); 
    if(a==0)
      //Not updated.
    else
      //Updated.

ExecuteNonQuery() returns an integer value.

Upvotes: 2

John the horn
John the horn

Reputation: 131

I know that there is already an answer posted but let`s try something else:

SqlConnection SQLConn = new SqlConnection("datahere");
SqlCommand SQLComm = new SqlCommand();
SQLcomm.Connection = SQLConn;
SQLConn.Open();
SQLComm.CommandText = "SQL statement goes here"
SqlDataReader dataReader = SQLComm.ExecuteReader();
dataReader.Read();
if(dataReader.HasRows == true){ //if it has rows do code
//do code
}
else{
// do other code
}

HasRows will return a bool value

Upvotes: 1

sreejithsdev
sreejithsdev

Reputation: 1210

ExecuteNonQuery() returns number of rows affected by an INSERT, UPDATE or DELETE statement.If you need to check sql exception you have to include a try catch statement in your function.

public void updateSupplierInformation(string id, string name, string balance, string place, string address, string phone, string bankname, string bankbranch, string accountno)
    {
       try
       {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        SqlCommand NewCmd = conn.CreateCommand();
        NewCmd.Connection = conn;
        NewCmd.CommandType = CommandType.Text;
        NewCmd.CommandText = " update supplier set " + " ID = " + "'" + id + "'" + " , NAME = " + "'" + name + "'" + " , BALANCE = " + "'" + balance + "'" + " , PLACE = " + "'" + place + "'" + "  , LOCATION = " + "'" + address + "'" + ",  PHONE = " + "'" + phone + "'" + " , BANK_NAME = " + "'" + bankname + "'" + " , BANK_BRANCH = " + "'" + bankbranch + "'" + ", ACCOUNT_NO = " + "'" + accountno + "'" + " where ID = " + "@id";
        NewCmd.Parameters.AddWithValue("@id",id);
        int a=NewCmd.ExecuteNonQuery(); 
        conn.Close();
        if(a==0)
          //Not updated.
        else
          //Updated.
        }
        catch(Exception ex)
         {
         // Not updated
         }
    }

Upvotes: 28

Jon Skeet
Jon Skeet

Reputation: 1500525

ExecuteNonQuery returns the number of rows affected - if it's 0, that means there were no matching rows to update. Of course, that's only if the update actually "works" in terms of not throwing an exception... whereas I suspect it's throwing an exception in your case, which probably isn't to do with the row not existing in the database. (It's possible that there's some code you haven't shown which does depend on the row existing, mind you.)

Additionally:

  • You should use parameterized SQL for all parameters rather than including the values directly in your SQL.
  • You should use using statements to dispose of resources reliably.
  • It looks like you're using a single connection - don't do that. Create (and dispose via using) a new connection each time you want to perform a database operation, and let the connection pool handle the efficiency
  • Work out why the application is just stopping. An exception is almost certainly being thrown, and it's really important that when that happens, you get the details of the exception. You may want to catch it and keep going (at some high level) depending on the exact context... but you should always at least end up logging it.

My guess (on a casual inspection) is that the problem is that your update statement tries to update the ID, which would presumably be read-only. But you'll find that out when you fix your exception handling.

Upvotes: 23

Related Questions