none
Access/VB.Net Question for Paul Domag - if Possible RRS feed

  • Question

  • Hi Paul (or any other Access/Vb.Net Pro)

     

    I’ve seen you leave some wonderful elementary examples of updating Access databases doing OLEDB table updates via DATASETS. A difficultly is that when I apply them in complex situations they don’t work. I can update an existing Access table when I add a new record. That’s not a problem.

     

    Other scenarios fail such as:

     

    1.) Adding a column to a table in a dataset and updating the table.

     

    2.) Bringing in what I think of as a “template table” that is one that has a standard structure but no data, copy the table and adding the copy to dataset. Attempting to write this new table out will drive the command builder crazy.

     

    In my ever so limited experience, I’m finding these things to be really challenging.

     

    Any help would be appreciated and I have on example of code that I posted today.

     

    Thanks,

     

    Renee

    Tuesday, January 10, 2006 3:14 AM

Answers

  • Hi,

    Sorry for the lengthy time to reply to this message. Just had a very busy time and a holiday hangover.

    I just read the code that you sent me, well the first thing that I noticed is that you are using a commandbuilder against an adapter which has no SelectCommand defined. You must first define the selectcommand of an adapter for your commandbuilder to work. This is because the command builder bases its generation of delete,update and insert commands on your select command. It gets the metadata of your select command and transform it into the other commands needed:

    Dim adapter as new OleDbAdapter("Select * From Table1", connection)
    Dim cmdbldr as new OleDbCommandBuilder(adapter)

    I think this is the reason why your getting all of these errors. If that doesn't solve it just reply to this post so that I can further check your code out.

     

    cheers,

    Paul June A. Domag

    Friday, January 13, 2006 5:13 PM

All replies

  • Hi,

    Sorry for the lengthy time to reply to this message. Just had a very busy time and a holiday hangover.

    I just read the code that you sent me, well the first thing that I noticed is that you are using a commandbuilder against an adapter which has no SelectCommand defined. You must first define the selectcommand of an adapter for your commandbuilder to work. This is because the command builder bases its generation of delete,update and insert commands on your select command. It gets the metadata of your select command and transform it into the other commands needed:

    Dim adapter as new OleDbAdapter("Select * From Table1", connection)
    Dim cmdbldr as new OleDbCommandBuilder(adapter)

    I think this is the reason why your getting all of these errors. If that doesn't solve it just reply to this post so that I can further check your code out.

     

    cheers,

    Paul June A. Domag

    Friday, January 13, 2006 5:13 PM
  • Paul,

    Thank you ever so much for this!!!!!

    I'm going to get back on this!!!

     

    Thursday, January 19, 2006 6:53 PM
  • Gee, I wish this were as simple as you made it sound.

    Here is a sample routine with the fixes you suggested:

    Public Function CreateCategory(ByVal NewCategoryName As String) As Boolean

            Dim st As New DataTable()

            st = GetTable(Common.sAppTblNames.cMasterTbl, False)

            Dim dt As DataTable = st.Copy

            st.Dispose()

            dt.TableName = NewCategoryName

     

            Using adapt As New OleDbDataAdapter("Select * From " & NewCategoryName, con)

                Using CmdBuilder As New OleDbCommandBuilder(adapt)

                    Try

                        With Adapt

                            .SelectCommand = New OleDbCommand("Select * from [" & dt.TableName & "]", con)

                            .UpdateCommand = CmdBuilder.GetUpdateCommand

                        End With

                        con.Open()

                        adapt.Update(dt)

                        Dataset.AcceptChanges()

                        con.Close()

                        Return True

                    Catch e As Exception

                        MsgBox(e.Message, MsgBoxStyle.Exclamation, "ADONET.CreateCategory")

                        Return False

                    End Try

                End Using

            End Using

        End Function

    I get an exception at the update command.....

    "Dynamic SQL Generation is not supported against a Select Command that does not return any base table information.....

    Of note, I played with this a bit and the table that I copied - does have the requisited base table information to derive the needed metadata and that was not copied with the st.Copy.

    Thursday, January 19, 2006 7:12 PM
  • Hi,

    Its either your select statement is invalid or your table doesn't contain a primary key. In this case the updatecommand was not generated. Also you don't need to set your Updatecommand. Once you pass it as a parameter in the constructor of the adapter, the adapter automatically configures your Update, Delete Commands. But always be sure that your select statement returns a valid datasource and it has a primarykey. If your table doesn't contain a primary key then you'll have to set the UpdateCommand manually...

     

     

    cheers,

    Paul June A. Domag

    Monday, January 23, 2006 3:40 AM
  • Hey Paul,

    I want to thank you all the hard work.

    I'm right in the middle of customer nightmare and I will return to the this at a more propitious time.

    Again, thank you.

    renee

     

    Friday, January 27, 2006 1:20 AM