Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
i can't get 2 query
just working string query
and query dont
 

        internal bool saveNewPayment(Guid ID, Guid creditID, decimal paymentAmount, DateTime dateTime)
        {
            bool flag = true;
            using (SqlConnection con = new SqlConnection(constring))
            {
                con.Open();
                SqlTransaction sqlTranc = con.BeginTransaction();
                SqlCommand com = con.CreateCommand();
                com.Transaction = sqlTranc;
 
                try
                {
                    string payAmount = paymentAmount.ToString(CultureInfo.InvariantCulture.NumberFormat);
 
                
                   string query = string.Format("INSERT INTO Payment (ID, CreditsID, Amount, PaymenDate)" + " VALUES ('{0}', '{1}', '{2}', '{3}')",
                     ID, creditID, payAmount, dateTime.ToString("MM/dd/yyyy"));
                    
                    com.CommandText = query;
                    com.ExecuteNonQuery();
 
                   query = string.Format("UPDATE Credits SET Balance = (Balance - {0}) WHERE ID = '{1}'", payAmount, creditID);
 
                    com.CommandText = query;
                    com.ExecuteNonQuery();
 
                   
                    sqlTranc.Commit();
 
                }
                catch (Exception)
                {
                    sqlTranc.Rollback();
                    flag = false;
                }
                finally
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Dispose();
                }
            }
            return flag;
 
        }
    }
  }
Posted 28-Nov-12 12:23pm
Comments
digimanus at 28-Nov-12 18:28pm
   
you do 2 commands over 1 statement. Second query will not work if it is first one. Than no entry in table.
Adam R Harris at 11-Dec-12 15:18pm
   
What is the value of creditId? What is the field type of ID on the Credits table? What command isn't running? - Does your insert run? - Does your update run?
Rohit Shrivastava at 11-Dec-12 16:53pm
   
what exception is coming and on which line, did not understand the question.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

since you use the con.BeginTransaction() before the beginning of statement the real insert will not complete until you make call to sqlTranc.Commit();
 
so its obvious that the creditID which you are trying to update is not available in the table and it failed to update.
move the Commit statement just after the insert is completed and try running the update query.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 Tadit Dash 350
2 Manas Bhardwaj 319
3 OriginalGriff 243
4 Sergey Alexandrovich Kryukov 205
0 Sergey Alexandrovich Kryukov 9,530
1 OriginalGriff 5,716
2 Peter Leow 4,320
3 Maciej Los 3,540
4 Abhinav S 3,373


Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid