ExecuteReader problem

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Here is my code:

Dim i As Integer
Dim oledcstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\UKC Application Services\UKCJuniors\UKCJuniors\Juniors.mdb"
Dim thisQuery As String = "SELECT YTDPts, FirstName, LastName, City, State
FROM JuniorMaster WHERE (Region = 1) ORDER BY YTDPts"
Dim olecn As New OleDbConnection(oledcstring)
Dim oledc As New OleDbCommand(thisQuery, olecn)
Dim oledr As OleDbDataReader
Dim resultView As DataView
Dim resultTable As DataTable = New DataTable("Results")
Dim resultRow As DataRow

'Make the table to store the results
Dim Place, FirstName, LastName, City, State, YTDPts As DataColumn
Place = New DataColumn()
Place.DataType = System.Type.GetType("System.String")
Place.Caption = "Place"
Place.ColumnName = "Place"
FirstName = New DataColumn()
FirstName.DataType = System.Type.GetType("System.String")
FirstName.Caption = "FirstName"
FirstName.ColumnName = "First Name"
LastName = New DataColumn()
LastName.DataType = System.Type.GetType("System.String")
LastName.Caption = "LastName"
LastName.ColumnName = "Last Name"
City = New DataColumn()
City.DataType = System.Type.GetType("System.String")
City.Caption = "City"
City.ColumnName = "City"
State = New DataColumn()
State.DataType = System.Type.GetType("System.String")
State.Caption = "State"
State.ColumnName = "State"
YTDPts = New DataColumn()
YTDPts.DataType = System.Type.GetType("System.Decimal")
YTDPts.Caption = "YTDPts"
YTDPts.ColumnName = "YTD Points"
resultTable.Columns.Add(Place)
resultTable.Columns.Add(FirstName)
resultTable.Columns.Add(LastName)
resultTable.Columns.Add(City)
resultTable.Columns.Add(State)
resultTable.Columns.Add(YTDPts)

' run the command for Region 1
olecn.Open()
oledr = oledc.ExecuteReader()

For i = 0 To 49
oledr.Read()
resultRow = resultTable.NewRow()
resultRow("Place") = i + 1
resultRow("FirstName") = oledr("FirstName")
resultRow("LastName") = oledr("LastName")
resultRow("City") = oledr("City")
resultRow("State") = oledr("State")
resultRow("YTDPts") = oledr("YTDPts")
Next
resultView.Table = resultTable
dgResults.DataSource = resultView
oledr.Close()
olecn.Close()

On the line of oledr=oledc.ExecuteReader I get the following error: An
unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in
system.data.dll.

I have been searching through articles and help files and cannot find
anything. If the above coding is sloppy, I apologize in advance for being a
beginner.

Thanks for any help provided.

Brad
 
Hi Brad,

You sure that select statament is correct?
Other than that, you are not checking if .Read() method returns true
(success).
BTW, why don't you use rather an adapter and its Fill method?

Dim adapter as OleDbDataAdapter = new OleDbDataAdapter(thisQuery, olecn)
Dim resultTable As DataTable = New DataTable("Results")
adapter.Fill(resultTable) ' no need to create table columns
 
Well, the data adapter will return several hundred records and I want to
read jus the top 50. I tested this with using a Parameterized Query and
that works fine, but I just want it to sort by YTDPts and then display only
the top 50.

If you know of a way I don't, please let me know. I will also check the
Select statement, but I built it with the Wizard and then checked my syntax
against that and it seemed fine.

I didn't check for true and I know that any parameter the user selects will
return true. Should I check for true anyway?

By the way, do you live in this newsgroup? LOL. But your advice is very
valuable.

Brad
 
Hi Brad,

Brad said:
Well, the data adapter will return several hundred records and I want to
read jus the top 50. I tested this with using a Parameterized Query and
that works fine, but I just want it to sort by YTDPts and then display only
the top 50.
Click to expand...

The Fill method has an overload that limits the number of returned values.
Also, the best way to limit the number or returned rows is TOP directive:
SELECT TOP xx YTDPts, FirstName, LastName ....
This is by far the fastest.
If you know of a way I don't, please let me know. I will also check the
Select statement, but I built it with the Wizard and then checked my syntax
against that and it seemed fine.
Click to expand...

When exception occurs open Locals window - you'll see the exception listed
at top.
Expand it and you'll get more info on exception.
I didn't check for true and I know that any parameter the user selects will
return true. Should I check for true anyway?
Click to expand...

To be on the safe side... Plus, you might put some try/finally blocks into.
By the way, do you live in this newsgroup? LOL. But your advice is very
valuable.
Click to expand...

Ha, no, I just post here and there. ;-)
 
I alos just found the problem. The parameter that I was passing to Region
was a numeric value. The actual adapter's column is set to string.

Brad
 
Brad,

When writing this sort of code you save yourself a lot of head-scratching if
you surroung the ExecuteReader() call in a try block and then provide a
catch block that displays or logs the actual exception with error message
and stack trace so that you can determine the exact error. It's also often
useful to display the SQL statement and/or parameter names and values.

--Bob
 
A new "funny" problem with this now. I will research it because I am in the
process of learning and the best times are those when I solve the
hurdle-problem I am at.

The datareader is now working, but the table only displays 7 records. I
manually counted the records and there should be 20. I think I would rather
have no records display than some and not all.

I will also try the TOP directive and see how that goes. I definately need
to get the ADO Core reference book.

Thanks for the help.

Brad
 
Hi Brad,

Brad Allison said:
A new "funny" problem with this now. I will research it because I am in the
process of learning and the best times are those when I solve the
hurdle-problem I am at.
Click to expand...

Yup, the best way.
The datareader is now working, but the table only displays 7 records. I
manually counted the records and there should be 20. I think I would rather
have no records display than some and not all.
Click to expand...

What does it mean to you "display"? In a DataGrid?
I will also try the TOP directive and see how that goes.
Click to expand...

Try it, it is by far the fastest.

I definately need
to get the ADO Core reference book.
Click to expand...

You might start by reading .net help files - it is a lot of (an
understandable) info in there.
Thanks for the help.
Click to expand...

Sure.
 
I am taking the table created from the data reader and binding it to a data
grid. Is there another process? Also, this is an interesting situation. I
want to bind the top 50 to the data grid, but if there are ties (say the
49th, 50th and 51st places have 100 points) I need to display more than 50.
So that is also something else I need to work out.
 
Remember that each time you use Read, a row is processed and deleted from
the stream--it can't be "read" again. I made this same mistake lately by
unintentionally including an extra Read in the logic.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I have read in a Do While loop. I wonder if that is the problem, except
that I am assigning the data from the rows after the read to a table. I
only have the one line of code to do the read.
 
If you're creating a DataTable, you should not be using a DataReader--you
should be using the DataAdapter.Fill method.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I thank everybody for all of their help. I have taken a large challenge (to
me), worked through it with help and it now works! What I did is trashed
the datareader/table methods and I am now filling a dataadapter and using a
dataset. I am using a GetRow() method to read each row and then assign that
to schema of the dataset.

At any rate, it is working for now. My next step is to evaluate a field
(YTDPts) of the previous record and the current record and if these are tied
(the same year-to-date points), then the placement has to match.

For instance:

Place Pts Name
1 100 afslkgjlkj
2 98 aytqwrlkjafh
3 96 afglkjafhl;kj
3 96 afhlkqrylkj
5 90 aflkjahflkj

and so on up to 50 records (may be more records if the 50th placements are
tied).

It will be fun.
 
Back
Top