Adding New Record to Access Database using VB.Net

[TechHelp]

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(Object sender, EventArgs e) in c:\inetpub\wwwroot\LearningASPNET\AddEditSaveRecords.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.AppSettings("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.Text)

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