We help IT Professionals succeed at work.
Get Started

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

1,057 Views
Last Modified: 2014-05-02
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE