Update SQL Table VS2005 VB.NET

Question

Hi All,

I have been messing with some basic vs2005 apps... im having trouble updating a table however.

Is it possible you can help me?

I have a form with a cbo binded to a dataset that I have. I use this to populate a couple of text box's on the form. If I make an edit and try to update the table using this code:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click Me.CustomerTableAdapter.Update(DsCustBasic.Customer) Me.DsCustBasic.AcceptChanges() End Sub

The dataset appears to change while the app is live, however I cant seem to actually write the update to the table.

Barry

Answer 1

Hi,

Check the Adapter's Update, Insert and Delete command. See if it has a valid command text or sql statement...





check,

Paul June A. Domag

Answer 2

Thanks for Answering Paul.

Here is what my actual update query looks like:

[sql]
UPDATE [dbo].[Customer] SET [Code] = @Code, [Company Name] = @Company_Name, [Post Code] = @Post_Code WHERE (([Code] = @Original_Code) AND ((@IsNull_Company_Name = 1 AND [Company Name] IS NULL) OR ([Company Name] = @Original_Company_Name)) AND ((@IsNull_Post_Code = 1 AND [Post Code] IS NULL) OR ([Post Code] = @Original_Post_Code)));
SELECT Code, [Company Name], [Post Code] FROM Customer WHERE (Code = @Code)
[/sql]

Answer 3

Does this mean im not passing the correct paramaters?

In 2k3 I would just add

Me.BindingsContext.dsCustBasic1.EndCurrentEdit()

before my update line of code which would solve the same problem I had back then.

Answer 4

Hi,

Are you using a local SQL Express database by any chance? If so, you may not be seeing your database updated because of the copy behavior. You can learn more about this in Antoine's blog post at https://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx. Look for the section titled Where is my data? -- Understanding the file copy for desktop projects.


Jay Hickerson
Visual Basic Team

Answer 5

Please try Jay's suggestion.  IF that does not work, I recommend a variation of the suggestions above:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click    'push all UI changes to the dataset    me.CustomerBindingSource.EndEdit    'call the update     Me.CustomerTableAdapter.Update(DsCustBasic.Customer)    'removed the AcceptChanges call since tableadapter.update does this automatically End Sub

In a nutshell the EndEdit pushes all live UI changes to the dataset.  Then you call update to update the database.  You actually don't want to call AcceptChanges afterwards.  The TableAdapter does this for you.

Hope this helps.

Paul

Answer 6

hi!

Thanks for your help guys! @ Jay thanks a lot, interesting entry, im using a sql 2000 server though to hold my data.

Paul, you saved me from such a headache! thanks :D