DataAdapter.Fill fails with different queries in the same code block

calibreinc
calibreinc used Ask the Experts™
on
Simple code block is executed in the click event of a button press.  The query is built dynamically based on the selected value of a radio button, a multi-line text box and a range set by two list boxes.  If the query is being executed against the same data, then no trouble.  However, if the other option is selected and thus the source data, the Fill method of the DataAdapter fails and throws an error in Oracle stating an "invalid number".  The code block redefines the DataAdapter with each button press.  The fill is a simple query from a temp table that is created, and does exist at the time of the failure.  It's just that the structure of the temp table changes.  See my code below, and let me know what am I overlooking, or if it's something that can't be done:

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
        Dim DocTable, NIINList, NewQuery, BadQuery As String
        Dim DocAdapter As New OracleDataAdapter
        Dim DmdDataSet, RtnDataSet As New DataSet
       Session("User") = "temp.table"
        TempTable = Replace(Session("User"), ".", "")
        DocTable = Replace(Session("User"), ".", "") & "Docs"
        If Len(Trim(Me.txtNIINList.Text)) > 9 Then
            NIINList = Replace("('" & Replace(Replace(Me.txtNIINList.Text, vbCrLf, "','"), " ", ""), "-", "")
            NIINList = Mid(NIINList, 1, Len(NIINList) - 3) & "')"
            NewQuery = "Create Table " & DocTable & " as Select *  from " & IIf(rblDemandReturn.SelectedValue = "Demands", "vw_demand_document", "vw_returns_docs") & " where fy " & _
                IIf(dlFYEnd.SelectedValue = dlFYStart.SelectedValue, "= " & CStr(dlFYEnd.SelectedValue), " between " & CStr(IIf(dlFYEnd.SelectedValue > dlFYStart.SelectedValue, dlFYStart.SelectedValue, dlFYEnd.SelectedValue)) & _
                    " and " & CStr(IIf(dlFYEnd.SelectedValue < dlFYStart.SelectedValue, dlFYStart.SelectedValue, dlFYEnd.SelectedValue))) & _
                    " and niin in " & NIINList
            armylogweb.ConnectionParams.setparams()
            Dim ConnectionString As String = "Data Source=" + armylogweb.ConnectionParams.datasource & _
                                            ";User ID=" + armylogweb.ConnectionParams.username & _
                                            ";Password=" + armylogweb.ConnectionParams.password
            orcl_conn = New OracleConnection(ConnectionString)
            orcl_conn.Open()
            DocAdapter.SelectCommand = _
            New OracleCommand(NewQuery, orcl_conn)
            Try
                DocAdapter.SelectCommand.ExecuteNonQuery()
            Catch BadNIIN As OracleException
                BadQuery = "Drop table " & DocTable
                DocAdapter.SelectCommand = _
                New OracleCommand(BadQuery, orcl_conn)
                DocAdapter.SelectCommand.ExecuteNonQuery()
                DocAdapter.SelectCommand = _
                New OracleCommand(NewQuery, orcl_conn)
                DocAdapter.SelectCommand.ExecuteNonQuery()
            End Try
            NewQuery = "Select * from " & DocTable & " order by fy desc, niin, cac_r,tpsn_r,dodaac"
            DocAdapter.SelectCommand = _
            New OracleCommand(NewQuery, orcl_conn)
            DocAdapter.SelectCommand.ExecuteNonQuery()
            DocAdapter.Fill(IIf(rblDemandReturn.SelectedValue = "Demands", DmdDataSet, RtnDataSet))
            Session("Documents") = IIf(rblDemandReturn.SelectedValue = "Demands", DmdDataSet, RtnDataSet)
            orcl_conn.Close()
            Dim dvBatch As New DataView(IIf(rblDemandReturn.SelectedValue = "Demands", DmdDataSet, RtnDataSet).Tables(0))
            Me.gvBatchQuery.DataSource = dvBatch
            Me.gvBatchQuery.DataBind()
            Me.gvBatchQuery.Visible = True
            Me.btnXLSB.Visible = True
        End If
    End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Dynamically creating tables is typically a bad design in Oracle.  It is rarely necessary.

My guess from the error you provided is you are dealing with dates incorrectly or some explicit data conversion problems.

Can you post the actual generated SQL statements that causes the error?
Most Valuable Expert 2012
Top Expert 2014
Commented:
Why do you have both executenonquery and fill?

DocAdapter.SelectCommand.ExecuteNonQuery()
DocAdapter.Fill(IIf(rblDemandReturn.SelectedValue = "Demands", DmdDataSet, RtnDataSet))


You only need to call Fill which will then execute the query.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Just noticed another possible issue:
In more recent versions of Oracle you have a recyclebin.  Dropping tables does not actually get rid of them from the data dictionary.

They need to be purged.  You can force a purge at the time of a drop by adding, well, purge:
drop some_table purge;

If you insist on creating dynamic temp tables, I would suggest an immediate purge when dropping them.

Author

Commented:
1)  I've removed the redundant query execution prior to the fill, no change.
2)  However, I also removed the building of the temp table and just ran the query.  The temp table was just a "shortcut" to cut down on having to re-query when user wanted to dump all the results to a spreadsheet (1,000's of rows).

Your suggestions forced me to see things differently and as a result the problem was solved.

Thanks for helping me see clearly!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>The temp table was just a "shortcut" to cut down on having to re-query when user wanted to dump all the results

Glad the suggestion helped.

Just wanted to add additional things to think about:

Issuing a select to populate a temp table then query against the temp table is almost always more resource intensive than just querying the base table to start with.

Oracle is pretty good about keeping data blocks in memory so subsequent queries against the same data it pretty quick and doesn't involve disk io.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial