datatable only working occasionally
-
I have a VB.Net windows app that retrieves data from 3 different tables. The SQL command uses a 3 table join. The results from the query is filled into a datatable and later populated into a combobox. The SQL uses the values from a date field inside a text field, for example, 01-15-2005. So, each time I submit the subroutine it executes the SQL command and VB.Net code to retrieve the required data. The date field changes according to the user’s needs. There’s no consistency when the error pops up. The error message I’m getting when it executes, da.Fill(dataTable), “There’s no row at position 0”,. The information I’m on the stack track is as follow: Calling SQLDisconnect --- OleDB UnInitialize not called!!! Connection Object destroyed If I copy the sql into a sql query tool it does retrieve the required data according to the date field. Here’s a sample of the subroutine: cmdQueryINVOICE = _ "SELECT PAYITEM.TRANSACT_NUMBER, " _ & "PAYITEM.ACCOUNT_NUMBER, " _ & "PAYITEM.PAY_DATE, " _ & "LSEINVH.INVOICE_NUMBER " _ & "FROM LSEINVH, PAYITEM, TRANSHDR " _ & "WHERE " _ & " LSEINVH.INVOICE_NUMBER > '" & 75000 _ & "' AND LSEINVH.STATUS <> '" & sStatus _ & "' AND LSEINVH.INVOICE_NUMBER = TRANSHDR.INVOICE_NUMBER " _ & " AND PAYITEM.PAY_DATE > '" & InvoiceDates.StartDate _ & "' AND PAYITEM.PAY_DATE < '" & InvoiceDates.EndDate _ & "' AND PAYITEM.COMPANY = '" & sCompany _ & "' AND PAYITEM.TRANSACT_NUMBER = TRANSHDR.TRANS_NUMBER order by PAYITEM.PAY_DATE desc " Dim sqlQuery As OleDbCommand = connDf.CreateCommand Dim dtInvDate As New DataTable() Dim iInvCnt, x As Integer Dim sUnitQuery As String Dim daInv As New OleDbDataAdapter() Try daInv = New OleDbDataAdapter(cmdQueryINVOICE, connDf) daInv.Fill(dtInvDate) Dim LoopRow As Data.DataRow Dim test As String = dtInvDate.Rows.Item(0).ToString For Each LoopRow In dtInvDate.Rows cboInvoice.Items.Add(LoopRow.Item("INVOICE_NUMBER").ToString + " " + LoopRow.Item("PAY_DATE")) Next Catch e As Exception Dim errMsg As String = e.Message Finally connDf.Close() End Try Thanks, for any help!
-
I have a VB.Net windows app that retrieves data from 3 different tables. The SQL command uses a 3 table join. The results from the query is filled into a datatable and later populated into a combobox. The SQL uses the values from a date field inside a text field, for example, 01-15-2005. So, each time I submit the subroutine it executes the SQL command and VB.Net code to retrieve the required data. The date field changes according to the user’s needs. There’s no consistency when the error pops up. The error message I’m getting when it executes, da.Fill(dataTable), “There’s no row at position 0”,. The information I’m on the stack track is as follow: Calling SQLDisconnect --- OleDB UnInitialize not called!!! Connection Object destroyed If I copy the sql into a sql query tool it does retrieve the required data according to the date field. Here’s a sample of the subroutine: cmdQueryINVOICE = _ "SELECT PAYITEM.TRANSACT_NUMBER, " _ & "PAYITEM.ACCOUNT_NUMBER, " _ & "PAYITEM.PAY_DATE, " _ & "LSEINVH.INVOICE_NUMBER " _ & "FROM LSEINVH, PAYITEM, TRANSHDR " _ & "WHERE " _ & " LSEINVH.INVOICE_NUMBER > '" & 75000 _ & "' AND LSEINVH.STATUS <> '" & sStatus _ & "' AND LSEINVH.INVOICE_NUMBER = TRANSHDR.INVOICE_NUMBER " _ & " AND PAYITEM.PAY_DATE > '" & InvoiceDates.StartDate _ & "' AND PAYITEM.PAY_DATE < '" & InvoiceDates.EndDate _ & "' AND PAYITEM.COMPANY = '" & sCompany _ & "' AND PAYITEM.TRANSACT_NUMBER = TRANSHDR.TRANS_NUMBER order by PAYITEM.PAY_DATE desc " Dim sqlQuery As OleDbCommand = connDf.CreateCommand Dim dtInvDate As New DataTable() Dim iInvCnt, x As Integer Dim sUnitQuery As String Dim daInv As New OleDbDataAdapter() Try daInv = New OleDbDataAdapter(cmdQueryINVOICE, connDf) daInv.Fill(dtInvDate) Dim LoopRow As Data.DataRow Dim test As String = dtInvDate.Rows.Item(0).ToString For Each LoopRow In dtInvDate.Rows cboInvoice.Items.Add(LoopRow.Item("INVOICE_NUMBER").ToString + " " + LoopRow.Item("PAY_DATE")) Next Catch e As Exception Dim errMsg As String = e.Message Finally connDf.Close() End Try Thanks, for any help!
You should tell us what the database is. There may be subtle differences between them. Your code is most insecure. It is the potential subject of a SQL Injection Attack. You should read this article about how to prevent them: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^] Is your connection open before calling the
Fillon the data adapter? If the connection is closed when you callFillthen the data adapter will open the connection, perform the action and then close the connection again. So theClosestatement in the finally block is redundant.
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
-
You should tell us what the database is. There may be subtle differences between them. Your code is most insecure. It is the potential subject of a SQL Injection Attack. You should read this article about how to prevent them: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^] Is your connection open before calling the
Fillon the data adapter? If the connection is closed when you callFillthen the data adapter will open the connection, perform the action and then close the connection again. So theClosestatement in the finally block is redundant.
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Thanks for your suggestion. I decided to retrieve the entire results from the query and fill the dataset, so I don't experience the problem. Thanks again for the recommendation of sql injection I'll look into to it, but this is a windows application I thought sql was only for web application?