Hello everyone, I am not much of a programmer but have some limited experience with vb.NET, so please bear with me.
I have written the following code to create a dataset and fill it with three different tables, one of which is empty to begin with but is populated successfully within the dataset as the code executes (I have confirmed this).
However, when the modifications to the dataset are complete, the program will not update the database. Depending on which of numerous minor changes I have made to the line of code with the update command, I get one of the following two results:
- An error saying "Update unable to find TableMapping" or something along those lines.
- No errors, but I check the tables in the database after the procedure is complete and they are not updated.
Not sure if it is relevant, but only one of the tables (the one that is initially empty) is modified during this procedure.
Thanks in advance to anyone who can help; I have no idea what the problem is and this is driving me crazy!
Private Sub GenerateTMC() Dim strClient As String = F4.txtClientName.Text.Replace(" ", "_") 'create new table Dim sqlCreate As String Dim objCmd As New OleDbCommand Dim conn As New System.Data.OleDb.OleDbConnection conn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;data source=" & "C:\test.mdb" Dim strTable As String = strClient + "_TMC" sqlCreate = "CREATE TABLE " + strTable + " ([housenum] TEXT(10), [predir] TEXT(2), [streetname] TEXT(28), [suffix] TEXT(4), [postdir] TEXT(2), [designator] TEXT(4), [aptnum] TEXT(8), [leftovers] TEXT(10), [line2] TEXT(8), [city] TEXT(28), [state] TEXT(4), [zip] TEXT(10), [crrt] TEXT(4), [vacant] TEXT(5), [errormsg] TEXT(50), [errornum] TEXT(50))" conn.Open() objCmd = New OleDbCommand(sqlCreate, conn) objCmd.ExecuteNonQuery() conn.Close() 'create dataset, data adapter Dim ds As New DataSet Dim dsNewRow As DataRow conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb" conn.Open() Dim sqlFirst As String = "SELECT * FROM " + strClient + "_sub_compare" Dim sqlSecond As String = "SELECT * FROM " + strClient + "_cds_compare" Dim sqlThird As String = "SELECT * FROM " + strTable Dim da As OleDb.OleDbDataAdapter da = New OleDb.OleDbDataAdapter(sqlThird, conn) Dim cb As New OleDb.OleDbCommandBuilder(da) da.Fill(ds, strTable) da.SelectCommand.CommandText = sqlFirst da.Fill(ds, strClient + "_sub_compare") da.SelectCommand.CommandText = sqlSecond da.Fill(ds, strClient + "_cds_compare") conn.Close() 'modifying the dataset; all of this stuff works conn.Open() da.Update(ds, strTable) da.Dispose() conn.Close() End Sub