Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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

0
itnifl
Asked:
itnifl
  • 8
  • 5
1 Solution
 
wht1986Commented:
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.
0
 
itniflAuthor Commented:
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."
0
 
itniflAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wht1986Commented:
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}.
0
 
itniflAuthor Commented:
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

0
 
itniflAuthor Commented:
oops, kundenr means customernr, so the true norwegian code is WHERE kundenr = ?. Forgot to edit the whole text.
0
 
wht1986Commented:
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>
0
 
itniflAuthor Commented:
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

0
 
wht1986Commented:
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

0
 
itniflAuthor Commented:
customernr / kundenr is a autoincrementing integer.
0
 
wht1986Commented:
If thats the case, your update statement is definitely what is wrong.

this line
UPDATE `KUNDE` SET `kundenr` = ?, ...

shows you are trying to update the autoincrement column.  you cant do that. If this is all based on a dataset table adapter, i would right click on the tableadapter and click configure again and follow the prompts.
0
 
itniflAuthor Commented:
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?
0
 
itniflAuthor Commented:
Thanks, I reconfigured the dataadapter. It now has the correct update sentence. Thanks again.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now