save not working ...... please help

Hi Everybody,

I am writing an application using the bindingnavigator and the oledbDataAdapter. I am not able to save the changes to the actual access database. Please help me. The code follows.

What do I mean when I say that I am not able to save the changes

I mean that they are not reflected in the database when I reopen it.

Does an error occur

No error occurs.

If not then how am I determining that the save operation didn't work

No changes are reflected in the database.

What value does Update return

It returns zero.

So, there might be a problem with the update function.

I am binding the richtext box with the bindingsource. And the bindingsource is the datatable. Are the values in the datatable automatically updates when I change a value in the richtext box If not how can I do that.

Thank you
Vishal

I have opened a connection and the following code binds the database with the bindingnavigator using a oledbDataAdapter. And also to the text box. Where we can edit the fields and later on save them.

Code:


Me.objDataAdapter = New OleDbDataAdapter("Select * from datatable", Me.objConnection)

    Me.objDataAdapter.Fill(Me.objDataTable)

    Me.BindingSource1.DataSource = Me.objDataTable
    Me.BindingNavigator1.BindingSource = Me.BindingSource1
    Me.BindingNavigator1.Dock = DockStyle.None
    Me.Controls.Add(Me.BindingNavigator1)

    Me.Controls.Add(Me.RichTextBox1)
    Me.RichTextBox1.DataBindings.Add(New Binding("Text", Me.BindingSource1, "audit_task_no", True))

The following is the save routine.

Code:



    Try
      Me.objDataAdapter.Update(Me.objDataTable)
      MsgBox("Changes Successful Saved ")
    Catch ex As Exception
      MsgBox("Saved Failed")
    End Try

    Try
      Me.objDataTable.AcceptChanges()
    Catch ex As Exception
      MsgBox("Update failed")
    End Try

.NET Development1

good you have justified your question - good stuff :-)

ok firstly, remove acceptchanges()

secondly, be sure that you are not overwritting the database so in your solution explorer, if you have added a database file in there, select it, view its properties and set the "copy to output" to "copy if newer" or "do not copy".

thirdly, has the values actually been changed so it can update the database

forthly, what is your update statement

What happens now

Thanks a lot for your reply....

I have removed the acceptchanges() now. I am actually opening a database connection dynamically using an oledbconnection, so, no database files are there in the solution explorer.

During execution I edit the entries in the richtextbox but they are not updated to the database.

My update statement is:

Me.objDataAdapter.Update(Me.objDataTable)

There is still the same problem. I am new to VB, so, any help would be really appreciated.

can you show the Update command you are using if it helps, feel free to send over the project, with the database file, via email (email in profile) and I will be sure to post back the solution.

modify your save command to this:

Me.objDataAdapter.UpdateCommand = New OleDbCommand("UPDATE [datatable] SET Audit_Task = , Source = , TEAC_Component = , STATUS = , FACULTY_Response = WHERE [Audit_Task_No] = ")
Me.objDataAdapter.UpdateCommand.Connection = Me.objConnection
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox2.Text.Length, "Audit_Task")
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox3.Text.Length, "Source")
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox4.Text.Length, "TEAC_Component")
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox5.Text.Length, "Status")
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox6.Text.Length, "Faculty_Response")
Me.objDataAdapter.UpdateCommand.Parameters.Add( , OleDbType.Variant, Me.RichTextBox1.Text.Length, "Audit_Task_No")
Me.objDataAdapter.UpdateCommand.Connection.Open()
Me.objDataAdapter.Update(Me.ObjDataSet) ', "datatable")

MsgBox("Changes Successful Saved")
Me.objDataAdapter.UpdateCommand.Connection.Close()

modify your load/opening database/filling dataset code to this:

Me.objConnection.Open()

'Fill the objDataSet

Me.objDataAdapter.Fill(Me.ObjDataSet) ', "datatable")
Me.objConnection.Close()
..
..

Me.BindingSource1.DataSource = Me.ObjDataSet.Tables(0)

after this, when you are adding the databindings to the rich text box control, take out the "datatable." string and just leave it with fieldnames only, not tablename.fieldname, just fieldname

I'm having same problem and have spent all morning looking for a working solution. What would I need to add to the following code behind my save button to actually save record additions/deletions/updates to a backend ACC DB

Code Snippet

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

Me.Validate()

Me.TblSectionsBindingSource.EndEdit()

Me.TblSectionsTableAdapter.Update(Me._MC2KtoOL_tblSections.tblSections)

End Sub

Right now it errors on line 5 when a record is changed and the save button clicked stating that "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." I've tried several links to other postings and have had no luck at all. How do I set up the Update etc. commands Thanks for any help.

you would do this through the dataset/tableadapter designer when you created the TableAdapter. View the TableAdapter/Dataset designer and in the tableadapter properties, select the UpdateCommand and create an Update command or re-create the tableadapter/go through the wizard to generate the UPDATE/DELETE/SELECT and INSERT commands

Ok...This is what I do:

1. Click "Add new data source..."

2. Select "Database" and click NEXT

3. Click "New Connection...", browse to my Access.mdb file, clear username and password fields and click "Test Connection" (all is well)

4. Click OK and then NEXT

5. Click YES to copy the data file to the project's output directory

6. Click "+" next to "Tables" and place check box next to the table "tblSections" that I want as my dataset (there are four columns and four rows [records] currently in the table with one primary key [ENTRYID])

7. Leave default dataset name and click FINISH (receive error that "Some updating commands could not be generated automatically. The database returned the following error: Dynamic SQL generation for the UpdateCommande is not supported against a SelectCommand that does not return any key column information."

8. Right-click dataset and select "Preview Data..." then click PREVIEW and data displays correctly (four columns and four rows); click CLOSE

9. Right-click dataset and select "Edit DataSet with Designer"

10. Right-click "tblSectionsTableAdapter" and select "Configure"

11. The following SQL statement is currently present: "SELECT ENTRYID, [SECTION], FOLDER, STOREID FROM tblSections"

12. Click ADVANCED OPTIONS; option to "Refresh the data table" is grayed out and cannot be checked; "Generate Insert, Update and Delete statements" is checked; "Use optimistic concurrency" is checked

13. Click NEXT; "Fill a DataTable" is checked and "Method name" is "Fill"; "Return a DataTable" is checked and "Method name" is "GetData"; "Create methods to send updates directly to the databas (GenerateDBDirectMethods)" is checked

14. Click NEXT; wizard results show that Update and Delete statements are not supported agains a SelectCommand that does not return any key column information; all others have green check marks

15. Click FINISH; receive error that "An unexpected error has occurred. Error Message: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

At this point there have been so many errors I won't even continue to creating the form etc. as I know it won't work. Can anyone tell me what has gone wrong so far in the process Do I need to add some statements to the default SQL statement that was generated Any help would be greatly appreciated.

UPDATE:

Ok. I've manually added the UPDATE and INSERT commands to my dataset but don't know if they are correct as they are not updating/adding the data to my ACC DB. Also, when I click the delete button on the form all records are deleted These are all the commands:

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.