C# + SQLServer

[hg_fs2002]

sqlCmd = new SqlCommand();
 sqlCmd.Connection = sqlCon;
 sqlCmd.CommandText = "update trainn set Adultno=Adultno-1 where ID=@pram1 and    
 adult=@pram2 and Adultno>0";
 sqlCmd.Parameters.AddWithValue("@pram1", textBox3.Text);
 sqlCmd.ExecuteNonQuery();
 sqlCon.Close();

The code is working properly when there is an update,but when it comes to more than
one I don't know what to do? The following code is the one which I thought it may
work, but actually it doesn't make sense! any suggestion?

sqlCmd = new SqlCommand();
  sqlCmd1 = new SqlCommand();
                
  sqlCmd.Connection = sqlCon;
  sqlCmd1.Connection = sqlCon;

  sqlCmd.CommandText = "update trainn set Adultno=Adultno-1 where ID=@pram1 and   
  adult=@pram2 and Adultno>0";
  sqlCmd1.CommandText = "update trainn set childno=childno-1 where ID=@pram1 and  
  child=@pram2 and childno>0";
  sqlCmd.Parameters.AddWithValue("@pram1", textBox3.Text);
  sqlCmd.ExecuteNonQuery();
  sqlCmd1.ExecuteNonQuery();
  sqlCon.Close();

[BestJewSinceJC]

What happens if you execute those SQL statements from within SQL Server itself; does anything get updated? Are the results what you expect? Alternatively, have you tried running the C# code and using SQL Server Profiler to see what SQL statements get executed on the Server (you can run profiler from within SQL Server management studio)? Sometimes it is your SQL that is incorrect and not the C# code.

Example of using multiple sql statements in one query
http://www.java2s.com/Code/CSharp/Database-ADO.net/ExecutemultipleSQLstatementsusingaSqlCommandobject.htm

[Mitja Bonca]

You can do:

string query1 = @"update trainn set Adultno=Adultno-1 where ID=@pram1 and   
  adult=@pram2 and Adultno>0";
string query2 = @"update trainn set childno=childno-1 where ID=@pram1 and  
  child=@pram2 and childno>0";
string[] queriesArray = new string { query1, query2 };
foreach(string query in queriesArray)
{    
     sqlCmd = new SqlCommand();
     sqlCmd.Connection = sqlCon;
     sqlCom.CommandText = query;
     if(sqlCon.State != ConnectionState.Open)
          sqlCon.Open();
     sqlCmd.Parameters.AddWithValue("@pram1", textBox3.Text);
     sqlCmd.ExecuteNonQuery();
}
sqlCon.Close();

[hg_fs2002]

Thanks for your suggestions but still it doesn't work.There isn't any error but when I use the code in try{ } catch{ }, it always return the try while none of the where statements are True.

[hg_fs2002]

I found out that my problem is in understanding the meaning of try-catch,because the transactions in my DataBase are according to sql statements and work properly but if forexample the where statement of an update is false, I want to show in messagebox that it's not possible and I placed this message in catch while it's not true.
Now how can I show the message "It works!" when the updates perform accurately or vice versa?

[Mitja Bonca]

You can create your own boolean variable, which will be set accordinglly:

string query1 = @"update trainn set Adultno=Adultno-1 where ID=@pram1 and adult=@pram2 and Adultno>0";
            string query2 = @"update trainn set childno=childno-1 where ID=@pram1 and child=@pram2 and childno>0";
            string[] queriesArray = new string { query1, query2 };
            bool bAllOK;
            foreach (string query in queriesArray)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.Connection = sqlCon;
                sqlCom.CommandText = query;
                if (sqlCon.State != ConnectionState.Open)
                    sqlCon.Open();
                sqlCmd.Parameters.AddWithValue("@pram1", textBox3.Text);
                try
                {
                    sqlCmd.ExecuteNonQuery();
                    bAllOK = true;
                }
                catch
                { 
                //show your error message
                    bAllOK = false;
                    break;
                }
            }
            if (bAllOK)
                MessageBox.Show("All ok");

[sharadp]

ExecuteNonQuery() Method Always Returns int. If ExecuteNonQuery() returns value grater than zero you should understand there is successful update. for eg;

int reslt= sqlCmd.ExecuteNonQuery();
if(reslt>0)
{
// numbers of rows updated=reslt
}
else
{
//No row updated
}

[hg_fs2002]

Thanks a lot,I've done it.