asp.net Oracle TEMP table woes

[Phil Short via DotNetMonster.com]

I'm having trouble using a temp table in Oracle. Here's my code:

Dim myConn As OleDbConnection
Dim strconn As String = Session.Item("optSrcDBConnect")

Dim dcSQL As OleDbCommand
Dim strSQL As String
Dim drSQL As OleDbDataReader

WriteLog("Open connection")
Try
myConn = New OleDbConnection(strconn)
myConn.Open()
Catch ex As Exception
WriteLog("Error opening connection " & vbCrLf & ex.Message)
Exit Sub
End Try

Try
strSQL = "DROP TABLE TEMPODSWIP"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.ToString & " " & ex.Message)
' Exit Sub
End Try

Dim trSQL As OleDbTransaction
trSQL = myConn.BeginTransaction
Try
strSQL = "CREATE GLOBAL TEMPORARY TABLE TEMPODSWIP ON COMMIT
PRESERVE ROWS AS SELECT /*+ ORDERED */ RMAM.RMA_NBR AS RMAM_RMA_NBR,
RMAD.LN_NBR AS RMAD_LN_NBR, SORD.ORD_NBR AS SORM_ORD_NBR, SORD.LN_NBR AS
SORD_LN_NBR, RMAD.OUT_PART_NBR, SORD.PRODUCT_ID, RMAD.OUT_TRACE_ID,
RMAD.DT_RECEIVED, SORD.DT_CREATED, ZORD.ORD_NBR AS ZORD_ORD_NBR,
ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR FROM RMAM, RMAD, SORD,
ZORD WHERE RMAM.RMA_NBR = RMAD.RMA_NBR AND RMAD.RMA_NBR=SORD.ORD_NBR AND
SORD.PRODUCT_ID=ZORD.PART_NBR AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR AND
SORD.REC_CD='DT' AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' AND TRIM
(SORD.CNCL_ITM_RSN_CD) Is Null AND RMAD.DT_RECEIVED Is Not Null AND TRIM
(SORD.INVOICE_ID) is null AND ZORD.PART_NBR Not Like '*-R *' AND
ZORD.SERV_ORDER='Y'"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.Transaction = trSQL
dcSQL.ExecuteNonQuery()
trSQL.Commit()
WriteLog(strSQL)
Catch ex As Exception
trSQL.Rollback()
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
Exit Sub
End Try

Try
strSQL = "COMMIT WORK"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
Exit Sub
End Try

Try
strSQL = "SELECT /*+ ORDERED */ RMAM.RMA_NBR, SORD.LN_NBR,
TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR),
SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE,
ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, OORD.ACTUAL_OPEN_DATE FROM
TEMPODSWIP, RMAM, RMAD, SORD, ZORD, OORD WHERE TEMPODSWIP.RMAM_RMA_NBR =
RMAM.RMA_NBR And TEMPODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR And
TEMPODSWIP.RMAD_LN_NBR = RMAD.LN_NBR AND TEMPODSWIP.SORM_ORD_NBR =
SORD.ORD_NBR AND TEMPODSWIP.SORD_LN_NBR = SORD.LN_NBR AND
TEMPODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR AND ZORD.ORD_NBR=OORD.ORD_NBR AND
ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR"
Dim daOrders = New OleDbDataAdapter(strSQL, strconn)
'WriteLog(txtSpecificQuery.Text)
If myDS.Tables.Contains("SpecificQuery") Then myDS.Tables
("SpecificQuery").Dispose()
daOrders.fill(myDS, "SpecificQuery")
WriteLog(strSQL & vbCrLf & myDS.Tables("SpecificQuery")
..Rows.Count & " rows returned", EventLogEntryType.Information)
Catch ex As Exception
WriteLog("Error processing SQL" & vbCrLf & strSQL & vbCrLf &
ex.Message)
End Try
Try
strSQL = "DROP TABLE TEMPODSWIP"
dcSQL = New OleDbCommand(strSQL, myConn)
dcSQL.ExecuteNonQuery()
WriteLog(strSQL)
Catch ex As Exception
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf &
ex.Message)
End Try

Session.Item("myDS") = myDS
Radiobutton8.Checked = True
DataGrid2.CurrentPageIndex = 0
DataGrid2.DataSource = myDS.Tables("SpecificQuery")
DataBind()

The problem is that the commit doesn't appear to be working (despite trying
it two different ways!). I get a temp table, but no data. Very occasionally
I do get data, but I have to idea why!

I basically need to create a temp table to use as a "root" for a series of
related queries, all going off at different tangents in the database from
this subset of about 2,000 records out of 30,000.

Anyone know where my data is going?

 

[Phil Short via DotNetMonster.com]

Thanks everyone, I have sorted it myself

My problem (obvious now) is that the DataAdapter creates a new connection.
The temp table data is connection dependant, so obviously the table
definition persists, but the temp table for the new connection created by
the DataAdapter is empty!

I've pulled the data using a DataReader instead, then converted that to a
DataSet.