ole db datareader help 1

[jeffmoore64]

Hi,
I'm trying to get that good old "movelast" functionality with the datareader in vb.net. I understand that this is a forward only dataset. What I need is the last record in my retrieved data set and then the one prior to it etc. I can find no fuctionality in the datareader that will allow my to do this.
Any thoughts would be graet.
Jeff


Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed

' Set up connection string
Dim ConnString As String = "Provider=IBMDA400;Data Source=JDEENT01; Default Collection=JRSLIB;User Id=xxxxx; Password=xxxxx;"

' Set up query string
Dim CmdString As String = "SELECT BFKLST FROM F55BOBFG"

'Declare Connection and DataReader variables
Dim Conn As OleDb.OleDbConnection = Nothing
Dim Reader As OleDb.OleDbDataReader = Nothing

Try
'Open Connection
Conn = New OleDb.OleDbConnection(ConnString)
Conn.Open()

'Execute Query
Dim Cmd As New OleDb.OleDbCommand(CmdString, Conn)
Reader = Cmd.ExecuteReader(CommandBehavior.SingleResult)
reader.

'Process The Result Set
While (Reader.Read())
===>
Console.WriteLine(Reader(0).ToString())

End While

Catch ex As Exception
Console.WriteLine("Error: {0}", ex)

Finally
'Close Connection
Reader.Close()
Conn.Close()

End Try

End Sub

 

[jebenson]

Use a dataadapter and a datatable instead of a datareader.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[ZmrAbdulla]

If you have an incremental ID column(Autonumber) then you can use TOP 2 Descending order in your SQL
"SELECT TOP 2 FROM ...."

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[jeffmoore64]

jebenson...
I'm a newbie on vb.net. Got any good code snippits to share?

zmrabdulla...
sorry no autonumber id column. I just need to do a movelast, read it, then delete it, then do a move movelast again.

 

[jebenson]

Something like this:

Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed

' Set up connection string
Dim ConnString As String = "Provider=IBMDA400;Data Source=JDEENT01; Default Collection=JRSLIB;User Id=xxxxx; Password=xxxxx;"

' Set up query string
Dim CmdString As String = "SELECT BFKLST FROM F55BOBFG"

'Declare Connection, DataAdapter and DataTable variables
Dim Conn As OleDb.OleDbConnection = Nothing
Dim da As Oledb.OledbDataAdapter
Dim dt As Datatable

Try
'Open Connection
Conn = New OleDb.OleDbConnection(ConnString)
Conn.Open()

'Execute Query
da = New Oledb.OledbDataAdapter(CmdString, Conn)
dt = New DataTable
da.Fill(dt)

'Process The Result Set
For r As Integer = 0 to dt.Rows.Count - 1

'this line writes the first field in each row to the console
Console.WriteLine(dt.rows(0).Item(0).ToString())

Next

Catch ex As Exception
Console.WriteLine("Error: {0}", ex)

Finally
'Close Connection
Conn.Close()

End Try

End Sub


You can also iterate through the DataTable in reverse:

For r = dt.Rows.Count - 1 To 0 Step -1
Console.WriteLine(dt.rows(0).Item(0).ToString())
Next



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Sorwen]

I agree with jebenson you should really use a DataAdapter and DataTable. Just go to msdn and do a search for DataAdapter.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jeffmoore64]

Thank you for the code. It really is a big help