Update failing after insert

[dawatson]

I'm using VB 2005 and an Access database in an application.
A form that I'm using has some bound controls on it.
The user enters some data into the controls, then clicks save to save the new record. If the user then keys more data into the controls and clicks save again, the update fails.

I use this code to determine the rowstate of the bindingsource.
Ctype(bsConfiguration.Current,DataRowView).Row.RowState
After the insert, the code returns Added {4}.
The new record is inserted into the database.

So when the update code is run, the rowstate is still Added {4}.
The update fails and returns a duplicate index, primary key, relationship ole db error. I believe the update statement is trying to insert the same record again.

Why doesn't the rowstate change after the insert?
What else do I do after inserting the record to get the next update to work?

Private Sub SaveConfiguration()

        If formLoading OrElse bCancelMode Then Exit Sub
        If Not Dirty Then Exit Sub

        Try

            If FormIsClosing Then Exit Sub

            'fix 10/28/2008
            If Me.Validate = False Then
                Exit Sub
            End If

            If String.IsNullOrEmpty(txtConfigId.Text) Then
                bsConfiguration.Current("ConfigurationId") = CreateNewConfigId()
                FillConfigurationObject()
                bsConfiguration.EndEdit()
                With Configuration
                    taConfiguration.Insert(.ConfigId, .ConfigName,   cboCustomer.EditValue, .SalesRepId, cboManufacturer.EditValue, 
dtConfigDate.Text, .SalesRepMarkup, .TradeIn, .Payoff, .PONumber, .OrderedBy, .OrderedByEmail, .PrintLease, .LeaseOption, .PrintRetailprice, .TotalSaleAmount, .CommissionRate, .ManagementNotes, 
.ServiceBaseCharge, .ServiceIncluded, .ServiceType, .UsageCharge, .ServiceOptions, .InstallContact, .InstallContactEmail, .PaymentMethod, .UseShipto, .MiscNotes, fMain.CurrentLogin, .Promotion)

 End With

Else
     bsConfiguration.EndEdit()
     taConfiguration.Update(DsConfiguration.Configuration)
End If

Catch ex As OleDb.OleDbException
  ErrorLog.WriteToErrorLog(ex.ToString, ex.StackTrace, SCP_UI.fConfiguration.SaveConfiguration")

Catch ex As System.Exception
  ErrorLog.WriteToErrorLog(ex.ToString, ex.StackTrace, "SCP_UI.fConfiguration.SaveConfiguration")
            MessageBox.Show(ex.ToString & vbCrLf & _
                                             ex.Source & vbCrLf & _
                                            ex.TargetSite.ToString & vbCrLf)
        End Try

    End Sub

 

[cjard]

What a mess. Please s ort your code indentation out. Also put some comments in so I can understand the logical flow of the program (doing this will probably help you find the error too)

Why doesn't the rowstate change after the insert?

I have never, in the 2 years of using datasets and tableadapters, seen a tableadapter.Update() statement fail to alter the rowstate from Added to Unchanged, following on from a successful insertion of data in a row that has been added to a datatable.

Given that you run through some logic to decide whether to poke an Insert directly, or let Update() do it, I suggest that you look to there as your first port of call for the failure of attempting to insert duplicate records.. i.e. youre doing it deliberately!

 

[dawatson]

I tried to format the code before it was uploaded. I actually took out some of the comments before I uploaded it because the upload doesn't format the comments very well. The upload reformatted the remaining code, and now I can't edit it.

When an insert is done, a control bound to the primary key is assigned a value. Then the insert is completed. The insert completes successfully. The new record is in the database. But the row state doesn't change. I've stepped through the code several times to verify it. When the procedure is entered again to update the record, the rowstate is still Added {4}.

I've searched the net and there are some other post around with similar problems.

 

[cjard]

I really get the feeling youre calling .Insert(a, bunch, of, values) and expecting this to somehow have an effect on a row sitting in a datatable somewhere.

Like I say, I have used datatables and tableadapters for nearly two years, and I have never, ever seen a row that was RowState Added, in a datatable, that was passed into tableadapter.Update, fail to have its Added state set to Unchanged pursuant to the successful execution of the InsertCommand

Scenarios where RowState is not changed do exist but these are deliberately arranged by the programmer. e.g. exposing the wrapped DataAdapter and setting its AccepChangesDuringUpdate property to False, or Calling DataTable.GetCHanges to create a cloned table, and then Update()ing the cloned table (naturally isnt going to affect the original table)

I've got an app at home currently uploading the FreeDB database into a SQLServer instance. It adds a row to a datatable, adds several rows to a child table, then calls ta.Update(dt) for parent and child, and then clears the dt. It's processed nearly quarter of a million files now, which is probably in the region of 5 million inserts, and it's always set the rowstate to Unchanged

 

[cjard]

I've stepped through the code several times to verify it.

Can you take a screen recording using WME?

I've searched the net and there are some other post around with similar problems.

Can you link them?

 

[dawatson]

I don't think I understand what is happening at the tableadapter level.
Remember, I'm using bound controls and tableadapters. I'm not creating a datatable in code and running an insert statement.

I'm not sure what you mean by
"I really get the feeling youre calling .Insert(a, bunch, of, values) and expecting this to somehow have an effect on a row sitting in a datatable somewhere."

I wouldn't expect an insert to affect a row in a datatable, since it is an insert command.
But I would expect it to insert the record and that the new record be in the tableadapter and bindingsource. And since it has been inserted, I should then be able to update that record.

The values are passed to the insert command because it uses an Access stored query as its commandtext and the commandtext has a parameter collection defined. The insert command is defined in the dataset/tableadapter xsd designer. The same as the select/update/delete commands are. If the tableadapter.update command is executed, isn't the tableadapter updated immediately? If that is the case, when the insert command is executed, shouldn't the tableadapter reflect that a new record has been added?

I know that it's a lot to ask of .net, but could the solution be as simple as refreshing the bindingsource to reflect the new record has been added?

I'll modify/test my code to use the update command instead of the insert, and let the update command figure out the state and insert the record.

 

[cjard]

Take a read of the "DataSets? DataReader?) sticky thread in the ADO.NET forum.. I think, once you get to the end of it, you'll have a