import-bot (20211) [Avatar] Offline
#1
[Originally posted by jdadamo]

Hi -

I'm using a form to add player records to a datatable that was filled from an
sql db. The primary key in the db is 'PlayerID' which is set to
autoincrement. I was able to add records and update the db and the db would
add the PlayerID value. I then created a form to edit the records in the
datatable. The edit form pulls the player info after the player name is
selected from a listbox using selectedvalue with 'PlayerID' as the
valuemember. When the form is called I get the error: Table doesn't have a
primary key. I identifyed the primary key column 'PlayerID' using the code
example on p.173 of the book - I am not using fillschema. I can now edit
player records but when I try to add a player record I get the error:

"An unhandled exception of type 'System.Data.NoNullAllowedException' occurred
in system.data.dll
Additional information: Column 'PlayerID' does not allow nulls."

I've tried setting AllowDBNull = true on the 'PlayerID' column in the
datatable but still get the same error.

I'm thinking there must be a straight forward way to add/edit records to/in a
datatable while using a primary key field without causing problems with the
primary key field in the underlying database.

Please help - thanks.
import-bot (20211) [Avatar] Offline
#2
Re: Adding rows to Dataset
[Originally posted by arlen]

Dealing with identity columns can be quite a nuisance. There is no really
straightforward automatic way to handle them with DataTables.

What you will probably want to do is create a custom Command for the Data
Adapter which does the insert, retrieves the value from the identity column
during the insert during the RowUpdated event, and set it back into the
DataTable.

There are a number of steps:

1. Put in a bogus value into the primary key column before doing the update
2. Create a custom insert command (See page 213+) that inserts all of the
columns _except_ the identity column
3. Add a handler to catch the RowUpdated event
4. In the row updated event, retrieve the identity value (you can use "SELECT
@@identity" and ExecuteScalar() to retrieve the identity value from the last
inserted row). (Don't forget to make sure that the command being executed is
an insert.
5. Set the value for the field in the handler
6. Call AcceptChanges() for the row in the event handler (otherwise it will
look like the row is dirty again).

As I said, not hugely complicated, but not trivial either. Let me know if you
have trouble with this.

Arlen
import-bot (20211) [Avatar] Offline
#3
Re: Adding rows to Dataset
[Originally posted by jdadamo]

Arlen - thanks for the quick reply!

Question - will this approach work if more than one record is added to the
datatable prior to updating the underlying db?

The application I'm coding is supposed to populate the dataset, let the user
view/add/edit/delete records in multiple related tables while disconnected,
and then save changes back to the db.

I'm working thru the suggestions you made but I seems like the underlying db
needs to be updated after each record is added/changed. I'm wondering if
using a GUID approach (p.137) on the client side instead of an identity column
in the db will help or if there is a better way to design the application.
The application is just "data in/data out" but will eventually have dozens of
tables. I'm new to programming - can you recommend sites/books where I could
find examples of complete "data in/data out" applications?

thanks.
import-bot (20211) [Avatar] Offline
#4
Re: Adding rows to Dataset
[Originally posted by arlen]

The approach will work with multiple records as the event will be fired
between each.

I am a big fan of the guid approach - it has a lot of advantages. The only
disadvantage is that the records are not ordered based on the key - but that
is easily rectified by having a field holding the date/time created
information.

I am sure there are books out there that cover the entire lifecycle of a
standard data access project, but I have yet to find one that I thought was
any good. I will do a little research and see if I can find one.

BTW I will be traveling over the next week, so may not be able to respond
quickly, but I will check the forum when possible.

Arlen
import-bot (20211) [Avatar] Offline
#5
Thanks. [EOM]
[Originally posted by jdadamo]