Get AutoIncrament Primary Key after Insert using VB.NET 2010 with sqlce 3.5

Question

I have an issue that I can't seem to tackle.  Going on 3 weeks.  I have a VB.NET app with an sqlce 3.5 database.  I have created several tables in this database.  I have attached them to my form using DataSet then BindingSets and so forth.  Done this many time before.  VS automatically created a TableAdapterManager for me.

I was having an issue getting the Refresh of the primary key value of a newly inserted row.  Ok, find out that sqlce does not do big transaction, or multiples rows at a time, found Beth Massi's blog on how to work around this: http://blogs.msdn.com/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

Ok, got it somewhat to work, but I had to take one of the tables out of the tableadaptermanager so I can update it by itself so insted of doing this:

frmMain.Validate()
frmMain.bsRunInformation.EndEdit() 
frmMain.TableAdapterManager1.UpdateAll(frmMain.DS1)
I tried this:
frmMain.Validate()
frmMain.bsRunInformation.EndEdit()
frmMain.tblRunTableAdapter.Update(frmMain.DS1) 
Now it does not refresh the new PrimaryKey value again.  Give's me -1 in the form.
What the heck am I doing wrong?  I used Beths' code as follows:
Imports System.Data.SqlServerCe
Public Class SQLCEIDHelper
    Public Shared Sub SetPrimaryKey(ByVal trans As SqlCeTransaction, ByVal e As SqlCeRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key PK column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                'Set up the post-update query to fetch new @@Identity
                Try
                    Dim cmdGetIdentity As New SqlCeCommand("SELECT @@IDENTITY", CType(trans.Connection, SqlCeConnection), trans)
 
                    'Execute the command and set the result identity value to the PK
                    e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                    e.Row.AcceptChanges()
                Catch ex As Exception
 
                End Try
            End If
        End If
    End Sub 
 
     
               
          
Namespace DSTableAdapters
    PartialClasstblRunInformationTableAdapterPrivateSub _adapter_RowUpdated(ByVal sender AsObject, ByVal e AsSqlCeRowUpdatedEventArgs) Handles _adapter.RowUpdated
            'SQLCEIDHelper.SetPrimaryKey(_connection, e, _transaction)SQLCEIDHelper.SetPrimaryKey(Me.Transaction, e)
        EndSubEndClass
 End Namespace 
           
Partial Class tblRunInformationDataTable
        Private Sub tblRunInformationDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
            Dim cat = CType(e.Row, tblRaceInformationRow)
            'cat.TotalTime = Now
        End Sub
    End Class
 Has anybody had an issue like this or can anyone please help.  Thank you so much in advance.

Answer 1

Hi viacuda77,

Could you please check that the ReadOnly property has been set to False on the Primary Key fields?

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Stephanie,

I have already made sure that the were marked False for Read only.

Answer 3

Hello viacuda77,

Assuming everything about your Database and Dataset is correct, try the following to fix your issue:

1) Add a new Windows Form page to your solution.

2) Navigate to (Main Menu -> Data -> Show Datasources) to bring out the Data Sources pane.

3) In the Data Sources pane, expand your DataSet name if it's not already expanded and drag the table unto the new empty Windows Form page. You will notice that visual studio will setup the page by adding the BindingNavigator and GridView onto the Form. It also generates new code-behind. **You don't need to change anything**

4) Now you need to make the new Form page the MainForm. Navigate to Solutions Explorer -> Your Solution -> your Project -> "My Project". Then double-click on "Application.myapp". In the file, You need to add the name of the new Form to the <MainForm>new name goes here</MainForm> section.

5) Once this is done, you should be able to execute the program with the new Form being displayed.

6) Test

Please respond if this fixes your issue or not.

---

Abdulwahab Suleiman