filling a DataTable
-
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, any help would be grateful.
-
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, any help would be grateful.
I don't know if this will make any difference, but you would be better off using the SQLDataAdaptor, SQLCommand and SQLConnection objects to access SQL. They work better. If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850)
-
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, any help would be grateful.
I assume connDF is valid somewhere else And you are not even using sqlQuery. I believe that the table needs to be part of a dataset for the adapter to fill.
Dim ds As new DataSet Dim dt As DataTable dt.TableName="InvDate" ds.Tables.Add(dt) Dim connection as string = "Whatever your connection string" Dim conn As New OleDbConnection(connection) Dim adapter As New OleDbDataAdapter() adapter.SelectCommand = new OleDbCommand(CmdqueryInvoice, conn) adapter.Fill(ds.tables("InvDate")) Dim LoopRow As Data.DataRow For Each LoopRow In ds.tables("InvDate")Rows ..... NextIf that does not work look at how the dataadapter processes joins. It may be the adapter will create the joined tables in the dataset but it will not automatically set the primary keys for the relationship. You may need to create the table schemas in the dataset then fill it. too much trouble? return a datareader and create your own columns and insert the data. Or just loop thru the reader. -
I don't know if this will make any difference, but you would be better off using the SQLDataAdaptor, SQLCommand and SQLConnection objects to access SQL. They work better. If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850)
-
I assume connDF is valid somewhere else And you are not even using sqlQuery. I believe that the table needs to be part of a dataset for the adapter to fill.
Dim ds As new DataSet Dim dt As DataTable dt.TableName="InvDate" ds.Tables.Add(dt) Dim connection as string = "Whatever your connection string" Dim conn As New OleDbConnection(connection) Dim adapter As New OleDbDataAdapter() adapter.SelectCommand = new OleDbCommand(CmdqueryInvoice, conn) adapter.Fill(ds.tables("InvDate")) Dim LoopRow As Data.DataRow For Each LoopRow In ds.tables("InvDate")Rows ..... NextIf that does not work look at how the dataadapter processes joins. It may be the adapter will create the joined tables in the dataset but it will not automatically set the primary keys for the relationship. You may need to create the table schemas in the dataset then fill it. too much trouble? return a datareader and create your own columns and insert the data. Or just loop thru the reader.Thanks for your suggestion. I tried your recommendation, but I was getting the same results. I decided to set the date range higher in the sql query and placing the larger results into the dataset. It takes longer to load, but the overall results are much better. Thanks again for your suggestion.