locked
Why cannot update the table in Access database? RRS feed

  • Question

  •  

    Dear all,

     

    I am using VB2005 and Access 2000 to develop an application. Now I have a problem to work with Access.

     

    The scenario is that I want to store a query result into an existing and empty table in the same database. The queary is derived from several related tables and stored in a dataset;

     

    Code Snippet

    Dim da As OleDbDataAdapter = New OleDbDataAdapter(sSQL, empConnstr)
      Dim myDataSet As New DataSet()

      da.FillSchema(myDataSet, SchemaType.Source, "Project")
      da.Fill(myDataSet, "Project")

     

    and then I want to store all rows of "myDataSet" into another existing empty table("project") in the same Access database. the table in "myDataSet" has the same table structure as the empty table.

    What I tried is to copy all the field value of "myDataSet" to "dbDataSet", which is collection of all fields of "project" with empty row.

     

           

    Code Snippet

       Dim na As OleDbDataAdapter = New OleDbDataAdapter()

              na.SelectCommand = New OleDbCommand("select * from Project", empOLEDB)
                Dim builder As New OleDbCommandBuilder(na)

              Dim dbDataSet As New DataSet()

              na.Fill(dbDataSet, "Project")

     

              Dim i As Integer

              Dim tbl As DataTable = dbDataSet.Tables(0)

     

                For i = 0 To myDataSet.Tables(0).Rows.Count - 1

                    Dim dr As DataRow = myDataSet.Tables(0).Rows(i)
                      Dim nr As DataRow = tbl.NewRow

                    ' assign field value of "myDataSet" to corresponding "dbDataSet"

                    nr("name") = dr("name")

    ...................
                       nr("level") = dr("level")

     

                    tbl.Rows.Add(nr)

                Next

     

                na.Update(dbDataSet, "Project")

     

    there is no error reported and I have checked the "dbDataSet" has the same rows and columns as the "myDataSet". but there is no change in the "Project" table in the database, I was expecting to see some new rows!

     

    By the way, I also tried to use "na.updata(myDataSet, "Project")" to update the database; it has no difference.

     

    Please, any help is appreciated. Thank you in advance.

     

    Tuesday, July 3, 2007 9:15 AM

All replies

  • Hi

     

    I am not sure why your code is not working, but I would have thought doing this transaction via Access would have been a better way of doing it.

     

    If you used a SELECT INTO command you could 'copy' all of your data from one table into another table.

     

    e.g.

    INSERT INTO Projects
      SELECT  *

    FROM tmpProjects;

     

    You could then do something like this

     

    Dim strSQL as String = "INSERT INTO Projects  SELECT  * FROM tmpProjects;"

    oledbConnection = New oledbConnection(empConnstr)

    oledbCommand = New oledbCommand(strSQL, oledbConnection)

    oledbConnection.Open()

    oledbCommand.ExecuteNonQuery()

     

    This in effect would get Access to do all the work instead of looping through the dataset and adding the records manually.

     

    Hope this helps

     

    Richard


      

     

    Tuesday, July 10, 2007 3:29 PM