none
update modified records in a dataset RRS feed

  • Question

  • keep in mind that I am very inexperienced at this, but my situation is that I am trying to write a simple little program to keep track of contacts.  I am using VB 2008 Express and have a bunch of text boxes on a form that correspond to the fields I have created in my sql table and I am connecting to the table via a dataset.  everything works fine when I add new records and save the changes back to the table with the UPDATE command.  This is the code I have used:

    Me
    .Validate()
    Me
    .ContactsBindingSource.EndEdit()
    Me.ContactsTableAdapter.Update(Me.PersonalInfoDataSet.Contacts)

    The problem arises when/if I go back and modify one of the records that was already svaed back to the table.  I get an exception that says something to the effect that I need a valid UpdateCommand in order to save modified records.  So I went into the properties of my table adapter and tried building a command using QueryBuilder, but didnt have much luck.  It seems I can only do that using very specific values and not just save any changes that were made.  So my question is... how do I go about saving modified records back to my table?  I would think that this would be a very simple fundamental thing... am I missing something???  Thanks in advance for any help with this!

    Oh, one more problem...  I have a couple of fields that I dont want to allow NULL values in.  How can I trap a NULL entry and throw up a message box.  With no trap in place, if I leave a NULL value, the program just blows up and throws an exception.  Thanks!!
    Saturday, March 13, 2010 7:06 PM

Answers

  • No worries.  There's always a learning curve and it helps to know a little about your background.  Personally, I think that we learn a great deal through iterative development where we run code, see a problem, figure out what went wrong on our own or with help from others, address the problem, re-run the application and keep moving forward.

    It sounds like the error messages you're seeing right now talk about the database columns that require values.

    1.)  Do you already have controls bound to the corresponding columns in your DataSet?  If you don't, you need to either programmatically assign values to the columns in your DataSet or bind controls to those DataSet columns.

    2.)  Once you have controls bound to those DataSet columns, you can check to see whether or not the user has supplied values to those controls in your code, then let the user know what went wrong and what they should do about it.  For example, in the code you showed earlier, you could add something like the following just before:

    If Me.ContactNameTextBox.Text.Length = 0 Then
        MessageBox.Show("Contact Name requires a value", "Validation Error", _ 
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Me.ContactNameTextBox.Focus()
        Return
    End If

    I hope this information proves helpful.

    David Sceppa
    • Marked as answer by kcoughenour Wednesday, March 17, 2010 7:54 AM
    Tuesday, March 16, 2010 9:51 PM
    Moderator

All replies

  • Hi,
     
    You're question is off-topic for this forum. Move your question to the DataSet forum:
    http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/threads

    cheers,

     --larsw
    Lars Wilhelmsen | Development Lead | KrediNor, Norway | Connected Systems MVP | http://larswilhelmsen.com/
    • Proposed as answer by _larsw Saturday, March 13, 2010 8:30 PM
    Saturday, March 13, 2010 8:29 PM
  • Thanks Kraig for getting my post into the correct forum for me!  Now that I am here, I was able to find a solution to my update problem.  It seems it was as simple as setting a primary key in my table.  I created an Index column and set it as my primary key, then I reestablished my data connection and wala, this time it created the UpdateCommand for me and everything works as it should.

    I am however still having problems with having NULL values in fields that I dont want left empty.  How can I catch these in order to not throw up an exception?  Thanks!
    Monday, March 15, 2010 6:56 AM
  • Without a primary key, ADO.NET cannot generate logic to update or delete a row in the database.  This behavior prevents developers from unintentionally updating or deleting multiple rows in their database.

    The strongly-typed DataSet designer sets the AllowDBNull property on DataColumns to True or False depending on whether or not the corresponding columns in your database allow nulls.  It sounds like your best bet may be to check those properties in your code and performing the appropriate action (setting a default, prompting the user, etc.) prior to trying to submit updates to the database.

    I hope this information proves helpful.


    David Sceppa
    Monday, March 15, 2010 10:07 PM
    Moderator
  • Thanks David!  That makes sense about the Primary Key.

    With my other problem, I guess thats what Im trying to find out.  How do I go about checking those properties and then prompting the user?  You have to understand that I am an absolute dummy when it comes to this.  I have no real programming experience.  I was just bored one day and decided to download VB 2008 Express and try and write an application becasue it is something that interested me.  The whole of my knowledge has been gained from searching through the MSDN help file and a whole lot of trial and error/headaches and sleepless nights.  So I apologize if I sound like a dummy, but its only becasue I am, haha.
    Tuesday, March 16, 2010 6:53 PM
  • No worries.  There's always a learning curve and it helps to know a little about your background.  Personally, I think that we learn a great deal through iterative development where we run code, see a problem, figure out what went wrong on our own or with help from others, address the problem, re-run the application and keep moving forward.

    It sounds like the error messages you're seeing right now talk about the database columns that require values.

    1.)  Do you already have controls bound to the corresponding columns in your DataSet?  If you don't, you need to either programmatically assign values to the columns in your DataSet or bind controls to those DataSet columns.

    2.)  Once you have controls bound to those DataSet columns, you can check to see whether or not the user has supplied values to those controls in your code, then let the user know what went wrong and what they should do about it.  For example, in the code you showed earlier, you could add something like the following just before:

    If Me.ContactNameTextBox.Text.Length = 0 Then
        MessageBox.Show("Contact Name requires a value", "Validation Error", _ 
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Me.ContactNameTextBox.Focus()
        Return
    End If

    I hope this information proves helpful.

    David Sceppa
    • Marked as answer by kcoughenour Wednesday, March 17, 2010 7:54 AM
    Tuesday, March 16, 2010 9:51 PM
    Moderator
  • Thanks David!  That will work perfect as that is how I have it setup... with TextBox controls bound to my dataset columns.  I was trying to make it too difficult when the solution was actually a very simple one.  Again... thanks for all your help!
    Wednesday, March 17, 2010 7:54 AM
  • No worries.  Thanks for letting us know that response helped.  Good luck with the next steps on your projects!
    David Sceppa
    Wednesday, March 17, 2010 4:50 PM
    Moderator