none
Object reference not set to an instance of an object with transaction RRS feed

  • General discussion

  • Using MSDE 2000.

    If I save the two datasets independantly, it works.

     

    cn = null;
    cn = new SqlConnection(cnStr);
    SqlCommand cmdMonthSet = new SqlCommand("SELECT * FROM MonthSet", cn);
    SqlDataAdapter daMonthSet = new SqlDataAdapter(cmdMonthSet);
    SqlCommandBuilder cbMonthSet = new SqlCommandBuilder(daMonthSet);


    SqlCommand cmdMonthSetHeader
    = new SqlCommand("SELECT * FROM MonthSetHeader", cn);
    SqlDataAdapter daMonthSetHeader = new SqlDataAdapter(cmdMonthSetHeader);
    SqlCommandBuilder cbMonthSetHeader = new SqlCommandBuilder(daMonthSetHeader);

    SqlTransaction trnMonthData;
    cn.Open();
    trnMonthData = cn.BeginTransaction();
    daMonthSet.UpdateCommand.Transaction = trnMonthData;
    daMonthSetHeader.UpdateCommand.Transaction = trnMonthData
    ;
    try
    {
       
    daMonthSet.Update(monthSet,"MonthSet");
       
    daMonthSetHeader.Update(header,"MonthSetHeader");
       
    trnMonthData.Commit();
    }
    catch(Exception ex)
    {
        trnMonthData.Rollback();
       
    return ex.Message;
    }
    finally
    {
       
    cn.Close();
    }

    return "";

     

    I have tried doing only one update at a time and they both return "Object reference not set to an instance of an object". The table names are correct.

    Nigel

     

    Thursday, July 3, 2008 12:33 PM

All replies

  • Does your code fail when you call Update methods fo DataAdapters? It looks like you are trying to call update on DataTatbles that are not initialized. Make sure that monthSet and header DataTables (or dataSets) are not null references.

     

    Friday, July 4, 2008 10:07 AM
    Moderator
  • I have replaced the above code with a (better?) example in my book  - see my earlier poat which you removed :-)

    Now no exception is returned, but no changes are made to the database.

    I only call the routine if both datasets have changes.

     

    Code is now:

    cn = null;

    cn = new SqlConnection(cnStr);

    SqlCommand cmdSet = new SqlCommand("SELECT * FROM MonthSet", cn);

    SqlDataAdapter daSet = new SqlDataAdapter(cmdSet);

    SqlCommand cmdHeader = new SqlCommand("SELECT * FROM MonthSetHeader", cn);

    SqlDataAdapter daHeader = new SqlDataAdapter(cmdHeader);

    SqlTransaction trnMonthData = null;

    cn.Open();

    trnMonthData = cn.BeginTransaction();

    cmdSet.Transaction = trnMonthData;

    daSet.UpdateCommand = cmdSet;

    cmdHeader.Transaction = trnMonthData;

    daHeader.UpdateCommand = cmdHeader;

    try

    {

        daSet.Update(monthSet, "MonthSet");

        daHeader.Update(header, "MonthSetHeader");

        trnMonthData.Commit();

    }

    catch(Exception ex)

    {

        trnMonthData.Rollback();

        return ex.Message;

    }

    finally

    {

        cn.Close();

    }

    return "";

     

    Until I get the transaction working, my (working ok) code at the client side is:

    if(dsMonthSet.HasChanges() && dsMonthSetHeader.HasChanges())

    {

        DataSet monthSet = dsMonthSet.GetChanges();

        DataSet header = dsMonthSetHeader.GetChanges();

        SaveMonthSetChanges(monthSet);  // Until transaction working

        SaveMonthSetHeaderChanges(header);  //  Until transaction working

    //  string result ="";

    //  try

    //  {

    //      result = rmMonthSets.SaveMonthSetTransaction(monthSet, header);

    //  }

     

    So there is nothing wrong with the datasets. Line from SaveMonthSetChanges is:

    numRows = da.Update(monthSet,"MonthSet");

     

    Nigel

    Friday, July 4, 2008 11:33 AM
  • From this and the other post, I still don't see where data is loaded/initialized; except on GetChanges, where you are creating the DataSet from the changes themselves.

     

    My second question: why you don't use only one DataSet with related DataTables?

     

    Bruno

     

    Sunday, July 6, 2008 11:52 PM
  • One more question. Are you sure that your code inside of "if" statement actually executed? If you do not get an exception, it might just mean you do not execute code at all. Can you debug the code and see if you really have any changes in your DataSets?

     

    Monday, July 7, 2008 9:49 AM
    Moderator
  • Apart from anything else, I modified the library code to:

    string xx;

    try

    {

        xx = daSet.Update(monthSet, "MonthSet").ToString();

        xx += " ";

        xx += daHeader.Update(header, "MonthSetHeader").ToString();

        trnMonthData.Commit();

    }

    catch(Exception ex)

    {

        trnMonthData.Rollback();

        xx += " ";

        xx += ex.Message;

    }

    finally

    {

         cn.Close();

    }

    return xx;

     

    This returns "1 1"

     

    So both data adapters return 1 row affected (corrrect) and no exception. No changes to database.

    Using da.Update without encapsulating them in a transaction works.

     

    I presume from your questions that the code looks ok. Have just upgraded from MSDE2000 to SQL 2005 Express, will see if that makes a change.

     

    Might have to relate the tables, but that isn't the point - transaction should work, if code is correct.

     

    Nigel

     

    Monday, July 7, 2008 5:32 PM