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!
-J
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