Maintain transaction with dataretrieve and inserting

Question

0.00/5 (No votes)

See more:

Hi I have a problem when I'm trying transation I am getting error please try to help me

Exception is coming Exception is
System.InvalidOperationException: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

tell me how I can do in best way

SqlCommand  _sqlICommmand = new SqlCommand();

           SqlConnection _sqlIConnection = new SqlConnection(_connectionString);
           _sqlICommmand.Connection = _sqlIConnection;

           if (_sqlIConnection.State == ConnectionState.Closed)
               _sqlIConnection.Open();
               _sqlICommmand.Transaction = transaction;
               transaction = _sqlIConnection.BeginTransaction();
               if (hiddenForRecoPlanType != "")
               {
                   if (Convert.ToDecimal(hiddenForRecoPlanType) != a)
                   {
                       try
                       {
                           dstAssociatedUsersForFeed =GetAllSubscribedUsersToFeed(feedId);
                           dstCompany = GetCompanyNames(txtAddReco.Trim());
                           //_sqlICommmand.Transaction = transaction;

                           _sqlICommmand.CommandType = CommandType.StoredProcedure;
                           _sqlICommmand.CommandText = "SP_InsertIndividualPredictionsDetails";
                           _sqlICommmand.Parameters.Add(new SqlParameter("@feedId", feedId));
                           _sqlICommmand.Parameters.Add(new SqlParameter("@predictionMessage", dstCompany.Tables[0].Rows[0][1].ToString()));
                           _sqlICommmand.Parameters.Add(new SqlParameter("@market", Convert.ToString(market)));
                           _sqlICommmand.Parameters.Add(new SqlParameter("@recommendationPrice", hiddenForRecoPlanType));
                           _sqlICommmand.Parameters.Add(new SqlParameter("@expectedPrice", targateprice));
                           _sqlICommmand.Parameters.Add(new SqlParameter("@predictionPostDate", DateTime.Now.ToString()));
                           if (_sqlIConnection.State == ConnectionState.Closed)
                               _sqlIConnection.Open();

                             _sqlICommmand.ExecuteNonQuery();

                                   DataSet dstGetAllFeedStatusWhileRecoPosting = new DataSet();
                                   DataSet _dstsellerPayment = new DataSet();
                                   dstGetAllFeedStatusWhileRecoPosting = GetAllFeedStatusWhileRecoPosting(feedId);
                                   if (_sqlICommmand.Parameters.Contains("@feedId"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@feedId");
                                   }
                                   if (_sqlICommmand.Parameters.Contains("@predictionMessage"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@predictionMessage");
                                   }
                                   if (_sqlICommmand.Parameters.Contains("@market"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@market");
                                   }
                                   if (_sqlICommmand.Parameters.Contains("@recommendationPrice"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@recommendationPrice");
                                   }
                                   if (_sqlICommmand.Parameters.Contains("@expectedPrice"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@expectedPrice");
                                   }
                                   if (_sqlICommmand.Parameters.Contains("@predictionPostDate"))
                                   {
                                       _sqlICommmand.Parameters.RemoveAt("@predictionPostDate");
                                   }


                                   int rowcount = dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows.Count;
                                   if (dstGetAllFeedStatusWhileRecoPosting != null && rowcount > 0)
                                   {
                                       for (int datacount = 0; datacount < rowcount; datacount++)
                                       {
                                           _sqlICommmand.CommandType = CommandType.StoredProcedure;
                                           _sqlICommmand.CommandText = "SP_InsertFeedIncome";
                                           _sqlICommmand.Parameters.Add(new SqlParameter("@feedId", feedId));
                                           _sqlICommmand.Parameters.Add(new SqlParameter("@scheme", dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][2].ToString()));
                                           _sqlICommmand.Parameters.Add(new SqlParameter("@userID", Convert.ToInt32(dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][0])));
                                           _sqlICommmand.Parameters.Add(new SqlParameter("@recosGot", 1));
                                           _sqlICommmand.Parameters.Add(new SqlParameter("@recosTotal", Convert.ToInt32(dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][1])));
                                           if (_sqlIConnection.State == ConnectionState.Closed)
                                               _sqlIConnection.Open();
                                           _sqlICommmand.ExecuteNonQuery();
                                           //_dstsellerPayment = InsertFeedIncome( dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][2].ToString(), Convert.ToInt32(dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][0]), 1, Convert.ToInt32(dstGetAllFeedStatusWhileRecoPosting.Tables[0].Rows[datacount][1]));
                                       }
                                   }

                                   DataSet dstGetSubscriptionStatusChange = new DataSet();
                                   dstGetSubscriptionStatusChange = GetSubscriptionStatusChange(feedId);
                                   transaction.Commit();
                                   result="susscess";
                       }
                       catch (SqlException se)
                       {
                           transaction.Rollback();
                       }
                       catch (Exception ex)
                       {
                           transaction.Rollback();
                       }

Posted 27-May-10 3:30am

vaibhav_all

Updated 27-May-10 3:38am

Johnny J.

v3

Add a Solution

Comments

Johnny J. 27-May-10 9:39am

Inserted Pre tags - maybe you can do it yourself the next time?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Henry Minute · Answer 1 · 2010-05-27T04:10:00

When you get an error that you have never encountered before, one of the first things you should do is to Google the error message.

I did it on your behalf and the first hit I got was to this[^] on MSDN.Social.

Now it may not have the answer to your problem but if it doesn't I'd be willing to bet that one of the other hits will.

Go on, give it a try. :)