Monday, February 13, 2012 4:56 PM
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) EndSubEndClassEnd 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 ClassHas anybody had an issue like this or can anyone please help. Thank you so much in advance.
Wednesday, February 15, 2012 7:19 AMModerator
Thursday, February 16, 2012 2:06 AM
I have already made sure that the were marked False for Read only.
Saturday, February 18, 2012 5:00 PM
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.
Please respond if this fixes your issue or not.