Saving Record Changes via Form Bound to DataSet

Donald Fisher

I have a form bound to a dataset and when I change a field and click save I get an error. An edited version of the default code for the save button is below:

Code Snippet

Private Sub TblSectionsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblSectionsBindingNavigatorSaveItem.Click

Try

Me.Validate()

Me.TblSectionsBindingSource.EndEdit()

Me.TblSectionsTableAdapter.Update(Me._MC2KtoOL.tblSections)

MsgBox("Update successful")

Catch ex As Exception

MsgBox("Update failed")

End Try

End Sub

I added the try method to catch the error before it errors out. Without the try method the following code line is highlighted:

Code Snippet

Me.TblSectionsTableAdapter.Update(Me._MC2KtoOL.tblSections)

I get the following code error:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I took for granted that the save button would work with default code but it doesn't. Any ideas Thanks for any help.

-- ADDED:

It's strange because I can click add record, add a record, click save and it saves to the dataset fine with no additional code However, I can't figure out how to pass the update to the back end Access database. It only updates the temporary dataset used by the form. Also, when a record is deleted and the save button clicked it also errors out.

rkimble

Yes, this has been addressed many times - simply searching the forums for that error message should return a number of results.

When you configured the TableAdapter, you used a complex SELECT statement (probably with a JOIN to another table) so the designer was not able to autogenerate the INSERT, UPDATE, and DELETE commands for you. You will need to define these on the TableAdapter yourself (there could be other reasons that the commands were not built for you, but this is one of the common ones).

Donald Fisher

I've seen that many others were having the same problems but am having trouble manually adding my SQL commands (or just don't know what to put in there). I've added the following UPDATE and DELETE commands but they don't seem to work as planned:

DELETE: DELETE FROM tblSections

INSERT: INSERT INTO tblSections (ENTRYID, [SECTION], FOLDER, STOREID) VALUES ( , , , )

SELECT: SELECT ENTRYID, [SECTION], FOLDER, STOREID FROM tblSections

UPDATE: UPDATE tblSections SET ENTRYID = ENTRYID, [SECTION] = [SECTION], FOLDER = FOLDER, STOREID = STOREID

I've never used SQL commands before so I'm sure these are way off.

On my form:

If I click DELETE for one of four records and then SAVE, close the form and reopen the form I find that all four records have been deleted from both the app and the back-end ACC DB. How do I tell it to only delete the one record that was intended to be deleted

If I edit one of four records and click SAVE, close the form and reopen the form I find that no changes have taken place in either the app or the back-end ACC DB. How do I tell it to update the changed record

If I click ADD NEW, enter data into the new record then click SAVE, close the form and reopen the form I find that the new record has been added to both the app and the back-end ACC DB (GREAT!); this is fine.

Please let me know where I'm going wrong. Thanks.

Rajneesh Narain

Hi Don!

All the data from your DB is being deleted because your SQL Command for DELETE does not filter anything. Try using an appropriate WHERE clause. Something like this:
DELETE * FROM tblSections where STOREID =

This will delete only those rows, which match STOREID=(your value). And not all the rows.

Once this is done, you will need to add a delete oleDBParameter, defining a value.

Hope this helps!
-Rajneesh

Donald Fisher

Thanks for the response. I think I understand how your solution would work. However, how would I tell the app to delete only those records from the dataset in which the delete button was clicked Would I add a field to the underlying table to mark the record for deletion and then run the delete SQL to delete records with an "X" (or something) in the delete column Is this a viable solution How would an expert compelte this task

Riquel Dong �C MSFT

Hi Donald,

Based on your post, you need to save the data change to the database. After the data in your dataset has been modified and validated, you want to send the updated data back to a database. In order to send the modified data to a database, you can call the Update method of a TableAdapter or data adapter. The adapter's Update method updates a single data table and executes the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table. I recommend you see these article about saving the data. Please visit here: http://msdn2.microsoft.com/en-us/library/ms171932(VS.80).aspx . These articles describe the concepts and tasks associated with saving data from your application to a database.

Hope this helps.

medel

hello, i have an almost the same problem but mine is i cannot save/ update any changes, my access2003 data is on network and i cannot update, save or delete anything. but if i add my access2003 data on my solution explorer its working. any help please

also the link you've given is not working. any more link

Donald Fisher

Ok, I'm using the following code to add a record to an Access database from a form:

Code Block

Dim ol As New Microsoft.Office.Interop.Outlook.Application

Dim olns As Microsoft.Office.Interop.Outlook.NameSpace

Dim txtENTRYID, txtSTOREID, txtSECTION, txtFOLDER

txtSECTION = InputBox("Enter the section name as you would like it to be displayed in the schedule [ALL CAPS]." & Chr(13) & Chr(13) & "Ensure this is the same name for a section already loaded in MC2K.", " Enter Section Name -")

If txtSECTION = "" Then

Dim CWSound As New System.Media.SoundPlayer(System.Environment.CurrentDirectory & "\Resources\WinCritStp.wav")

CWSound.Play()

MsgBox("You have either clicked CANCEL or attempted to load a blank [NULL] section name. This action has cancelled the current 'ADD SECTION' event.", MsgBoxStyle.OkOnly, " 'NULL SECTION' Error (or CANCEL) -")

Exit Sub

End If

txtFOLDER = InputBox("Enter the exact name of the folder on your public share for this section [ALL CAPS].", " Enter Task Folder Name -")

If txtFOLDER = "" Then

Dim CWSound As New System.Media.SoundPlayer(System.Environment.CurrentDirectory & "\Resources\WinCritStp.wav")

CWSound.Play()

MsgBox("You have either clicked CANCEL or attempted to load a blank [NULL] folder name. This action has cancelled the current 'ADD SECTION' event.", MsgBoxStyle.OkOnly, " 'NULL FOLDER' Error (or CANCEL) -")

Exit Sub

End If

olns = ol.GetNamespace("MAPI")

Dim fldFolder As Microsoft.Office.Interop.Outlook.MAPIFolder

fldFolder = ol.GetNamespace("MAPI").PickFolder

txtENTRYID = fldFolder.EntryID

txtSTOREID = fldFolder.StoreID

Try

frmMC2KtoOL_Sections.Visible = True

frmMC2KtoOL_Sections.MT2DataSet.tblSections.Rows.Add(txtSECTION, txtFOLDER, txtENTRYID, txtSTOREID)

frmMC2KtoOL_Sections.Close()

Dim CWSound As New System.Media.SoundPlayer(System.Environment.CurrentDirectory & "\Resources\WinExcl.wav")

CWSound.Play()

MsgBox("THE SELECTED MS OUTLOOK FOLDER HAS BEEN SAVED AS: " & Chr(13) & Chr(13) & "SECTION: " & txtSECTION & Chr(13) & Chr(13) & "FOLDER: " & txtFOLDER & Chr(13) & Chr(13) & "ENTRYID: " & txtENTRYID & Chr(13) & Chr(13) & "STOREID: " & txtSTOREID & Chr(13) & Chr(13) & "Click 'OK' to continue.", vbOKOnly, " Folder & Section Load Complete -")

Me.Visible = True

Catch EX As ConstraintException

frmMC2KtoOL_Sections.Visible = False

Me.Visible = True

Dim CWSound As New System.Media.SoundPlayer(System.Environment.CurrentDirectory & "\Resources\WinCritStp.wav")

CWSound.Play()

MsgBox("The current 'ADD SECTION' event has been cancelled due to an already existing 'ENTRYID'. The folder selected for addition is already loaded. No data has been added." & Chr(13) & Chr(13) & "Err: " & Err.Description, MsgBoxStyle.Exclamation, " ENTRYID Already Exists -")

End Try

Me.Visible = True

However, the record is not passed to the database. I think is has something to do with either the INSERT INTO or UPDATE commands on the dataset They are:

-----

INSERT INTO tblSections
(ENTRYID, [SECTION], FOLDER, STOREID)
VALUES ( , , , )

-----

UPDATE tblSections
SET ENTRYID = ENTRYID, [SECTION] = [SECTION], FOLDER = FOLDER, STOREID = STOREID

-----

Any ideas as to where I'm going wrong I had this set up and working a while back but had to recreate the form and re-establish the dataset and can't remember what my update and insert commands where.

Donald Fisher

Figured this one out. Added some save code to frmMC2KtoOL_Sections FormClosed event and it works fine now.

Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet

Re: Visual Basic Language Saving Record Changes via Form Bound to DataSet