Avatar of calibreinc
calibreinc
Flag for United States of America asked on

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

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
Visual Basic.NETOracle Database.NET ProgrammingASP.NET

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

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.
calibreinc

ASKER
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!
slightwv (䄆 Netminder)

>>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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes