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

Tried a few different ideas I had this morning and still nothing.

I should add a couple of things that may be relevant.

1. I am using OleDB and the tables are in a Microsoft Access database.
2. I am using vb.NET 2008 Express Edition.
3. All three of the tables are unrelated, although they share a few of the same columns.
4. I'm not positive that the problem has to do with the dataset containing multiple tables, but it seems to be the only possibility.

I realize this is probably an obscure and specific problem, but I'd appreciate any assistance I can get. Thanks again in advance.

-J

ok, I'm newer but I'll take a stab at it, when I've done the oledb connection I don't ever remember using the & symbol where you do here:

conn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;data source=" & "C:\test.mdb"

I've done

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\test.mdb"

Yeah I'm not sure why I did that, but they are the same thing. The & symbol just combines the two strings, so the result is the same. It successfully connects to the database in order to fill the datasets and create the new table, so I guess it must be working.

Thank you for your input though, I really appreciate the effort!

-J

Figured it out; used a different data adapter for each table in the dataset. Thanks to the dude who posted a suggestion.

-J

Figured it out; used a different data adapter for each table in the dataset. Thanks to the dude who posted a suggestion.

-J

i have the same problem can you post a sample code

thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.