asked on

VB .Net - Cant update primary key in table

When I want to update a row in a table I write the code it as in the code box below.
In this example I get the error message that the column customernr can not be edited. It has worked before, until there was added a column to the table. Both the dataadapter and the dataset was re-created after that.

I use the same way of handling updating a row in another datatable, and that datatable/datasets/dataadapter has also gone through the same ordeal. But the code gives no errors when it is run agains that table with that dataadapter and dataset.

Any suggestions?

Dim customerRow As dsCustomerSeek.CUSTOMERRow
customerRow = frmSeek.DsCustomerSeek1.CUSTOMER.FindBycustomernr(txtCustomerNr.Text)
 
With customerRow
            .BeginEdit()
            .firstname = txtFirstname.Text
            .lastname = txtLastname.Text
             .customernr = CInt(txtCustomerNr.Text)
            .EndEdit()
End With
 
frmSeek.DaCustomerSeek1.Update(frmSeek.DsCustomerSeek1.GetChanges())
frmSok.DsCustomerSeek1.AcceptChanges()
DsCustomerSeek1.Clear()
DaCustomerSeek1.Fill(DsCustomerSeek1)

Open in new window

wht1986

That looks correct, customernr is the primary key on the table. You executed a FindBycustomernr on the table, which returns a row that obviously already has customernr fill in it. Just update the other columns, and call update.

With customerRow
.BeginEdit()
.firstname = txtFirstname.Text
.lastname = txtLastname.Text
.EndEdit()
End With

DataAdapter already knows how to set the where clause based on primary key thats already in the row.

itnifl

ASKER

I forgot to mention that I already tried that. I get the same error message when I try to leave customernr out of the "treatment."

itnifl

ASKER

What makes it strange is that it worked before, and it works now for another table with the same type of coe and table conditions.

wht1986

I would open up your dataset and reconfigure your table adapter. I am thinking the update of the adapter is what is incorrect. Does the adapter use inline sql or a stored proc for the update. Can you post the expression for the update it is using? Somewhere it is trying to do something like
UPDATE YourTableName SET firstname='bob', ..., customernr=2 where customernr = 2

By the way is this an obect data source or sql? I ask because I have seen the ObjectDataSource put the original object if for the update as object_{0} where it should just remain {0}.

itnifl

ASKER

It is a OleDbDataAdapter. The select command looks like in the code box(the column names are in norwegian). You mean I should set the parameter before using the adapter? It should be set already from when the dataset was created with the row I am editing. The database is a simple access database. I dont know if this is an object data or sql.

SELECT        kundenr, fornavn, etternavn, firma, adresse, postnr, tlfnr, mobilnr, faxnr, [e-post]
FROM            CUSTOMER 
WHERE customernr = ?

Open in new window

itnifl

ASKER

oops, kundenr means customernr, so the true norwegian code is WHERE kundenr = ?. Forgot to edit the whole text.

wht1986

I think the select statement is fine, i think the update statement is what is causing issues.

it should be something like

UPDATE [CUSTOMERS] SET [fornavn] = ?, [etternavn] = ? WHERE [kundenr] = ?

and the parameter order should match the order of the statement

<UpdateParameters>
<asp:Parameter Name="fornavn" Type="String" />
<asp:Parameter Name="etternavn" Type="String" />
<asp:Parameter Name="kundenr" Type="Int32" />
</UpdateParameters>

itnifl

ASKER

The update statement is a mess. Where can I edit to see the paramters?

UPDATE `KUNDE` SET `kundenr` = ?, `fornavn` = ?, `etternavn` = ?, `firma` = ?, `adresse` = ?, `postnr` = ?, `tlfnr` = ?, `mobilnr` = ?, `faxnr` = ?, `e-post` = ? WHERE ((`kundenr` = ?) AND ((? = 1 AND `fornavn` IS NULL) OR (`fornavn` = ?)) AND ((? = 1 AND `etternavn` IS NULL) OR (`etternavn` = ?)) AND ((? = 1 AND `firma` IS NULL) OR (`firma` = ?)) AND ((? = 1 AND `adresse` IS NULL) OR (`adresse` = ?)) AND ((? = 1 AND `postnr` IS NULL) OR (`postnr` = ?)) AND ((? = 1 AND `tlfnr` IS NULL) OR (`tlfnr` = ?)) AND ((? = 1 AND `mobilnr` IS NULL) OR (`mobilnr` = ?)) AND ((? = 1 AND `faxnr` IS NULL) OR (`faxnr` = ?)) AND ((? = 1 AND `e-post` IS NULL) OR (`e-post` = ?)))

Open in new window

wht1986

What is kundenr definition in the table. I know it is the primary key but is it an autoincrementing integer by chance? Anyhow you can see what the parameters are by looking in several places depending on your project.
1) on the web form aspx page if defined there
2) on the designer.cs file of the dataset

itnifl

ASKER

customernr / kundenr is a autoincrementing integer.

ASKER CERTIFIED SOLUTION

wht1986

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itnifl

ASKER

I deleted the dataset, I then deleted the dataadapter. Next I copied a existing dataadapter, and configured it correctly. I generated a new dataset. I now get the error message that there is not given a value for one of the parameters when I tru to fill the dataset. The parameters should be the same, the select command is the same, and it is given its parameter the same way. Creating everything again from scratch did not help. I do it like this:

cmdCommand.Parameters("kundenr").Value = Cint(txtValue.Text)

So what is wrong now then?

itnifl

ASKER

Thanks, I reconfigured the dataadapter. It now has the correct update sentence. Thanks again.