1,105,333 Community Members

Global Temp Table issue

Member Avatar
Mike_
Newbie Poster
1 post since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I am attempting to create a global temp table to be used between two sessions being called from a webservice.

When I attempt to access the temp table from the second session it can not be found. The reason I am using a global temp table is because the initial session is created on a before event handler which calls the service and then from an after event handler.

I have checked that the table is still available after the initial session closes, and I am still able toe query the data.

Any help would be greatly appreciated!

public void SetUpdateHolds(string SOPnumber, int SOPType, string companyId, string compDatabaseName)
            {
                
                
                SqlConnection conn = new SqlConnection(connString.StrEPConnString);
                conn.Open();
                

                try
                    {


                        string sql = " USE [" + compDatabaseName + "]" +
                                     "CREATE TABLE ##HOLDS (HOLDID varchar (20)) ";
                                     
                        SqlCommand command = conn.CreateCommand();
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        
                        sql = 
                                "INSERT INTO ##HOLDS " +
                                "SELECT PRCHLDID from SOP10104 WHERE SOPNUMBE = '" + SOPnumber + "' AND DELETE1 = '0' AND  SOPTYPE ='" + SOPType + "'";


                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        command.Dispose();
                        conn.Close();
                        conn.Dispose();
          
                    }
                    catch (SqlException e)
                    {

                        util.Log("Error creating temp table after canceling RemoveHold " + SOPnumber + e.Message + e.StackTrace);
                        conn.Close();
                        conn.Dispose();
                       
                    }

            }

            public void UpdateHolds(string SOPnumber, int SOPType, string companyId, string compDatabaseName)
            {

                Collection<CCTransaction> list = new Collection<CCTransaction>();
                SqlConnection conn = new SqlConnection(connString.StrEPConnString);
                conn.Open();
                string result = "";


                try
                {

                    string sql = "USE [" + compDatabaseName + "] UPDATE SOP10104 SET DELETE1 = 0 WHERE PRCHLDID = (SELECT  HOLDID FROM ##HOLDS)";
                                 
                           

                    SqlCommand command = conn.CreateCommand();
                    command.CommandText = sql;
                    SqlDataReader reader = command.ExecuteReader();
                    command.ExecuteNonQuery();
                 
                           
                    sql = "DROP TABLE ##HOLDS"; 

                    
                    command.ExecuteNonQuery();
                    command.Dispose();
                    conn.Close();
                    conn.Dispose();

                }
                catch (SqlException e)
                {

                    util.Log(result + "   Error  updating PRCHLDID in Table SOP10104 after canceling RemoveHold " + SOPnumber + e.Message + e.StackTrace);
                    conn.Close();
                    conn.Dispose();

                }




            }
Member Avatar
thines01
Postaholic
2,420 posts since Oct 2009
Reputation Points: 389 [?]
Q&As Helped to Solve: 413 [?]
Skill Endorsements: 10 [?]
Team Colleague
Featured
 
0
 

You need to call new on your new SQL commands.
Also, don't you get an Exception or an invalid state when the code hits the ExecuteReader followed by the ExecuteNonQuery?

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: