?
Solved

Data table not being updated in my database using OleDbDataAdapter

Posted on 2009-02-19
13
Medium Priority
?
703 Views
Last Modified: 2013-11-10
I'm trying to update an MS Access table from my Visual Basic program.  Below is a copy of the pertinent code.  
1. I create a data set to hold the 4 tables.
2. I then create OldeDb DataAdapters for the tables.
3. Using OleDbCommand, I fill the dataAdapter for the table.  I have confirmed that this code is working by verifying the number of rows in the table in the dataset.
4. The SurveySent table does not contain any records so I create a new DataRow, set the field data.  I then call the Rows.Add method on the DataSet to add the row to the table in the dataset.  To write the changes back to the physical database, I call the AcceptChanges method on the DataSet and then call the Update Method on the SurveySent DataAdapter.

No errors are generated.  The table in the MS Access database is not updated and I can't figure out why.  Any help would be greatly appreciated.
' Create a DataSet to hold the Survey data
                Dim dsSurveys As DataSet = New DataSet()
                dsSurveys.Tables.Add("Surveys")
                dsSurveys.Tables.Add("Evaluator")
                dsSurveys.Tables.Add("SurveySent")
                dsSurveys.Tables.Add("Presenter")
 
                ' Create the Data Adapters
                Dim dataSAdapter = New OleDb.OleDbDataAdapter() ' Surveys table
                Dim dataEAdapter = New OleDb.OleDbDataAdapter() ' Evaluator table
                Dim dataSSAdapter = New OleDb.OleDbDataAdapter() ' SurveySent table
                Dim dataPAdapter = New OleDb.OleDbDataAdapter()  ' Presenter table
 
                ' Load Surveys Table                
                strSQL = "Select * from Surveys Where SurveyProjectId=" & CStr(surveyprojectid)
                Dim cmdSurveys As OleDbCommand = New OleDbCommand(strSQL, connection)
                dataSAdapter.SelectCommand = cmdSurveys
                dataSAdapter.Fill(dsSurveys.Tables("Surveys"))
 
                ' Load the Evaluator records that match the presenter id from the survey record
                strSQL = "Select * From Evaluator Where PresenterID=" & CStr(presenterid)
                cmdSurveys.CommandText = strSQL
                dataEAdapter.SelectCommand = cmdSurveys
                dataEAdapter.Fill(dsSurveys.Tables("Evaluator"))
 
                ' Load the Presenter record that matches the presenter id from the survey record
                strSQL = "Select * From Presenter Where PresenterID=" & CStr(presenterid)
                cmdSurveys.CommandText = strSQL
                dataPAdapter.SelectCommand = cmdSurveys
                dataPAdapter.Fill(dsSurveys.Tables("Presenter"))
 
                ' Load the SurveySent record for the evaluator and survey
                strSQL = "Select * from SurveySent Where ProjectSurveyId=" & CStr(surveyprojectid) & " And SurveyID=" & CStr(surveyid) & " And EvaluatorID=" & CStr(evaluatorid)
                cmdSurveys.CommandText = strSQL
                dataSSAdapter.SelectCommand = cmdSurveys
                dataSSAdapter.Fill(dsSurveys.Tables("SurveySent"))
 
                ' Create survey sent record
                Try
                      drSurveySent = dsSurveys.Tables("SurveySent").NewRow()
                      drSurveySent("ProjectSurveyID") = surveyprojectid
                      drSurveySent("SurveyID") = surveyid
                      drSurveySent("EvaluatorID") = evaluatorid
                      drSurveySent("SentDate") = currdate
                      dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
                      dsSurveys.Tables("SurveySent").AcceptChanges()
                      dataSSAdapter.Update(dsSurveys.Tables("SurveySent"))

Open in new window

0
Comment
Question by:dyarosh
13 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 900 total points
ID: 23690438
I'm not sure, but can you leave this statement out of your code and retry it :
dsSurveys.Tables("SurveySent").AcceptChanges()
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 600 total points
ID: 23690459
I think the updatecommand and the InsertCommand might not have been generated. Try first by stepping through the code to see if these commands are generated as desired.
0
 
LVL 23

Expert Comment

by:irudyk
ID: 23691610
Maybe try defining drSurveySent as a DataRow.  Also, maybe the Update and AcceptChanges need to be modified/swapped.  A couple of code changes you could test are listed below:
' Create survey sent record
Try
    Dim drSurveySent As DataRow = dsSurveys.Tables("SurveySent").NewRow()
    drSurveySent("ProjectSurveyID") = surveyprojectid
    drSurveySent("SurveyID") = surveyid
    drSurveySent("EvaluatorID") = evaluatorid
    drSurveySent("SentDate") = currdate
    dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
    dsSurveys.Tables("SurveySent").AcceptChanges()
    dataSSAdapter.Update(dsSurveys.Tables("SurveySent"))
 
'~~~~~~~~~~~~~~~~~~~~
'or try the following
'~~~~~~~~~~~~~~~~~~~~
 
' Create survey sent record
Try
    Dim drSurveySent As DataRow = dsSurveys.Tables("SurveySent").NewRow()
    drSurveySent("ProjectSurveyID") = surveyprojectid
    drSurveySent("SurveyID") = surveyid
    drSurveySent("EvaluatorID") = evaluatorid
    drSurveySent("SentDate") = currdate
    dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
    dataSSAdapter.Update(dsSurveys.GetChanges())
    dsSurveys.AcceptChanges()

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dyarosh
ID: 23723770
The insert and update commands have not been generated.  Do you know how to do this?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23723882
These commands are generated automatically when you create a dataadapter in design time and you have a primary key field in the select command. You can generate these commands using the SQLCommandBuilder as described in the article

http://msdn.microsoft.com/en-us/library/tf579hcz(VS.80).aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
0
 

Author Comment

by:dyarosh
ID: 23748111
I'm still not doing something right because the Insert command is not working.  I've attached the code I'm using.  I've omitted setting the Update Command since the test data that I'm using should be inserting a new record into the database.  I don't receive an error message but data is not inserted.  When debugging, I can't "Step Into" the

dataSSAdapter.Update(dsSurveys, "SurveySent")
line.  Any help would be greatly appreciated.

    Function SendSurveyEmail(ByVal dr As DataRow, ByRef xmlparameters() As String) As Boolean
        Dim surveyprojectid As Integer
        Dim surveyid As Integer
        Dim projectname As String
        Dim dpath As String
        Dim passing As Integer
        Dim monthtosend As Integer
        Dim jointsp As String
        Dim presenterid As Integer
        Dim evaluatorid As Integer
        Dim dateTestTaken As Date
        Dim currdate As Date = Today()
        Dim basepath As String
        Dim drPresenter As DataRow
        Dim drSurveySent As DataRow
        Dim evaluatorName As String
        Dim msg As String
 
        basepath = xmlparameters(1)
 
        ' Pull info out of datarow
        surveyprojectid = dr.ItemArray(0)
        projectname = dr.ItemArray(1)
        dpath = dr.ItemArray(2)
        passing = dr.ItemArray(3)
        monthtosend = dr.ItemArray(4)
 
        Dim connStr As String
 
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & basepath & "\" & dpath
        Using connection As New OleDb.OleDbConnection(connStr)
            Dim strSQL As String
 
            Try
                connection.Open()
 
                ' Create a DataSet to hold the Survey data
                Dim dsSurveys As DataSet = New DataSet()
                dsSurveys.Tables.Add("Surveys")
                dsSurveys.Tables.Add("Evaluator")
                dsSurveys.Tables.Add("SurveySent")
                dsSurveys.Tables.Add("Presenter")
 
                ' Create the Data Adapters
                Dim dataSAdapter = New OleDb.OleDbDataAdapter() ' Surveys table
                Dim dataEAdapter = New OleDb.OleDbDataAdapter() ' Evaluator table
                Dim dataSSAdapter = New OleDb.OleDbDataAdapter() ' SurveySent table
                Dim dataPAdapter = New OleDb.OleDbDataAdapter()  ' Presenter table
 
                ' Load Surveys Table                
                strSQL = "Select * from Surveys Where SurveyProjectId=" & CStr(surveyprojectid)
                dataSAdapter.SelectCommand = New OleDbCommand(strSQL, connection)
                dataSAdapter.Fill(dsSurveys.Tables("Surveys"))
 
                ' Create the InsertCommand for the SurveySent Table.
                strSQL = "INSERT INTO SurveysSent (ProjectSurveyID, SurveyID, EvaluatorID, SentDate, Followup) VALUES (?,?,?,?,?)"
                dataSSAdapter.InsertCommand = New System.Data.OleDb.OleDbCommand(strSQL, connection)
                dataSSAdapter.InsertCommand.Parameters.Add("@ProjectSurveyID", OleDb.OleDbType.Integer, 0, "ProjectSurveyID")
                dataSSAdapter.InsertCommand.Parameters.Add("@SurveyID", OleDb.OleDbType.Integer, 0, "SurveyID")
                dataSSAdapter.InsertCommand.Parameters.Add("@EvaluatorID", OleDb.OleDbType.Integer, 0, "EvaluatorID")
                dataSSAdapter.InsertCommand.Parameters.Add("@SentDate", OleDb.OleDbType.Date, 0, "SentDate")
                dataSSAdapter.InsertCommand.Parameters.Add("@Followup", OleDb.OleDbType.Integer, 0, "Followup")
 
                ' Loop through the surveys
                For Each drSurvey As DataRow In dsSurveys.Tables("Surveys").Rows
                    surveyid = drSurvey("ID")
                    jointsp = drSurvey("JointSponsor")
                    presenterid = drSurvey("PresenterID")
 
                    ' Load the Evaluator records that match the presenter id from the survey record
                    strSQL = "Select * From Evaluator Where PresenterID=" & CStr(presenterid)
                    dataEAdapter.SelectCommand = New OleDbCommand(strSQL, connection)
                    dataEAdapter.Fill(dsSurveys.Tables("Evaluator"))
 
                    ' Load the Presenter record that matches the presenter id from the survey record
                    strSQL = "Select * From Presenter Where PresenterID=" & CStr(presenterid)
                    dataPAdapter.SelectCommand = New OleDbCommand(strSQL, connection)
                    dataPAdapter.Fill(dsSurveys.Tables("Presenter"))
 
                    ' If Presenter record not found, send email for attention
                    If dsSurveys.Tables("Presenter").Rows.Count = 0 Then
                        SendErrorEmail(drSurvey, projectname, dpath, xmlparameters)
                    Else
                        drPresenter = dsSurveys.Tables("Presenter").Rows(0)
                        ' Loop through each evaluator that matches the presenter id for the survey
                        For Each drEvaluator As DataRow In dsSurveys.Tables("Evaluator").Rows
                            evaluatorid = drEvaluator("EvaluationID")
                            evaluatorName = Trim(drEvaluator("FirstName")) & " " & Trim(drEvaluator("LastName"))
 
                            ' Check if months to send survey has been reached from when the test was taken
                            dateTestTaken = drEvaluator("Date")
                            If dateTestTaken.AddMonths(monthtosend) <= currdate Then
                                ' Load the SurveySent record for the evaluator and survey
                                strSQL = "Select * from SurveySent Where ProjectSurveyId=" & CStr(surveyprojectid) & " And SurveyID=" & CStr(surveyid) & " And EvaluatorID=" & CStr(evaluatorid)
                                dataSSAdapter.SelectCommand = New OleDbCommand(strSQL, connection)
                                dataSSAdapter.Fill(dsSurveys.Tables("SurveySent"))
 
                                ' Check if survey sent record exists
                                If dsSurveys.Tables("SurveySent").Rows.Count = 0 Then
 
============== Everything works fine until here =======================
                                    ' Create survey sent record
                                    Try
                                        drSurveySent = dsSurveys.Tables("SurveySent").NewRow()
                                        drSurveySent("ProjectSurveyID") = surveyprojectid
                                        drSurveySent("SurveyID") = surveyid
                                        drSurveySent("EvaluatorID") = evaluatorid
                                        drSurveySent("SentDate") = currdate
                                        dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
                                        dsSurveys.Tables("SurveySent").AcceptChanges()
                                        dataSSAdapter.Update(dsSurveys, "SurveySent")
 
=======================================================================
                                                                                If SendEmail(xmlparameters, drEvaluator("Email"), evaluatorName, dateTestTaken, drPresenter("Sponsor"), drPresenter("EvaluationName"), drPresenter("Program"), jointsp, surveyprojectid, surveyid, evaluatorid) Then
                                            ' Log survey sent
                                            msg = "Survey sent to " & evaluatorName & " at " & drEvaluator("Email") & " SPID=" & surveyprojectid & "; SID=" & surveyid & "; EID=" & evaluatorid
                                            WriteToLogFile(LogFileName, msg)
                                        End If
 
 
                                    Catch ex As Exception
                                        WriteToLogFile(LogFileName, ex.ToString & Environment.NewLine)
                                    End Try
                                Else
                                    'SendEmail(xmlparameters, drEvaluator, drPresenter, jointsp, surveyprojectid, surveyid, evaluatorid)
                                End If
                            End If
                            ' Clear the surveys sent table
                            dsSurveys.Tables("SurveySent").Clear()
                        Next
                    End If
                    ' Clear evaluator and presenter tables
                    dsSurveys.Tables("Evaluator").Clear()
                    dsSurveys.Tables("Presenter").Clear()
                Next
                connection.Close()
                Return True
 
            Catch ex As Exception
                WriteToLogFile(LogFileName, ex.ToString & Environment.NewLine)
                Return False
            End Try
        End Using
    End Function

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23751016
Do not add table names to dataset before filling. Try with this

dataPAdapter=new dataadapter("select * ...", "connectionstring")


This should generate all the commands for you.
0
 

Author Comment

by:dyarosh
ID: 23756020
In trying to simplify things I pulled out all of the other tables and hard coded the INSERT, UPDATE, and DELETE Commands.  The code is attached.  In debugging the code I verified that the INSERT, UPDATE, and DELETE commands are there.
The MsgBox on the Fill showed 1 record (the db only has one record).
The drSurveySent DataRow holds the record from the database and the data is correct.  The MsgBox("Survey ID:" ..) shows the correct survey id from the database.  When the evaluator id is changed to 111, the change is reflected in the drSurveySent DataRow and the DataRow Status is Modified.  After calling AcceptChanges() and then the Update(), the data is not modified in the database.  The MsgBox shows 0 as the return value for the Update() call.
The same holds true when doing the Insert.  The DataRow shows the Row Status as Added but the AcceptChanges doesn't appear to be doing anything and Insert() call does not insert data into the database.
Any ideas?

    Function SendSurveyEmail(ByVal dr As DataRow, ByRef xmlparameters() As String) As Boolean
        Dim surveyprojectid As Integer
        Dim projectname As String
        Dim dpath As String
        Dim passing As Integer
        Dim monthtosend As Integer
        Dim currdate As Date = Today()
        Dim basepath As String
        Dim drSurveySent As DataRow
 
        basepath = xmlparameters(1)
 
        ' Pull info out of datarow
        surveyprojectid = dr.ItemArray(0)
        projectname = dr.ItemArray(1)
        dpath = dr.ItemArray(2)
        passing = dr.ItemArray(3)
        monthtosend = dr.ItemArray(4)
 
        Dim connStr As String
 
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & basepath & "\" & dpath
        Using connection As New OleDb.OleDbConnection(connStr)
            Dim strSQL As String
 
            Try
                connection.Open()
 
                ' Create a DataSet to hold the Survey data
                Dim dsSurveys As DataSet = New DataSet()
                dsSurveys.Tables.Add("SurveySent")
 
                ' Create the Data Adapters
                Dim dataSSAdapter = New OleDb.OleDbDataAdapter() ' SurveySent table
 
                ' Create the InsertCommand for the SurveySent Table.
                strSQL = "INSERT INTO SurveySent (ProjectSurveyID, SurveyID, EvaluatorID) VALUES (1,2,3)"
                dataSSAdapter.InsertCommand = New System.Data.OleDb.OleDbCommand(strSQL, connection)
                ' Create the UpdateCommand for the SurveySent Table
                strSQL = "UPDATE SurveySent Set EvaluatorID=23 WHERE ID=6"
                dataSSAdapter.UpdateCommand = New System.Data.OleDb.OleDbCommand(strSQL, connection)
                ' Create the DeleteCommand for the SurveySent Table
                strSQL = "DELETE FROM SurveySent WHERE ID=6"
                dataSSAdapter.DeleteCommand = New System.Data.OleDb.OleDbCommand(strSQL, connection)
                ' Create the SelectCommand for the SurveySent Table
                strSQL = "SELECT * FROM SurveySent"
                dataSSAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSQL, connection)
                ' Get records from the SurveySent Table
                MsgBox("Records Loaded: " & dataSSAdapter.Fill(dsSurveys.Tables("SurveySent")))
 
                ' Update survey sent record
                drSurveySent = dsSurveys.Tables("SurveySent").Rows(0)
                MsgBox("Survey ID: " & drSurveySent("ID"))
                drSurveySent("EvaluatorID") = 111
                dsSurveys.Tables("SurveySent").AcceptChanges()
                MsgBox("Records Updated: " & dataSSAdapter.Update(dsSurveys.Tables("SurveySent")))
                MsgBox("Changed ID: " & drSurveySent("EvaluatorID"))
 
                ' Create survey sent record
                Try
                    drSurveySent = dsSurveys.Tables("SurveySent").NewRow()
                    drSurveySent("ProjectSurveyID") = 99
                    drSurveySent("SurveyID") = 100
                    drSurveySent("EvaluatorID") = 101
                    dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
                    dsSurveys.Tables("SurveySent").AcceptChanges()
                    MsgBox("Records Inserted: " & dataSSAdapter.Update(dsSurveys.Tables("SurveySent")))
 
                Catch ex As Exception
                    WriteToLogFile(LogFileName, ex.ToString & Environment.NewLine)
                End Try
                connection.Close()
                Return True
 
            Catch ex As Exception
                WriteToLogFile(LogFileName, ex.ToString & Environment.NewLine)
                Return False
            End Try
        End Using
    End Function

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23756096
I do not understand why it is not working. The reason could be that the dataadapter works with parameters rather than hardcoded sql. So the starting point now is to leave everything else out, create a new dataadapter as i advised, add a new row to the table after filling it and then call the update on the dataset not datatable.
0
 

Author Comment

by:dyarosh
ID: 23756844
Changed the code to:

Dim dataSSAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM SurveySent", connection) ' SurveySent table
MsgBox("Records Loaded: " & dsSurveys.Tables("SurveySent").Rows.Count())
MsgBox shows 0 records.  In debugging, the dataadapter shows a select command but does not show an Insert, Update or Delete command.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23756964
Can you zip the whole project(or a simplified version) with the database and upload it?
0
 

Author Comment

by:dyarosh
ID: 23760076
Finally got it working.  Attached is the code.  I used your suggestion for creating the dataAdapter.  I had to add a dataAdapter.TableMappings.Add("Table", "SurveySent").  I then added the OleDbCommandBuilder(dataAdapater) and this created the INSERT, UPDATE and DELETE commands.  Finally, I had to remove the .AcceptChanges() call.
        Dim connStr As String
 
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & basepath & "\" & dpath
        Dim strSQL As String
 
        Try
 
            ' Create the Data Adapters
            strSQL = "SELECT * FROM SurveySent"
            Dim dataSSAdapter = New OleDb.OleDbDataAdapter(strSQL, connStr)
 
            ' Map "Table" to "SurveySent"
            dataSSAdapter.TableMappings.Add("Table", "SurveySent")
 
            ' Create Command builder
            Dim builder As New OleDbCommandBuilder(dataSSAdapter)
 
            ' Create a DataSet to hold the Survey data
            Dim dsSurveys As DataSet = New DataSet()
 
            ' Fill the DataAdapter
            dataSSAdapter.Fill(dsSurveys)
 
            Console.WriteLine("*** INSERT ***")
            Console.WriteLine(builder.GetInsertCommand.CommandText)
            Console.WriteLine("*** UPDATE ***")
            Console.WriteLine(builder.GetUpdateCommand.CommandText)
            Console.WriteLine("*** DELETE ***")
            Console.WriteLine(builder.GetDeleteCommand.CommandText)
 
            Console.WriteLine("*** Records Loaded ***")
            Console.WriteLine(dsSurveys.Tables("SurveySent").Rows.Count())
 
            drSurveySent = dsSurveys.Tables("SurveySent").NewRow()
            drSurveySent("ProjectSurveyID") = surveyprojectid
            drSurveySent("SurveyID") = 100
            drSurveySent("EvaluatorID") = 101
            drSurveySent("SentDate") = currdate
            dsSurveys.Tables("SurveySent").Rows.Add(drSurveySent)
 
            drSurveySent = dsSurveys.Tables("SurveySent").Rows(0)
            drSurveySent("EvaluatorID") = 202
 
 
            dataSSAdapter.Update(dsSurveys)
            Return True
 
        Catch ex As Exception
            WriteToLogFile(LogFileName, ex.ToString & Environment.NewLine)
            Return False
        End Try
    End Function

Open in new window

0
 

Author Closing Comment

by:dyarosh
ID: 31548988
Thank you for all your help.  Your suggestions helped me to narrow down the problem and then find the solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question