Using TableAdapter to insert rows into a dataset does not do anything

Question

I have a question similar to this one, but reading the (accepted) answer didn't give me much insight, so I'm hoping to state it more clearly and get a clearer response back.

I'm attempting to insert a data row into a table. I'm using TableAdapter's custom "insert nonQuery" that I wrote (it works, I tested) to accept some parameters. I'm fairly new to this business of communication with a database via .NET and what I'm doing is probably wrong by design. My questions are why is it wrong and what's the right way to do it? Both are equally important, IMO.

Here's some sample VB code I wrote:

Dim arraysTableAdapter As New UnitTestsDataSetTableAdapters.ArraysTableAdapter

Try
    arraysTableAdapter.InsertArray("Test Array", 2, 1, 2, "Test user")
Catch ex As SqlException
    MsgBox("Error occured when trying to add new array." _
          & vbNewLine & vbNewLine _
          & ex.Message)
End Try

...and that's pretty much it. There is no exception raised, my table does not get a new row inserted. Everything is just the way it was before I called the InsertArray method. When I test my query in the QueryBuilder with the same parameters, a new row gets added to the database.

Now, I do understand some of the reasons this would not work. I understand that I need to create and select a row in my DataSet (no idea how to do it) in order to tell the TableAdapter what it's adding the data to. Or at least I got that impression from reading the vast abyss of forums.

I would really like to use TableAdapter at some point, because it knows that .InsertArray exists and it knows which parameters it likes. I could try and do it using

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand

    con.ConnectionString = connString
    con.Open()

    cmd.CommandText = "INSERT ...... all that jazz"

but it's not nearly clean enough for how clean I like my code to be. So, is there any way to do what I'm trying to do the way I'm doing it? In other words, how do I use the neat structure of a TableAdapter to communicate to my DataSet and put a new row in it?

Thanks in advance!

Answer 1

There were two things that were wrong:

1. (minor issue) I did not have a DataTable filled from the TableAdapter (see code below)

2. (major, sneaky issue) My method worked from the very beginning. There is nothing extra to be added except for the line above. However, the ConnectionString of arraysTableAdapter was pointing my program (automatically, by default) to a wrong location. Once I manually set the ConnectionString, it worked perfectly.

Here's my complete code:

    Dim connString As String = "Some correct connection string"
    Dim arraysDataTable As New SpeakerTestsDataSet.ArraysDataTable

    Dim arraysTableAdapter As New UnitTestsDataSetTableAdapters.ArraysTableAdapter

    'Set the correct connection string'
    arraysTableAdapter.Connection.ConnectionString = conn 

    'Fill table from the adapter'
    arraysTableAdapter.Fill(arraysDataTable)

    Try
        arraysTableAdapter.Insert("Test", 2, 1, 2, Now, Now, "Me")
    Catch ex As Exception
        MsgBox("Error occured when trying to add new array." _
              & vbNewLine & vbNewLine _
              & ex.Message)
    End Try

Answer 2

The accepted answer in the question you linked to is correct, but sometimes saying it in different words helps:

A TableAdapter is used to communicate between a DataTable (there can be one or more DataTables in a DataSet) and a database. It can pull data from a database and add it to a DataTable and it can send data from a DataTable to the database. It's purpose is to create and execute the SQL code required to make this communication work.

You are trying to use the TableAdapter to directly add data to your DataTable. This will not work. Instead, you should use the methods that come with the DataTable to add a new row to the DataTable and then (if necessary) use your TableAdapter to send that row to a database.

For instance, with a Dataset called DataSet1 that contains a DataTable called DataTable1 that has three text columns you can add a record like this:

    Dim d As New DataSet1
    d.DataTable1.AddDataTable1Row("value1", "value2", "value3")

That AddDataTable1Row method is automatically created for you, and I think is what you are looking for.