CODEWITHSUNDEEP
asp.net.netdatabasevb.netms-access
Ben Elgar
Ben Elgar

Reputation: 785

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

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

Upvotes: 3

Views: 2465

Answers (1)

Casey Wilkins
Casey Wilkins

Reputation: 2595

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

Upvotes: 4

Related Questions