Using a dataset update errors?

[pachjo]

I am getting an error when trying to update a database via a tableadapter .update method.

Here is the code:

Dim ds As New MyFinanceDataSet

 Dim ta As New MyFinanceDataSetTableAdapters.MFTableAdapter

 Dim ro As MyFinanceDataSet.MFRow = Nothing

 ta.Fill(ds.MF)

 If ds.MF.Rows.Count = 0 Then
   
    ro = ds.MF.NewMFRow

    ds.MF.Rows.Add(ro)

    ta.Update(ds.MF)

End If

 ds.MF(0).fld = System.Text.Encoding.Default.GetString(tmpHashNew)

' error happens here

ta.Update(ds.MF)

The error is update requires valid updatecommand when passed datarow clooection with modified rows.

I have built the dataset MyFinanceDataset again and it says that all the commands were created, select insert etc.

So I dont understand why this should happen as I am using a Typed dataset?

 

[pachjo]

Found the answer here:

http://forums.microsoft.com/msdn/sh...17&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

I had to add a new field to my table which I set to be Identity and primary key

I then configured the dataset and clicked the advanced button to check Refrresh checkbox

 

[cjard]

The error is update requires valid updatecommand when passed datarow clooection with modified rows.

The wizard wasnt able to generate an UPDATE sql for this table because it has no primary key, ergo the wizard cannot decide what combination of columns should be used to uniquely identify at most one row.. so it doesnt write an UPDATE statement..

Rather than just blindly adding an Identity, you should consider that fact that you must have intended to update this table, otherwise you wouldnt be attempting to do so.. so what combination of columns allows you to uniquely id a single row?

If there really is no combination of columns that is going to be enforcibly unique then yes, you will have to use an id column ot something but this points to a much bigger problem that the data design is flawed? How can you expect to update a table without being able to identify what you want to update


e.g. suppose this is a Person table. No 2 people have the same social security number so thats ok for a primary key, and in a webpage, Joe Smith can tyupe his social security number and see his data...

But what if you dont have that, and youre relying in an Identity field to be the PK? What if your web site (or whatever) has 2 Joe Smiths, and the only way to tell them apart is that one has ID column entry 12283742 and the other has 98275348 ... What does that mean??? Nothing...

This is why usually PKing on a meaningless ID is.. well.. Meaningless. You really should attempt to find a better primary key, unless youre going to visibly be using that Identity field from now on..

 

[pachjo]

Never a true word spoken

However the table in question stores an encrypted password for the application therefore there is only 1 column and 1 row.

Even if (not through my app) the end user somehow adds a new row my app only references the first row.

I need the update statement to allow the user to change the app password (through my app)

 

[cjard]

I need the update statement to allow the user to change the app password

So the PK is that one column, because its guaranteed to be unique.. You dont need the Identity column

 

[pachjo]

Mmmm true....I think?

I used an identity column as the column with the password is encrypted and is full of gobbledigook characters so presumed this would caused a problem

 

[cjard]

It depends on the DB, i suppose.. If its a BLOB field of some kind then yep, youre right the rdbms might refuse to index it