next/previous records (VS 2005)

Topic

New Reply

Am trying to move next/previous from a chosen record.
da, ds are dimmed as formwide

Public Sub MoveNext(ByRef sName As String, ByRef iID As Integer)
Dim dr As DataRow
da = New OleDbDataAdapter()
ds = New DataSet()
sName = Replace(sName, “‘”, “””)
cmd = New OleDbCommand(“SELECT * FROM Restaurants WHERE RestName = ‘” & sName & “‘” & _
” ORDER BY RestName”, cn)
da.SelectCommand = cmd
da.Fill(ds, “Restaurants”)
For i As Integer = 0 To ds.Tables(“Restaurants”).Rows.Count – 1 ‘returns current row
cR.RestaurantID = iID ‘cR is a class containing all restaurant info
dr = ds.Tables(“Restaurants”).Rows(0) ‘ dr contains all data in current row
Next
End Sub

Since the data in the current row can be extracted, I struggle with how to move the pointer in the table forward/backward.

Reply | Quote

Replies

I think this line is meant to contain i where you currently have :

dr = ds.Tables("Restaurants").Rows(0) ' dr contains all data in current row

Reply | Quote

Thanks for the response. Irrespective of (0) or (i) the current row is returned with the data. How to move to the next/previous record?

Reply | Quote

That’s what you’re telling it to do. When you move to a row, it becomes the current row. However, all your code does is loop through the dataset and assign each row to the object variable. It doesn’t do anythihng else. Are you trying to navigate in a form, find a specific record for some purpose, or what?

Reply | Quote

Re: next/previous records (VS 2005)
Thanks for the response.

I have a form that is filled with name, address, city, state, zip and comments. On the form are two arrows representing next and previous records. So, from the record that is showing on the form, the name is used to query the db. Once the record is found, I want to move to the next/previous record.

Reply | Quote

Is your background in Access by any chance? That’s where I’m used to seeing that kind of navigation.

How is the data bound to your form? And where is the data–Excel, Access, SQL Server, text, xml, or what?

Reply | Quote

I don’t know .Net, but here’s a general idea: if the ds object is available for the life of the form, and if you place a row index somewhere in your form, then when the user clicks an arrow you could increment or decrement the row index (as applicable) and then use that updated row index to retrieve the desired record.

Reply | Quote

In .Net there are a gazillion ways to do almost anything and the devil is in the details. The specifics depend on how the form is bound to the data. The basic idea is to move the pointer in the binding context to the desired index so that controls bound to the fields in the dataset will reflect that row. I can’t tell if the form is bound to CR or to the dataset.

Reply | Quote

Thanks for the responses.

Not using binding. and yes, Access is the db.

These are form statements.
Private cn As OleDb.OleDbConnection = _
New OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|restaurants.mdb”)
Private cmd As OleDbCommand = New OleDbCommand
Private da As OleDb.OleDbDataAdapter
Private ds As DataSet
Private sSql As String
Private cR as cRestaurant ‘cRestaurant is a class

What I want to accomplish is to increment/decrement the row by one from the current row. And this returns the current row data:
dr = ds.Tables(“Restaurants”).Rows(0)

Is binding necessary to increment/decrement?

Reply | Quote

OK, let’s back up. When you open the form, can you see any data? If so, then you’ve used some kind of databinding. Did you drag a tableadapter onto the form or a dataadapter? In other words, how did you tell the form it should display data from this dataset?

Reply | Quote

A listbox is loaded on startup thru a collection. When the item is clicked in the listbox, the data from that record is loaded into textboxes. In the classical sense, that is not data binding, which was stated previously.

Public Sub LoadItems()
c = New Collection
lstDisplay.Items.Clear() ‘lstbox displays names
c = dataMgr.Display© ‘datamgr tier to connect to db
CR = New cRestaurant ‘restaurant class
For Each CR In c
lstDisplay.Items.Add(CR)
Next
c = Nothing
End Sub

Public Function Display(ByVal c As Collection) As Collection
Dim dr As DataRow
da = New OleDbDataAdapter()
ds = New DataSet()
c = New Collection
cmd = New OleDbCommand(“Select * From Restaurants ORDER BY RestName”, cn)
da.SelectCommand = cmd
da.Fill(ds, “Restaurants”)
Try
For Each dr In ds.Tables(“Restaurants”).Rows
cR = New cRestaurant
With cR
.RestaurantID = CInt(dr(“RestaurantID”))
.RestName = CStr(dr(“RestName”))
c.Add(cR, CStr(cR.RestaurantID))
End With
Next dr
cn.Close()
Return c
Catch ex As Exception
MessageBox.Show(ex.Message)
Return c
End Try
End Function

With that out of the way, or it should be, how to increment/decrement from this statement and get the data:

For i As Integer = 0 To ds.Tables(“Restaurants”).Rows.Count – 1 ‘returns current row

Reply | Quote

OK, you’ve answered the question about how the data is being loaded into the listbox. But exactly how are you loading it into the controls on the form? You say [indent]

---

the data from that record is loaded into textboxes

---

[/indent]. I’m asking how you’re presently trying to do that, because that’s where the issue lies.

Reply | Quote