Hello Guys,

I am doing an update and add new product to an Access 2002 database (NorthWind.mdb). When the user hit the save button, it'll excute the btnSave_Click procedure I attached below. On the updating, it did beatifully; for example, when I change the Unit Price or Unit In Stock of a particular product, it updated correctly. I am only having problem with adding a new record, for example adding product: "Smoke Turkey", Unit Price = 3.50, UnitInStock = 100, I got error below:

PLEASE HELP!!!...


*************** ERROR

System.Data.OleDb.OleDbException: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at LearningASPNET.AddEditSaveRecords.btnSave_Click(Ob ject sender, EventArgs e) in c:\inetpub\wwwroot\LearningASPNET\AddEditSaveRecor ds.aspx.vb:line 525

***************




*************** CODE

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSet tings("OleDbConnectionString"))
    Dim da As New OleDb.OleDbDataAdapter

    '------------------------------
    '~~~~ Parametters: In SQL Statement, parameters are named, using a prefix of @.
    'For an OleDBCommand, use question marks instead of naming the parmaters.
    Dim strSQL = "UPDATE Products Set " _
                        & "ProductName = ?, " _
                        & "UnitPrice = ?, " _
                        & "UnitsInStock = ? " _
                        & " WHERE ProductID = ?"

    Dim cmdUpdate As New OleDbCommand(strSQL)
    cmdUpdate.Connection = cnn
    '------------------------------

    '------------------------------
    '~~~~Parameters: Adding to Command
    'Add parameters to the DataAdapter Command object's parameters collection.

    Response.Write(cnn)

    With cmdUpdate.Parameters
        'The last parameter in this overload of Add allows us to specify a column name to bind to
        .Add("ProductName", OleDbType.VarChar, 40, "ProductName")
        .Add("UnitPrice", OleDbType.Currency, 8, "UnitPrice")
        .Add("UnitsInStock", OleDbType.SmallInt, 2, "UnitsInStock")
    End With

    '~~~~Parameters: SourceVersion
    'Use this syntax for ?ProductID, if we needed to be able to update the ProductID in the DataSet
    Dim prm As OleDbParameter = cmdUpdate.Parameters.Add("?ProductID", OleDbType.Integer, 4, "ProductID")
    prm.SourceVersion = DataRowVersion.Original
    '------------------------------

    '------------------------------
    '~~~~ Using a Stored Procedure
    Dim cmdInsert As New OleDbCommand("procProductInsert")
    cmdInsert.CommandType = CommandType.StoredProcedure
    cmdInsert.Connection = cnn

    With cmdInsert.Parameters
        'The last parameter in this overload of Add allows you to specify a column name to bind to
        .Add("CategoryID", OleDbType.Integer, 4, "CategoryID")
        .Add("ProductName", OleDbType.VarChar, 40, "ProductName")
        .Add("UnitPrice", OleDbType.Currency, 8, "UnitPrice")
        .Add("UnitsInStock", OleDbType.SmallInt, 2, "UnitsInStock")
        .Add("ProductID", OleDbType.Integer, 4, "ProductID")

    End With

    '~~~~Parameters: Direction
    'Because the sourceColumn was set this output parameter will insert the correct new identity
    ' ProductID retrieved from the database
    cmdInsert.Parameters("ProductID").Direction = ParameterDirection.Output
    '~~~~DataAdapter: Adding Commands
    da.UpdateCommand = cmdUpdate
    da.InsertCommand = cmdInsert

    '~~~~DataAdapter: Update
    'DataSet Updates always operate on one table at a time
    Try
        'Normally, we won't need the next step: Make sure the stored procedure has been created.

        CreateStoredProcProductInsert()
        cnn.Open()
        da.Update(mds, "Products") ' PROBLEM IS HERE ( ERROR LINE )
        mds.AcceptChanges()
        '---------------------------

        'Reset the autincrement seed to the currentl max ProductID in the database
        SetProductIDAutoIncrement() ' THIS WORKED CORRECTLY

        'Save the dataset
        Session("mds") = mds
        'Clear the list of changed items
        lboChanges.Items.Clear()
        'Refresh the Product details (this should only matter if it's a new product, getting the actual
        ' ProductID from the database.)
        RefreshProductDetails(lboProducts.SelectedItem.Tex t)

    Catch ex As Exception
        lblError.Text() = ex.ToString
    Finally
        'Release the database connection to the pool
        cnn.Close()
    End Try

End Sub
Private Sub CreateStoredProcProductInsert()
    'Create a stored procedure for insertting new Products.
    Dim cnn As New OleDbConnection(ConfigurationSettings.AppSettings( "OleDbConnectionString"))
    Dim cmd As New OleDbCommand

    cmd.Connection = cnn
    cmd.CommandType = CommandType.Text

    'retrieve the record with highest value from the Products Table
    Dim strSQL = "CREATE PROCEDURE procProductInsert(" _
        & " CategoryID INTEGER, ProductName VARCHAR(40), " _
        & " UnitPrice CURRENCY =NULL, UnitsInstock SMALLINT =NULL," _
        & " ProductID INTEGER OUTPUT AS INSERT INTO Products" _
        & " (CategoryID, ProductName, UnitPrice, UnitsInStock)" _
        & " VALUES(CategoryID, ProductName, UnitPrice, UnitsInStock)" _
        & " SET ProductID = ProductID;"

    cmd.CommandText = strSQL

    cnn.Open()

    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        'This will fail if the procedure already exists, and that's fine.
    Finally
        cnn.Close()
    End Try

End Sub
***************


Thank you very much once again.

--Iris

did you fix it