#13 Error when using MpmTransaction with SQL Server database

closed
Bengt Gunne
None
5
2005-05-24
2005-04-19
Sean Flook
No

I am trying to use a MpmTransaction with a group of
update stored procedures. This code worked for SQL
Server before converting the code to use Mimer.

Now when I call the update on the dataadapter I get the
following message:

"The transaction objectis not associated with the
connection object".

The same code works fine with Oracle

Discussion

1 2 > >> (Page 1 of 2)
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    Could you upload the relevant parts of your code so we can
    see what call sequences are made (i.e. from transaction start
    to the failing statement)?

    Bengt

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    Here are the lines of code as requested.

    conn.Open();
    trans = conn.BeginTransaction();
    try
    {
    StreetData.Create(dsStreets.Tables["STREET"], out
    newUSRN);

    ....

    public void Create(DataTable dt, out int iUSRN)
    {
    iUSRN = Convert.ToInt32(dt.Rows[0]["USRN"].ToString
    ());

    //
    // If there are no records to insert return
    //
    if (dt.Select(null, null,
    DataViewRowState.Added).GetLength(0) == 0)
    return;
    // end if

    try
    {
    daStreet.InsertCommand.Connection = conn;
    daStreet.InsertCommand.Transaction = trans;

    daStreet.InsertCommand.Parameters.Clear();
    //Set the parameters
    // SF IT2838++
    daStreet.InsertCommand.Parameters.Add
    ("@DESCRIPTION", MpmType.VarChar,
    100, "DESCRIPTION");
    daStreet.InsertCommand.Parameters.Add
    ("@ST_VER_NO", MpmType.SmallInt, 1, "ST_VER_NO");
    daStreet.InsertCommand.Parameters.Add("@USRN",
    MpmType.Int, 4, "USRN");
    if (conn.DataSourceDescriptor.DbmsType ==
    MpmDbmsTypes.Oracle)
    daStreet.InsertCommand.Parameters.Add
    ("@ST_CUR", MpmType.SmallInt, 2, "ST_CUR");
    else
    daStreet.InsertCommand.Parameters.Add
    ("@ST_CUR", MpmType.Boolean, 1, "ST_CUR");
    // end if
    daStreet.InsertCommand.Parameters.Add
    ("@START_XREF", MpmType.Int, 4, "START_XREF");
    daStreet.InsertCommand.Parameters.Add
    ("@END_XREF", MpmType.Int, 4, "END_XREF");
    daStreet.InsertCommand.Parameters.Add
    ("@START_YREF", MpmType.Int, 4, "START_YREF");
    daStreet.InsertCommand.Parameters.Add
    ("@END_YREF", MpmType.Int, 4, "END_YREF");
    daStreet.InsertCommand.Parameters.Add
    ("@ST_TOL", MpmType.Int, 4, "ST_TOL");
    daStreet.InsertCommand.Parameters.Add
    ("@ST_REF_TYPE", MpmType.SmallInt,
    1, "ST_REF_TYPE");
    daStreet.InsertCommand.Parameters.Add
    ("@LOC_REF", MpmType.Int, 4, "LOC_REF");
    daStreet.InsertCommand.Parameters.Add
    ("@TOWN_REF", MpmType.Int, 4, "TOWN_REF");
    daStreet.InsertCommand.Parameters.Add
    ("@COUNTY_REF", MpmType.Int, 4, "COUNTY_REF");
    daStreet.InsertCommand.Parameters.Add
    ("@AUTHORITY_REF", MpmType.Int,
    4, "AUTHORITY_REF");
    daStreet.InsertCommand.Parameters.Add
    ("@ST_SUB_TYPE", MpmType.Int, 4, "ST_SUB_TYPE");
    daStreet.InsertCommand.Parameters.Add
    ("@ALIAS_DESCRIPTION", MpmType.NVarChar,
    100, "ALIAS_DESCRIPTION");
    daStreet.InsertCommand.Parameters.Add
    ("@ALIAS_LOC", MpmType.NVarChar, 100, "ALIAS_LOC");
    daStreet.InsertCommand.Parameters.Add
    ("@ALIAS_TOWN", MpmType.NVarChar,
    100, "ALIAS_TOWN");
    daStreet.InsertCommand.Parameters.Add
    ("@ALIAS_COUNTY", MpmType.NVarChar,
    100, "ALIAS_COUNTY");
    if (conn.DataSourceDescriptor.DbmsType ==
    MpmDbmsTypes.Oracle)
    daStreet.InsertCommand.Parameters.Add
    ("@bNeverExport", MpmType.SmallInt,
    2, "NEVEREXPORT"); //SF IT2419
    else
    daStreet.InsertCommand.Parameters.Add
    ("@bNeverExport", MpmType.Boolean,
    1, "NEVEREXPORT"); //SF IT2419
    // end if
    SetDoTransProcess(conn, daStreet.InsertCommand);
    MpmParameter USRNParam =
    daStreet.InsertCommand.Parameters.Add("@Return_USRN",
    MpmType.Int, 4);
    USRNParam.Direction = ParameterDirection.Output;
    MpmParameter ResultParm =
    daStreet.InsertCommand.Parameters.Add("@errMsg",
    MpmType.VarChar, 250);
    // SF IT2838--
    ResultParm.Direction = ParameterDirection.Output;

    daStreet.Update(dt.Select(null, null,
    DataViewRowState.Added));

    This is then the line that has the exception on it.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    Have you set the transaction property on the
    daStreet.SelectCommand.Transaction property?

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    I am not using the SelectCommand on the daStreet at this
    point, I am using the InsertCommand and this is the line o
    code

    daStreet.InsertCommand.Transaction = trans;

    Which is after the line where I assign the connection object.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    I tried to make a reproducer for your problem. If the
    SelectCommand was set I got a similar error which I could
    only resolve by setting the Transaction property on the
    SelectCommand.

    When SelectCommand was null I could not get my code to
    fail.

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    I see what you mean now. I have now done the following
    code:

    //Create Select MpmCommand
    cmdSelect = new MpmCommand(); //SF IT2838
    cmdSelect.CommandType =
    CommandType.StoredProcedure;
    cmdSelect.Connection = conn;
    cmdSelect.Transaction = trans; //SF IT2838

    //Create Update MpmCommand
    cmdUpdate = new MpmCommand(); //SF IT2838
    cmdUpdate.CommandType =
    CommandType.StoredProcedure;
    cmdUpdate.CommandText = StoredProcedureName
    ("DAL_UpdateStreet", conn); //SF IT2838
    cmdUpdate.Connection = conn;
    cmdUpdate.Transaction = trans;

    //Create Insert MpmCommand
    cmdInsert = new MpmCommand(); //SF IT2838
    cmdInsert.CommandType =
    CommandType.StoredProcedure;
    cmdInsert.CommandText = StoredProcedureName
    ("DAL_InsertStreet", conn); //SF IT2838
    cmdInsert.Connection = conn;
    cmdInsert.Transaction = trans;

    //Create Delete MpmCommand
    cmdDelete = new MpmCommand(); //SF IT2838
    cmdDelete.CommandType =
    CommandType.StoredProcedure;
    cmdDelete.CommandText = StoredProcedureName
    ("DAL_DeleteStreet", conn); //SF IT2838
    cmdDelete.Connection = conn;
    cmdDelete.Transaction = trans;

    //Create Street DataAdapter
    daStreet = new MpmDataAdapter(); //SF IT2838
    daStreet.SelectCommand = cmdSelect;
    daStreet.UpdateCommand = cmdUpdate;
    daStreet.InsertCommand = cmdInsert;
    daStreet.DeleteCommand = cmdDelete;

    But I am still getting the error. Is this what you meant or was
    it something else?

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    • assigned_to: nobody --> mimerbg
     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    Yes, that is what I meant.

    I have added a test to the MpmTest suite. It is called
    F15_DataAdapterTransaction in file F_FuncTests.cs.

    Am I doing anything different than what you are doing?

    If you browse the file in
    http://cvs.sourceforge.net/viewcvs.py/mimerpm/MpmSource/M
    pmTests/F_FuncTests.cs it is revision 1.10 (sometimes it
    takes a while before sourceforge updates the web with check
    in files).

     
  • Sean Flook
    Sean Flook
    2005-04-20

    • assigned_to: mimerbg --> nobody
     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    I see what you mean now. I have now done the following
    code:

    //Create Select MpmCommand
    cmdSelect = new MpmCommand(); //SF IT2838
    cmdSelect.CommandType =
    CommandType.StoredProcedure;
    cmdSelect.Connection = conn;
    cmdSelect.Transaction = trans; //SF IT2838

    //Create Update MpmCommand
    cmdUpdate = new MpmCommand(); //SF IT2838
    cmdUpdate.CommandType =
    CommandType.StoredProcedure;
    cmdUpdate.CommandText = StoredProcedureName
    ("DAL_UpdateStreet", conn); //SF IT2838
    cmdUpdate.Connection = conn;
    cmdUpdate.Transaction = trans;

    //Create Insert MpmCommand
    cmdInsert = new MpmCommand(); //SF IT2838
    cmdInsert.CommandType =
    CommandType.StoredProcedure;
    cmdInsert.CommandText = StoredProcedureName
    ("DAL_InsertStreet", conn); //SF IT2838
    cmdInsert.Connection = conn;
    cmdInsert.Transaction = trans;

    //Create Delete MpmCommand
    cmdDelete = new MpmCommand(); //SF IT2838
    cmdDelete.CommandType =
    CommandType.StoredProcedure;
    cmdDelete.CommandText = StoredProcedureName
    ("DAL_DeleteStreet", conn); //SF IT2838
    cmdDelete.Connection = conn;
    cmdDelete.Transaction = trans;

    //Create Street DataAdapter
    daStreet = new MpmDataAdapter(); //SF IT2838
    daStreet.SelectCommand = cmdSelect;
    daStreet.UpdateCommand = cmdUpdate;
    daStreet.InsertCommand = cmdInsert;
    daStreet.DeleteCommand = cmdDelete;

    But I am still getting the error. Is this what you meant or was
    it something else?

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    • assigned_to: nobody --> mimerbg
     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    You sent a duplicate of your previous message...

    I need to be able to reproduce your problem to be able to
    help you. Since I am only seeing parts of your code and that
    part looks ok I thought it would be a good idea if you could
    help me repeat the problem!

    Please take a look at F15_DataAdapterTransaction and get
    back to me!

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    Sorry about the duplicate message, but I was trying to get
    the file, but I can not see it in CVS yet.

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Source File

     
    Attachments
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    As I still can not see your file I have attached my source file.
    The method that is being called is Create (line 2826)

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-20

    Logged In: YES
    user_id=895705

    Every time you call BeginTransaction you get a new instance
    of the MpmTransaction class. So the assignement you do in
    the constructor:

    cmdSelect.Transaction = trans; //SF IT2838

    is not the transaction instance that is active when you call
    Update. In SaveType21 add the line:

    daASD.SelectCommand.Transaction = trans;

    and check if that does not help.

     
  • Sean Flook
    Sean Flook
    2005-04-20

    Logged In: YES
    user_id=684148

    Sorry, I gave you the wrong method that I am testing. I am
    using the Create method (line 2847) and the first call is to
    the Street.Create. I have added

    daStreet.SelectCommand.Transaction = trans;

    to this method, but I still get the exception.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    Logged In: YES
    user_id=895705

    I cannot see this in your source...

    You have several commands attached to your data adapter
    and you should assign the transaction property on all of them.

     
  • Sean Flook
    Sean Flook
    2005-04-21

    Logged In: YES
    user_id=684148

    Sorry I should have given you the line number (Street.Create
    starts on line 1282)

    I have tried setting the transaction property for all the
    commands as shown below:

    daStreet.InsertCommand.Connection = conn;
    daStreet.InsertCommand.Transaction = trans;
    daStreet.SelectCommand.Transaction = trans;
    daStreet.UpdateCommand.Transaction = trans;
    daStreet.DeleteCommand.Transaction = trans;

    But I am still getting the error.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    Logged In: YES
    user_id=895705

    Can you look at my test code now?

     
  • Sean Flook
    Sean Flook
    2005-04-21

    Modified F_Funcs.cs

     
    Attachments
  • Sean Flook
    Sean Flook
    2005-04-21

    Logged In: YES
    user_id=684148

    Yes I can now see it. I have modified it so that it follws my
    code as close as I can get it.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    Logged In: YES
    user_id=895705

    After a few modifications I have run the test. Works fine! I
    have attached the file.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

     
    Attachments
  • Sean Flook
    Sean Flook
    2005-04-21

    Logged In: YES
    user_id=684148

    My code is still giving me the error. I have made a small
    modification to the file as I do not create thestored
    procedures on the fly and I open the connection just before
    setting the transaction and calling the create method for the
    Street object.

    Additionally, I do not know if it has an influence, but the
    stored procedure I am calling has a lot more parameters and
    2 of them are ouput parameters.

     
1 2 > >> (Page 1 of 2)