I originally posted this problem a few weeks ago but never got it solved.
I moved on to other parts of the app development but this is now critical!
dsFinishes does fill properly, is used successfully and accepts new info.
But, the 'Update' does not update the db.
This is the error from the "Try ... Catch"
"Dynamic SQL generation is not supoorted against a Select Cammand that does
not return any base table information"
The db is Access 2010 and it has a primary key ('ID') generated by Access.
There are several tables in the db but this dataset has only the one. There
are only 2 columns: "Finish" and "ID"
strSelect = "SELECT * FROM tblFinishes ORDER BY ID"
daFinishes = New OleDb.OleDbDataAdapter(strSelect, con)
MaxRowsFinishes = dsFinishes.Tables("tblFinishes").Rows.Count
Sub AddElement(ByVal newItem As String)
Dim cb As New OleDb.OleDbCommandBuilder(daFinishes)
Dim dsNewRow As DataRow
Further (frustrating) info:
I have lifted the code from the app and set it up as a stand alone ... it works perfectly!
Same db, exactly the same syntax, etc. What might cause this ??
Help! It still does not work as part of the bigger project
<Bangs head on desk!> This comes up every other day! The Adapter (daFinishes, I assume) has to be initiated in every sub it appears in. You can't carry it over from form_load to another sub.</Bangs head on desk!>
Let me send you something for the lump on your head! I have read probably 30 articles about this problem and this is the first mention of re-initiating. Actually, I have seen countless examples that are exactly as mine ... no re-initializing. But, I made the change and it is working properly and I do thank you for it. And also for the quick reply.
There does remain a mystery. Note that I lifted the relevant code from the main app and put it in a 'stand alone' where it does work. In that stand alone, the 'select' is also in the 'form load' while the 'update' is in a different sub. In that case, it does work.
There are circumstances in which it isn't strictly necessary but it's simpler all round to get in the habit of doing it (if only to eradicate it as a potential problem further down the line) rather than trying to work out what those circumstances are! The frequency with which this problem comes up (and the size of my bumps) are a good indication that it's far better to be safe than sorry.