LoadDataRow - Inserts or Updates?

[anilgupte]

The documentation says that LoadDataRow updates or inserts based upon whether the row exists or not. It does not say so in the documentation, but I figured the only way for it to know that is for it to check the Primary key. So, in my dataset, I defined a Primary key for the table, like this:

Dim keyColumn(1) As DataColumn
keyColumn(0) = ds.Tables(TableName).Columns(i)
ds.Tables(TableName).PrimaryKey = keyColumn

Now when I use the following:
Dim NewRow(4) As Object
NewRow(0) = intProfileID
NewRow(1) = DropDownProfiles.Text.Trim
NewRow(2) = 1
NewRow(3) = txtBoxProfileDescription.Text.Trim
NewRow(4) = txtBoxPassword.Text
drins = ds.Tables("Profiles").LoadDataRow(NewRow, True)

I get an error message saying "Column 'ProfileID' is constrained to be unique. Value '2' is already present." In other words, it will not update, only insert. I have been forced to delete each row I want to update and then reinsert.

What gives?

TIA,
AG

 

[Stonkie]

I use .NET 2.0 and 3.5 so I don't know exactly which methods are there and which aren't for you, but here's how I'd do it.

dim row as DataRow = ds.Tables(TableName).NewRow()
row("ColumnName1") = value1
row("ColumnName2") = value2
'set whatever fields you need by their names because the row took the table's shema.
ds.Tables(TableName).Rows.Add(row)

 

[cjard]

What gives?

Er.. What LoadOption did you use?

 

[cjard]

I use .NET 2.0 and 3.5 so I don't know exactly which methods are there and which aren't for you, but here's how I'd do it.

dim row as DataRow = ds.Tables(TableName).NewRow()
row("ColumnName1") = value1
row("ColumnName2") = value2
'set whatever fields you need by their names because the row took the table's shema.
ds.Tables(TableName).Rows.Add(row)

Nono, LoadRow is different, if the row exists it updates, if it doesnt exist it inserts. THis is very different to your always-insert which will cause a PK violation if you insert a row that already exists (PK value-wise)

 

[Stonkie]

oh, I used to do the "look for the row and update otherwise insert" code all by myself! Learning every day! (and if I didn't I'd be outdated within a month anyway! )

 

[anilgupte]

Where does one use the LoadOption?

Er.. What LoadOption did you use?

I am not sure where this option is. I don't think I am using it. Of course there are the two option in LoadDataRow that you can see.

 

[anilgupte]

I did find one solution which is completely counter-intuitive. It says to use DataSet.AcceptChanges before doing this. The funny thing is that it appears to work. The row does update or insert correctly.