Help with DataTable update (BeginLoadData method)

[Mike]

I have an ASP.NET/VB app that updates values in a DataTable over the
course of about 3 different pages. On the way out of the first of
these pages, I explicitly build the DataTable from values in a
DataGrid, and set the PrimaryKey of the DataTable to be the first cell
in the grid (which is a UserID value). I then store the DataTable in
a session object, from which it is retrieved for subsequent pages.
All this seems to be working fine.

On the second page, I retrieve the DataTable from the session object,
apply a filter to it, and display the filtered data in a DataGrid. In
this grid, the user can change the data, specifically assigning an
employee to a different supervisor. The Grid is populated with the
current employee/supervisor assignment in the database, and then any
necessary changes are made by the user. This, too, seems to be
working fine.

The problem I'm having is that on the update command of the Grid,
which is where I'm updating the DataTable (the thought being that I
would just do an "in-place" update of the DataTable and then rebind
the grid). I am able to trap the new assignment values (which are set
in a listbox within the grid) and can trap the employee ID value from
the grid (which is my primary key), but whenever I call the
LoadDataRow method, rather than updating the row based on the primary
key, a new row insert is *attempted*. I say attempted because I get
an error on the EndLoadData method, which I believe is due to not
passing in values for all the columns.

I was hoping to be able to just pass in the key value and the values
of the columns that had changed, but apparently that is not
sufficient. Which is the first question - when attempting a
LoadDataRow call, do I need to pass in valid values for *all* of the
columns in the datatable (except, of course, those for which there are
default values, of which I have none in this instance). You'll see in
my example code that I'm just passing in the first, eighth and ninth
columns, which I suspect is the problem.

However, the *real* question is why isn't the LoadDataRow method
finding the pre-existence of the key value in the table? I've checked
and the value returned by my code is exactly the same as the one in
the table, length is the same, etc. I set the primary key when I
first build the table on the previous page, but I thought perhaps that
attribute somehow gets lots when I set the DataTable to a session
object so, as you will see in the example, I explicitly set the
primary key again on this page, which seems to work, but the code
still inserts a new row (or attempts to).

Here is the relevant code from the page in question:

Dim DDL As DropDownList = CType(e.Item.Cells(2).Controls(1),
DropDownList)
Dim NewID As Integer = DDL.SelectedIndex
Dim SupervisorID As String = DDL.Items(NewID).Value
Dim SupervisorName As String = DDL.Items(NewID).Text
Dim NewRow(9) As Object
Dim Key As TextBox = e.Item.Cells(1).Controls(0)
Dim RowID As String = Key.Text

Dim dsTechnicianList As DataTable
dsTechnicianList =
CType(HttpContext.Current.Session("ILApplicators"),
System.Data.DataTable)
Dim dcFirst As DataColumn = dsTechnicianList.Columns(0)
Dim dcPrimary(1) As DataColumn
dcPrimary(0) = dcFirst
dsTechnicianList.PrimaryKey = dcPrimary

NewRow(0) = RowID
NewRow(7) = SupervisorName
NewRow(8) = SupervisorID
Dim myRow As DataRow
dsTechnicianList.BeginLoadData()
myRow = dsTechnicianList.LoadDataRow(NewRow, True)
dsTechnicianList.EndLoadData()


Any help or ideas would be GREATLY appreciated!

TIA,
Mike

 

[mklapp]

Hello,
My reading of the LoadDataRow method indicates that
you do need all values for the row. Those columns that
will receive a default value are to be sent Null (or
DBNull, I guess).

Now, the search is only on the unique Primary Key
(according to docs). Judging from what I read, column
positions are important here (something new for long time
relational DB programmers).

In your excerpt, you define your array used to set the
Primary key as : DIM dcPrimary(1)
This gives you an array with an upper bound of 1, i.e.
a two element array and you are populating only one
element. I haven't used the method, but try either
populating all array elements or declare an array with an
upper bound of 0.

Good luck

mklapp

 

[Mike]

Thanks for your response! Unfortunately, I suspect you're right about
having to pass in all rows at update time. That means I'll have to
basically add code to include all columns in the datagrid, which I was
hoping to avoid, but I guess not.

I tried your suggestion on the size of the primary key array, as well
and it didn't change my situation. It's definitely a good spot, and I
think I would have had to do this anyway.

I still end up with 1 more row in the table after calling
BeginLoadData than I had before calling it, so I suspect things still
aren't working. I'll add the other data to my grid and try passing
everything in, but if things were working properly I would expect my
table size to stay the same.

Any other suggestions are certainly welcome!


Thanks again,
Mike

 

[Mike]

I found the answer to my own problem. Although it is not documented
you must call the AcceptChanges method *before* calling the
LoadDataRow method. This really doesn't make sense, as passing in
True for the fAcceptChanges argument of LoadDataRow forces it to be
called in conjunction with LoadDataRow.

At any rate, I added AcceptChanges just before LoadDataRow and all is
well!