Retrieving specific entries in .mdb

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am attempting to retrieve records from an .mdb file. But can only
retrieve the first entries of the table using the code below. How would I
code to get any entry I desired (2nd entry, 4th entry, etc.) ?

Code:
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Name\Desktop\History.mdb;")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select * from MainTable", cn)
dr = cmd.ExecuteReader
While dr.Read()
' loading data into TextBoxes by column index
TextBox1.Text = dr(0) 'Scrape
TextBox2.Text = dr(1) 'Amount
End While
Catch
End Try
dr.Close()
cn.Close()

Main Table is the name of my table, Scrape is the name of my first field,
Amount is the name of my second field. They are both number data types.
 
What makes you think that you are only retrieving 'the first entries(sic) of
the table'?

What is you interpretation of what is occurring in the:

While dr.Read()
...
End While

construct?
 
VB2003 or VB2005?

What do you mean "any entry" -- you mean any row?
Because in the example below, you will end up with
the textboxes populated with the last row read.

If you mean any column, you can do this:

While dr.Read()
For i as Integer = 0 to dr.Items.Count - 1
Debug.Print("Item " & i " & = " & dr.Items(i).ToString)
Next i
'if you know the column names, you can do this
' although it is not the most performant
Debug.Print("Scrape = " & dr.Items("Scrape").ToString)
Debug.Print("Amount = " & dr.Items("Amount").ToString)
End While

Robin S.
 
Don't spoil my fun :)


RobinS said:
VB2003 or VB2005?

What do you mean "any entry" -- you mean any row?
Because in the example below, you will end up with
the textboxes populated with the last row read.

If you mean any column, you can do this:

While dr.Read()
For i as Integer = 0 to dr.Items.Count - 1
Debug.Print("Item " & i " & = " & dr.Items(i).ToString)
Next i
'if you know the column names, you can do this
' although it is not the most performant
Debug.Print("Scrape = " & dr.Items("Scrape").ToString)
Debug.Print("Amount = " & dr.Items("Amount").ToString)
End While

Robin S.
Click to expand...
 
I use VB 2003. By 'entry' I mean the data in the first column accompanied by
its corresponding data in the second column.

When I try to comple the code below, I am told 'items' is not a member of
....OleDbDataReader (dr).
 
Ever so sorry. When I posted that, your message hadn't come through my
newsreader yet. I might have posted it anyway. I'm a rebel!

Robin S.
--------------
 
Oops. Sorry. Try this.

For i as Integer = 0 to dr.ItemArray.Length - 1
Debug.Print("Item " & i & " = " & dr.ItemArray(i).ToString)
Next i

Robin S.
----------------------------
 
I find that when I execute this code, only the FIRST data entry appears,
regaurdless of what ( i ) is. Do I need to change the
While dr.Read()
End While
Construct to retrive any entry I chose? For example,
If this is what is inside my table
Scrape Amount
1 200
2 200
3 300
4 400
etc...

and I wanted the third entry, how would I choose to do that?
 
First of all John, humour us and try this:

Dim cn As OleDbConnection = Nothing
Dim dr As OleDbDataReader = Nothing

Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Name\Desktop\History.mdb;")
cn.Open()
Dim cmd As New OleDbCommand("select * from MainTable", cn)
dr = cmd.ExecuteReader
Console.WriteLine("Scrape Amount")
While dr.Read()
Console.WriteLine("{0,-10}{1}, dr("Scrape"), dr("Amount"))
End While
Catch _ex As Exception
Console.WriteLine(_ex.ToString)
Finally
If Not dr Is Nothing Then dr.Close()
If Not cn Is Nothing Then cn.Close()
End Try

If all is how you say it should be then you should get:

Scrape Amount
1 200
2 200
3 300
4 400
etc...

I suspect that either you were getting an exception (that you were ignoring)
or the data wass being returned in an order other than the order you
expected.

In SQL a rowset returned by a select statement is unordered unles you
explicitly order it. Unfortunately, the Jet engine tends to implicitly order
the results on some suitable index thus 'hiding' this aspect from the user.
It is best to NEVER assume that the rowset will be in any particular order
and to ALWAYS order it yourself, e.g.:

select * from MainTable order by Scrape

Let us know what results you get.

Now, in your original code, you are iterating through all the rows in the
returned rowset:

While dr.Read()
TextBox1.Text = dr(0) 'Scrape
TextBox2.Text = dr(1) 'Amount
End While

On each iteration the values TextBox1 and TextBox2 are overwritten by the
values from the current row. This means that when the end of the rowset is
reached, the values in TextBox1 and TextBox2 are the values from the LAST
row of the rowset. It is impossible for TextBox1 and TextBox2 to have the
values from the FIRST row unless some other condition occurs. One such
condition could be that the rowset is in a different order that what you
expect and the row that you expect to be FIRST is actually LAST.

Form your code, it is clear that you do not have Option Strict turned on. I
strongly recommend that you ensure that both Option Explicit and Option
Strict are turned on for all VB.NET projects. Doing so will 'save you from
yourself' in many ways.

One way is that the lines:

TextBox1.Text = dr(0) 'Scrape
TextBox2.Text = dr(1) 'Amount

would not have compiled without recoding as:

TextBox1.Text = CType(dr(0), String) 'Scrape
TextBox2.Text = Ctype(dr(1), String) 'Amount

or

TextBox1.Text = dr(0).ToString 'Scrape
TextBox2.Text = dr(1).ToString 'Amount

or something similar.
 
Stephany is right, of course.

I think John could also retrieve it as a datatable using an
OleDBDataAdapter and then access only the third row this way:

dt.Rows(2).Item("Scrape")
dt.Rows(2).Item("Amount")


In terms of not knowing which row actually has the data in
it, this could be considered a form of Russian roulette. :-)

However, he may just be trying to figure out how to read
data, and using this for an example.

Robin S.
-----------------------------------
 
Back
Top