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();

                }




            }

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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.