TableAdapter.Update Will Not Save Changes

I have used drag and drop to create a dataset in VS 2010 with SQL Server CE 3.5 on the user computer and SQL Server 2008 R2 Express on the server computer. Everything was done using the VS 2010 designer. At first, all records were inserted properly and updates were done. That was before I began inserting URLs into the image path textbox. Now, the Update will do some updates but not any which are a link to a web address such as http://www.somewebsite/foldername/filename. When running, I can upload an image and view it and the path to the image is inserted into the bound textbox where it belongs. When I attempt to save it, the textbox is blanked out and the record may or may not be updated, depending on which changes are made. If it is a new record, nothing is inserted. If it is an existing record and the changes do not involve the image path textbox, then it is updated. If it is a new record without an image, it is inserted. If the image path is inserted into the table on the server and then synchronized with the local database, the local database correctly updates the field.

I have looked at the dataset using the .GetChanges method and when the path is typed into the textbox, it shows in the pending changes dataset but when the path is programmatically inserted, it does not. In either case, the record is not inserted/updated.

I cannot find any calls to fill the datatable between the changes and the updates.

I have found other references to this kind of problem on this site and other sites but no definitive solutions. I've lost about a week over this.  My question is this: Is there a fix for this or should I scrap the existing dataset and start over? I have done this exact same thing on other programs and not had this problem. Suggestions would be greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Do you get any kind of error? Have you tried adding a breakpoint and go step by step? If i would go for a lucky guess, i would say there is a length violation, like the field in your DB is declared to be of length 30 and you are trying to enter more than that.

A sample code may also give us a hint.

rkulpAuthor Commented:
Thanks for your quick reply. I have not received any error messages. The field is nvarchar 100 so should easily contain the data. It does fine if I insert it via the SQL Server Management program. I have done it step by step and can see the data being removed from the field as it tries to save it. Here is the code where I attempt to save the data:

    Private Sub btnSaveInventoryData_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveInventoryData.Click
        Dim nbrRecords As Integer = 0

            If Me.ChurchInventoryDataSet.HasChanges Then
                nbrRecords = ItemsTableAdapter.Update(Me.ChurchInventoryDataSet)
                MsgBox("Number of records affected = " + nbrRecords.ToString)
            End If
        Catch ex As Exception
            gMsg = "The following error occurred saving the Items table:" + vbCrLf + ex.Message
        End Try
    End Sub

Open in new window

Ioannis ParaskevopoulosCommented:
Ok, all this with the Table Adapters is old history for me, so bare with me i am a bit out of place. I just want to try to help.

Please have a look on this :

It seems to be a similar situation.

Apparently EndEdit on the BindingSource does not update the RowState of each row.

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

rkulpAuthor Commented:
That is interesting. I will try some changes (additions, deletions, updates) to see if it solves the problem for me and get back. I have discovered another, possibly related, problem -- it seems I can only do one addition at a time as it appears to mess up the autoincrement. Still checking on that.
Ioannis ParaskevopoulosCommented:

That is even more interesting. What you say reminded me of another post i read yesterday searching for your case, but i thought it was irrelevant. It may be worth trying to read this one:

If you also want my personal opinion, I never liked binding controls. They take away the power of you having the control of what you are doing, though they do give you some sort of ease. But then there are all these issues, that makes you go into circles and some times even drive you crazy.

Since i messed with Entity Framework and LINQ i found my place of comfort. Entity Framework would take care of autoincrement.

Anyway, i know it is some times tough to change technology when you have already started.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rkulpAuthor Commented:
That looks very promising. I'll get back to this later today.
rkulpAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for rkulp's comment #a40936817

for the following reason:

This solved the problem. Thanks for the great solution and sticking with me.  Have you abandoned bound controls entirely when using Entity Framework and LINQ? I'll have to study up on them. I've used LINQ only once and it was very nice.
rkulpAuthor Commented:
Thanks for sticking with me and the great solution. It seems to have completely solved the problem.
Have you abandoned bound controls completely? I don't know anything about the Entity Framework and very little about LINQ. I need to study up on them.
rkulpAuthor Commented:
I apologize for messing up the "accept solution." In spite of my blunder, have a great day. You've been very helpful.
Ioannis ParaskevopoulosCommented:
No worries. To be honest i have almost ambandoned windows forms. But even before, i prefered 3rd party tools to visualise the data (DevExpress, Telerik, etc). And even in those cases i prefered to handle the push to the db myself.

You should read a bit about EF as it is the current standard.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.