Dataset query not acting right.

[.net Noob]

I have an application that uses bound controls on the form. I have a dataset associated with my form and have a custom query written for one of my tables that is supposed to get two fields in the database and order them by their ID in descending order. However, when I run the program, the bound controls do not contain the last record, but the first.

Why is this happening?

Noob

 

[jmcilhinney]

There's really not much we can do with the information you provided. What does the query look like? How are you calling it? Exactly what data does the result set contain? What order is it in?

 

[.net Noob]

Here's the query:

SELECT StoryID, Title, Author, Genre, DateStarted, DateFinished, NumChapters, NumPages, PubID
FROM Admin
ORDER BY StoryID DESC


I am calling it from a table adapter. The StoryID field is my primary key, and I want to load the dataset so that the last record in the table is the first one in the dataset so it displays in the form with the bound controls. That is why I used DESC in my query. Enough info?

 

[jmcilhinney]

Is that the primary or a secondary query on the TableAdapter? How are you calling it in code? What does the whole DataTable look like, i.e. bound to a DataGridView?

 

[.net Noob]

If you mean is it the first one listed in the tableadapter, then no. It is also not bound to a DataGridView

Here is the form load sub. I call my custom query first:

Private Sub MyStories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.AdminTableAdapter.GetLastRec(Me.MyStoriesDataSet.Admin)
For Each row As DataRow In Me.AdminTableAdapter.GetData
comboOpenStory.Items.Add(row("Title"))
Next
End Sub

 

[jmcilhinney]

There are a couple of non-critical issues with that. First, "GetLastRec" is a very bad name for that query because that is not what it does. It doesn't just get the last record; it gets all the records, so the name should reflect that.

Second, while it's not essential, it's a good idea to stick to the convention if using "Fill" and "GetData" as prefixes for your additional queries. The default query for the table adapter is, as you can see, exposed via two methods: Fill and GetData. The difference between the two is that Fill populates an existing DataTable while GetData creates, populates and returns a new DataTable. If you add another query then you have the choice of exposing it by either or both of those two types of methods. Whichever you choose, they should start with "Fill" and "GetData" for clarity. If, for instance, your query was to get only the records that had a specific value for a ParentID column then the methods would be called "FillByParentID" and "GetDataByParentID". In your case, the only distinguishing feature of the query is that the data is in descending order, so the name(s) should reflect that. Your one method populates an existing DataTable so the name should start with "Fill" and reflect the order of the data, e.g. "FillDescending". If you were to include a method that returned a DataTable too then that would be "GetDataDescending".

Now, on to the issue itself. The problem is that you are getting the data twice: once in descending order and then once in ascending order. You ignore the first result set and use the second to populate your control. Here's your code:

Private Sub MyStories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.AdminTableAdapter.[COLOR="#008000"]GetLastRec(Me.MyStoriesDataSet.Admin)[/COLOR]

    For Each row As DataRow In Me.AdminTableAdapter.[COLOR="#FF0000"]GetData[/COLOR]
        comboOpenStory.Items.Add(row("Title"))
    Next
End Sub

The green part is executing your additional query, retrieving the data in descending order and populating the specified DataTable. You then ignore that DataTable. The red part is executing the default query, retrieving the data in ascending order and returning it in a new DataTable. It's that new DataTable that you are looping through so its the contents of that DataTable, in asending order, that you see in your control. Now do you see why i asked to see your code... twice! When we can see what you're actually doing, rather than a description of what you think you're doing, the issue is often obvious. That code should be like this:

Private Sub MyStories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.AdminTableAdapter.GetLastRec(Me.MyStoriesDataSet.Admin)

    For Each row As DataRow In [B][U]Me.MyStoriesDataSet.Admin[/U][/B]
        comboOpenStory.Items.Add(row("Title"))
    Next
End Sub

so that you're getting the data only once and looping through the DataTable you populated in the first place. Better still, don;t use a loop and bind the data:

Private Sub MyStories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.AdminTableAdapter.GetLastRec(Me.MyStoriesDataSet.Admin)

    With comboOpenStory
        .DisplayMember = "Title"
        .ValueMember = "StoryID"
        .DataSource = Me.MyStoriesDataSet.Admin
    End With
End Sub

That said, if you are using a typed DataSet and adding the DataSet and TableAdapter in the designer, you really should be binding in the designer too.

 

[.net Noob]

Thanks for the advice and the code. I'll try it and let you know. I have written vb6 applications, but not .net, and my vb6 apps did not use bound forms. This is all new.

 

[.net Noob]

OK, I updated my code and it is working, now. I didn't bind the data to the control like you suggested in the second code snippet, because the control in question is a toolstrip combobix and it has no data parameters.

Thanks again for the help. I am sure I'll be asking a lot more (detailed) questions.

 

[jmcilhinney]

I didn't bind the data to the control like you suggested in the second code snippet, because the control in question is a toolstrip combobix and it has no data parameters.

That is just a host for a regular control. It has a ComboBox property that returns a reference to the regular ComboBox it hosts, which does have binding properties.