How to add a record to a database in asp .NET

Question

I currently have a Microsoft Access database which is being accessed through an ASP .NET front end. I am trying to add a row to a table, but this doesn't appear to be working, the code I am using is shown below.

    Sub prcChoose()
        Dim sql As String
        Dim da As New OleDbDataAdapter
        Dim conn As New OleDbConnection
        Dim comm As New OleDbCommand
        Dim cb As New OleDbCommandBuilder(da)
        ds = New DataSet

        conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("dbConnection").ConnectionString)
        sql = "SELECT * FROM ParentPreference"

        conn.Open()
        da = New OleDbDataAdapter(sql, conn)
        da.Fill(ds, "Choice")
        Dim NewRow As Data.DataRow = ds.Tables("Choice").NewRow()
        NewRow.Item(0) = txtUser.Text
        NewRow.Item(1) = dropdown.SelectedValue
        ds.Tables("Choice").Rows.Add(NewRow)
        ***da.UpdateCommand = cb.GetUpdateCommand()***
        da.Update(ds, "Choice")

    End Sub

I receive different error messages depending on whether or not the line enclosed by 3 asterisks is included. Without it I receive "Update requires a valid InsertCommand when passed DataRow collection with new rows." and with it I receive "The DataAdapter.SelectCommand property needs to be initialized." I have spent hours trying to figure out where I'm going wrong and I haven't had any success.

Thanks in advance!

EDIT: On the advice of Casey below I added the following lines of code:

    da.InsertCommand = cb.GetInsertCommand
    da.DeleteCommand = cb.GetDeleteCommand

I added these lines just below the code enclosed by asterisks above. But I still receive the same error message. The DataAdapter.SelectCommand property needs to be initialized. This error occurs on the following line

da.InsertCommand = cb.GetInsertCommand

Casey Wilkins · Accepted Answer · 2012-02-01 13:39:51Z

4

Shooting from memory here as I can't currently get to Visual Studio to check your code, but..

There are several commands that the Data Adapter needs in order to update/delete/insert to your Access table. It looks like you have used a Command Builder to build the Update command, which the Data Adapter will use for rows that need updating. In addition, you also need to use the Command Builder to build Insert and Delete commands:

da.InsertCommand = cb.GetInsertCommand
da.DeleteCommand = cb.GetDeleteCommand

The "Update requires a valid InsertCommand" error is telling you that the Data Adapter does not know how to insert the new rows you created in Data Table to the Access DB because you haven't told it what Insert Command to use. Inserting the two lines above should fix that problem.

The InsertCommand, DeleteCommand, and UpdateCommand on the Data Adapter are just SQL statements that the Data Adapter uses to update your underlying data source, in this case Access. You can manually supply these commands or use the CommandBuilder for simple cases like this one.

When you tell the DataAdapter to Update, it loops through the Data Table and for each row determines if that row has been added, deleted, or updated. It then looks for the appropriate command to execute the SQL statement to Insert, Delete, or Update. If you haven't provided the command, you get the error you are seeing.

EDIT: Here is your code updated to fix the "SelectCommand not initialized" error and a few other things:

Sub prcChoose()

    'Always create disposable objects with a Using statement.
    'This ensures that the objects will be disposed properly.
    Using conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("dbConnection").ConnectionString), _
        da As New OleDbDataAdapter("SELECT * FROM ParentPreference", conn), _
        cb As New OleDbCommandBuilder(da)

        'Open connection.
        conn.Open()

        'Create new dataset.
        Dim ds As New DataSet

        'Fill dataset.
        da.Fill(ds, "Choice")

        'Create new row and populate it.
        Dim NewRow As Data.DataRow = ds.Tables("Choice").NewRow()
        NewRow.Item(0) = txtUser.Text
        NewRow.Item(1) = dropdown.SelectedValue

        'Add row to Choice table.
        ds.Tables("Choice").Rows.Add(NewRow)

        'Use command builder to generate Update, Insert, Delete commands for DataAdapter.
        da.UpdateCommand = cb.GetUpdateCommand
        da.InsertCommand = cb.GetInsertCommand
        da.DeleteCommand = cb.GetDeleteCommand

        'Use dataadapter to update datasource.
        da.Update(ds, "Choice")


    End Using

End Sub

edited Feb 1, 2012 at 13:39

answered Jan 31, 2012 at 13:48

Casey Wilkins

2,5952 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ben Elgar Over a year ago

Thanks very much for the response, and I think I understand now. I inserted the code you recommended but, as I said in the edit, I receive the same error message. I don't know what it means or how I would go about initialising the DataAdapter.SelectCommand.

Casey Wilkins Over a year ago

Is the error being thrown by the DataAdapter or the CommandBuilder? The CommandBuilder uses the SelectCommand to generate the Insert/Update/Delete commands. Use the debugger to see if the DataAdapter has a valid SelectCommand right before the CommandBuilder is called.

Ben Elgar Over a year ago

I think it's being thrown by the DataAdaptor. It occurs on the da.InsertCommand = cb.GetInsertCommand line and the data adaptor does have a valid SelectCommand, at least I believe it's valid. It's "SELECT * FROM ParentPreference".

Casey Wilkins Over a year ago

I think I see it. You create a new OleDataAdapter at the top of the code, then you give that specific adapter to the CommandBuilder. But later your create another new DataAdapter, and that is the one that gets initialized with your SelectCommand. I'll update your code in my response.

Ben Elgar Over a year ago

Thank you! I finally understand! That's fixed the problem that's been holding me up for ages! I didn't realise the second assignment statement for the data adaptor was wiping it. Thanks again!

|

Collectives™ on Stack Overflow

How to add a record to a database in asp .NET

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related