Click here to Skip to main content
15,917,795 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello

Please help me. I tried to update column between two dates. The system commit the method. But it does not take change in the database.

This is the code.


C#
public string AlterValid(DateTime startDate, DateTime endDate)
 {  
 OleDbConnection conn = new OleDbConnection("My Db setup");
 string status = null;
 bool setToFalse = false;  

 string updateSql = "UPDATE Watchreceipt SET valid = @valid WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";  
 
conn.Open();
 OleDbTransaction sqltrans = conn.BeginTransaction();
  
 try
 {
 OleDbCommand updateCmd = conn.CreateCommand();
 updateCmd.CommandText = updateSql;
  
 updateCmd.Parameters.AddWithValue("@startDate", startDate);
 updateCmd.Parameters.AddWithValue("@endDate", endDate);
 updateCmd.Parameters.AddWithValue("@DriverIDFK", UserMapper.driverNumber);
  
 updateCmd.Transaction = sqltrans;
  
 updateCmd.Parameters.Add("@valid", OleDbType.Boolean);
 updateCmd.Parameters["@valid"].Value = setToFalse;
  
 updateCmd.ExecuteNonQuery();
 sqltrans.Commit();  
 
status = "Ok";
 }
 catch (OleDbException odbe)
 {
 sqltrans.Rollback();
 System.Windows.Forms.MessageBox.Show("Rolled back\n" + odbe.Message);
 }
 catch (Exception ex)
 {
 System.Windows.Forms.MessageBox.Show("Systemfejl\n" + ex.Message);
 }
 finally
 {
 conn.Close();
 }  
 return status; 
}


Please help
Posted
Updated 27-Jul-12 9:37am
v2
Comments
Jyoti Khanchandani 27-Jul-12 9:44am    
Did you check your SQL query by directly executing into SQL console?

As I said to your other instance of this question - you need to use the command you created outside the try block, not create a new one inside it - the parameters do not transfer themselves.

Please do not re-post questions because you don't feel you got a fast enough reply - we all have other jobs to do, and it is rude. Remember we don't get paid for this! :laugh:
 
Share this answer
 
this is the solution

C#
public string UpdateValid(DateTime startDate, DateTime endDate)
        {
          
            OleDbConnection conn = new OleDbConnection("my db path"));
            string status = null;
            
            string updateSql = "UPDATE Watchreceipt SET valid = false WHERE Dato BETWEEN @startDate AND @endDate AND DriverIDFK = @DriverIDFK";

            conn.Open();
            OleDbTransaction sqltrans = conn.BeginTransaction();
            OleDbCommand updateCmd = conn.CreateCommand();
            updateCmd.CommandText = updateSql;
            
            updateCmd.Parameters.AddWithValue("@startDate", startDate);
            updateCmd.Parameters.AddWithValue("@endDate", endDate);
            updateCmd.Parameters.AddWithValue("@DriverIDFK", UserMapper.driverNumber);

            updateCmd.Transaction = sqltrans;

            try
            {
                
                updateCmd.ExecuteNonQuery();
                sqltrans.Commit();
                
                status = "Ok";
            }
            catch (OleDbException odbe)
            {
                sqltrans.Rollback();
                System.Windows.Forms.MessageBox.Show("Rolled back\n" + odbe.Message);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Systemfejl\n" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return status;
        }


I changed the SQL statement and deleted some code inside the try block.
Thanks!!
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900