Hi Paul (or any other Access/Vb.Net Pro)
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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
Access/VB.Net Question for Paul Domag - if Possible
Pedro Coelho
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
Cyberpro60
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
Mark Boehlen
Paul,
Thank you ever so much for this!!!!!
I'm going to get back on this!!!
engmohamedsalah
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
Karen C
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.