DataSet Reading Out of Order

[drdemp]

DataSet Filling Out of Order

I am currently designed a bowling statistics keeping program with essentially nested tables located in Microsoft Access.

Game -> Series -> Bowler -> Group

Each Game has a column called seriesID to say what series it is part of, directly associated with the primary key of Series, each series has a bowlerID and so on.

I am writing my own primary keys to ensure they match the row number regardless of any deleted rows, so to allow easy indexing.

The problem is, at random I start reading the rows of the table into a dataset out of order.

For instance, today I tried to read in 26 bowlers, and the dataset looked like this.

ds.rows(0-11) = table.rows(0-11) 'Good
ds.rows(12) = table.rows(24)
ds.rows(13) = table.rows(25)
ds.rows(14-25) = table.rows(12-23) 'All very bad

I have no idea what is causing this. And it's not always the center rows that get displaced. I've had other times where ds.rows(0) = table.rows(last) and ds.rows = table.rows(n - 1) for n = 1..last. It's all really random.

The code I am using to fill the dataset is the following.

Dim con as New OleDbConnection
con.ConnectionString = "database location"
con.Open()

Dim table As String = "Bowlers"
Dim da As New OleDbDataAdapter("SELECT * FROM " & table, con)

Dim ds As New DataSet
da.Fill(ds, table)

con.Close()

Is there a reason this shouldn't work, or would work how mine is sometimes?

It's a problem that comes up off and on, usually more so when I've added rows by hand to the database directly (I think), though I can't say that it hasn't happened when adding to the database in code. I have elimated the problem by erasing the table and starting over, but it usually pops up again later on.

I really need to solve this problem or my program can't be very reliable. If you don't understand why this is happening, any suggestions on simple fixes that might get around the problem rather than solve it?

Thanks

 

[Stonkie]

Usually, when you select rows from a table, they will be returned in the order they were inserted, but no DBMS is forced to follow this behavior. If you want to order rows, you will need to use the ORDER BY clause, specifying whichever column you want to order on.

This should solve your problem :

SELECT * FROM Bowlers ORDER BY BowlerId

While we are there though, you should consider using a strongly typed dataset. This is done by adding a Dataset item to your project. You may add tables by dragging them from the server explorer and let them create both the Datatables and TableAdapters.

Using this, you can use the designer to create custom methods for almost any SQL query you which to throw at the database, implement optimistic concurrency automatically and use the automatically generated extensions of DataSet, TableAdapter, DataTable and DataTableRow to access your data like public properties of these objects.

It is the recommended way of connecting to a database in .NET 2.0 and would allow an experienced programmer to build a form with a datagridView bound to a table of your database within minutes.

 

[drdemp]

Usually, when you select rows from a table, they will be returned in the order they were inserted, but no DBMS is forced to follow this behavior. If you want to order rows, you will need to use the ORDER BY clause, specifying whichever column you want to order on.

This should solve your problem :

SELECT * FROM Bowlers ORDER BY BowlerId

While we are there though, you should consider using a strongly typed dataset. This is done by adding a Dataset item to your project. You may add tables by dragging them from the server explorer and let them create both the Datatables and TableAdapters.

Using this, you can use the designer to create custom methods for almost any SQL query you which to throw at the database, implement optimistic concurrency automatically and use the automatically generated extensions of DataSet, TableAdapter, DataTable and DataTableRow to access your data like public properties of these objects.

It is the recommended way of connecting to a database in .NET 2.0 and would allow an experienced programmer to build a form with a datagridView bound to a table of your database within minutes.

I would do this, except the location to the database is not something I want to be constant, and as I am both reading and writing to the database, I can't make it a resource. Is there a way to connect via designer to a database who's location is determined at runtime?

I will try the Orderby tactic, as that sounds like what I was looking for, and hopefully will fix the problem.

The fact is I have struggled to work with databases for as long as I can remember, spending countless wasted hours trying to get the to load or save correctly, dealing with out of order issues, and all sorts of things. I've even been developing a DataManager class to use in all future programs for this purpose (though it still has all sorts of bugs)

Speaking of which, another major bug I'm having involves the Delete command as for the life of me I can't seem to get it to work. Do you have a good reference for getting that to work? Otherwise, here's the code I'm using to generate the delete command for the dataadapter and delete a row while fixing the primary key values to keep in order with no gaps. These two subs are within my DataManager class.

        Private Sub GenerateDeleteCommand()
            With ds.Tables(table).Columns(0)
                dCmd = New OleDbCommand("DELETE FROM Property WHERE " & .ColumnName & "=?", con)
                dCmd.Parameters.Add(New OleDbParameter(.ColumnName, DatabaseType(0), DatabaseDataSize(0), .ColumnName))
                da.DeleteCommand = dCmd
            End With
        End Sub

        Public Sub DeleteRow(ByVal rnum As Integer)
            With ds.Tables(table)
                For r As Integer = rnum + 1 To .Rows.Count - 1
                    .Rows(r).Item(0) -= 1
                Next r
                .Rows(rnum).Delete()
            End With
        End Sub

'included for reference for GenerateDeleteCommand()

        Private Function DatabaseType(ByVal c As Integer) As OleDbType
            With ds.Tables(table).Columns(c).DataType
                If .Name = "String" Then
                    Return OleDbType.Char
                ElseIf .Name = "Int32" Then
                    Return OleDbType.Numeric
                ElseIf .Name = "Boolean" Then
                    Return OleDbType.Boolean
                ElseIf .Name = "DateTime" Then
                    Return OleDbType.Date
                Else
                    MsgBox(.Name)
                End If
            End With
        End Function

        Private Function DatabaseDataSize(ByVal c As Integer) As Integer
            If DatabaseType(c) = OleDbType.Char Then
                Return ds.Tables(table).Columns(c).MaxLength
            Else
                Return 0
            End If
        End Function

Thanks.

EDIT: Order By solved the problem. I love how simple the solution ended up being and how I couldn't find that despite hours of searching on Google with poor word choices. I should have joined these threads ages ago. Thanks a lot.

 

[Stonkie]

The tables work like a cache of the actual table on the server. That way, if you tell it

For Each row As DataRow In table.Select("Id = 2")
    row.Delete()
Next

You have not actually deleted the rows with that id in the database, but only in the cache you possess of it. The DataTable is totally disconnected from the TableAdapter and will not call the delete method. Then, whenever the user clicks the save button (or you ask the user to save) you can call the following to apply changes to the database :

adapter.update(table)

This method does not only update rows, it actually looks at all the rows in the cache (including the deleted ones), apply the changes to the database using the Update, Insert and Delete commands you set on the adapter and then call the table.AcceptChanges() method.

That way, you could simply call the adapter.update(table) method right after you deleted the and it should apply the changes to the database. I sent the whole table because you seem to be modifying the table before you delete the row, but just sending the row is what you would usually do (the methods is overloaded to take both).

Also, I see that you do this :

For r As Integer = rnum + 1 To .Rows.Count - 1
    .Rows(r).Item(0) -= 1
Next r

I suppose that the 0th column is your primary key... You should never have to mess with the primary key like this. This is both ineffiecient and unsafe. Ineffiecient because a simple delete clause will not do this, so you need to update every single row in the table that have an Id higher than the row you delete. Unsafe because you may be breaking relationships between tables and would need to update those link also, which will work only on very small amounts of data.

Instead, you should consider numbering your rows on client side (just run a loop that goes through all the records). If you're using an Oracle or SQL Server 2005 database, Oracle has the ROWNUM field which acts like a column name and SQL Server 2005 has some other tool (or so I quickly read somewhere). You could use these tools to have the row number to pop out of the database without it truly being a column. It is also possible to create a SQL Query that will return it using the COUNT function in a nested SELECT query, but that may get complicated if you have condiions and order by clauses in you original SQL.

 

[cjard]

Ehm. I think youre doing your data access in nearly completely the wrong way. Take a read of the DW2 link in my sig, start with "Creating A Simple Data App" - it shows everything you need to start including related data display

Then you have to stop thinking about "nested tables" - they are something your mother has tucked away in the corner of the room in case of emergency tea party. There's no such thing as a table within another table in computers. Tables hold data that is all related to itself, like Person holds Name, Age, Address. Tables may relate to each other, by use of primary key, but that doesnt mean they live inside each other

A dataset is a collection of related tables. Do not attempt to access it as though it is a row-ar store of data, and that rows 0-11 must relate to rows 12-21. THis is NOT how data is related (fixed row indexes)
Rows are related by a column or columns that can be used to uniquely identify exactly one row. Like a serial number.

Once you get the understanding of this, it will become clear, but read the DW2 link first, and also any other tutorials you can find on RDBMSs

 

[cjard]

I would do this, except the location to the database is not something I want to be constant, and as I am both reading and writing to the database, I can't make it a resource. Is there a way to connect via designer to a database who's location is determined at runtime?

Yes, but that would be running before you can walk. Read DW2, follow it with a fixed database, and we'll show you how to select a different MDB later. DONT FOCUS ON THIS AS A PRIORITY. It's something we can teach you ONLY when you understand what youre doing elsewhere.

The fact is I have struggled to work with databases for as long as I can remember, spending countless wasted hours trying to get the to load or save correctly, dealing with out of order issues, and all sorts of things.

I have a favourite quote; "If it's hard, youre doing it wrong" (there is only one situation I can think of where this isnt true.. )
Databases are very easy, but i think youre struggling with them because youre thinking about them in completely the wrong way. Order that data is stored in is 100% irrelevant in all cases, because we NEVER use positional indexes to access data. In the case of columns, we NEVER use rows(1).Columns(0) to get the ID column, hoping it will be first. We always use the named alternatives .rows(1).Columns("ID") because our program will carry on working even if the column order changes

ALways seek to make your code tolerant and ignorant of order

I've even been developing a DataManager class to use in all future programs for this purpose (though it still has all sorts of bugs)

Can I be blunt? Dont bother. Microsoft already did it for you and built it into Visual Studio for you.. and they spent hundreds of thousands on it. It's excellent and surpasses anything you or I could do in a reasonable time

Speaking of which, another major bug I'm having involves the Delete command as for the life of me I can't seem to get it to work. Do you have a good reference for getting that to work?

You dont need to know. You just call .Delete() on the row and then ask the tableAdapter to Update() the database

Otherwise, here's the code I'm using to generate the delete command for the dataadapter and delete a row while fixing the primary key values to keep in order with no gaps.

As i said before, get over this idea of closing gaps up and shuffling thousands of primary keys round. Databases are NOT arrays. You are not wasting space just because you delete row 59 out of 100 and hence have a gap. Leave the gaps alone; they are doing no harm! If you must have a counter you do that at query time. Example, in oracle, this table had 100 rows. i deleted 50 at random, then wanted a row number to show to the user:

SELECT rownum, id, name FROM person

1, 23, john
2, 46, mark
3, 12, matthew
4, 75, luke

I DO NOT USE THE ROWNUM TO IDENTIFY THE ROW. EVER. That is what the ID is for. If you dont want the user to see the ID, dont show it.. but that is the column that identifies the row and its children relations. DONT change the id. DONT worry about it having gaps.

EDIT: Order By solved the problem. I love how simple the solution ended up being and how I couldn't find that despite hours of searching on Google with poor word choices. I should have joined these threads ages ago. Thanks a lot.

Bear in mind that youre still using databases as though they are an array; they arent (not since the eighties)