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

                }




            }

Edited by Mike_: n/a

2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by thines01
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?

Edited by thines01: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.