OLEDB: UpdateCommand fails

[Guest]

Hi, can anyone help me with the following.
MS Access 2002 database, Northwind, Customer table.
In VB.NET using a datagrid and a dataset to fill the datagrid.
I'm trying to insert, update and delete records in the datagrid.
Inserts and deletes are working fine, but the updates are failing and I
can't find out why. Looks strange to me because I'm using inserts, updates en
deletes in the same way.

My Code:
Sub GridSaveDatagrid()
Dim cmdUpdate, cmdInsert, cmdDelete As OleDbCommand
Dim MySql_Insert, MySql_Update, MySql_Delete As String

Try
mDbConn.Open()
'Deletes
MySql_Delete = "DELETE FROM Customers WHERE CustomerID =
@CustomerID"
cmdDelete = New OleDbCommand(MySql_Delete, mDbConn)
cmdDelete.CommandType = CommandType.Text
With cmdDelete.Parameters
.Add("@CustomerID", OleDb.OleDbType.VarChar, 5, "CustomerID")
End With
mDa1.DeleteCommand = cmdDelete

'Updates
MySql_Update = "UPDATE Customers " & _
"SET CompanyName = @CompanyName, ContactName
= @ContactName, " & _
" City = @City, PostalCode =
@PostalCode " & _
"WHERE CustomerID = @CustomerID"
cmdUpdate = New OleDbCommand(MySql_Update, mDbConn)
cmdUpdate.CommandType = CommandType.Text
With cmdUpdate.Parameters
.Add("@CustomerID", OleDb.OleDbType.VarChar, 5, "CustomerID")
.Add("@CompanyName", OleDb.OleDbType.VarChar, 40,
"CompanyName")
.Add("@ContactName", OleDb.OleDbType.VarChar, 30,
"ContactName")
.Add("@City", OleDb.OleDbType.VarChar, 15, "City")
.Add("@PostalCode", OleDb.OleDbType.VarChar, 10, "PostalCode")
End With
mDa1.UpdateCommand = cmdUpdate

'Inserts
MySql_Insert = "INSERT INTO Customers (CustomerID, CompanyName,
ContactName, City, PostalCode) " & _
"VALUES (@CustomerID, @CompanyName,
@ContactName, @City, @PostalCode)"
cmdInsert = New OleDbCommand(MySql_Insert, mDbConn)
cmdInsert.CommandType = CommandType.Text
With cmdInsert.Parameters
.Add("@CustomerID", OleDb.OleDbType.VarWChar, 5, "CustomerID")
.Add("@CompanyName", OleDb.OleDbType.VarChar, 40,
"CompanyName")
.Add("@ContactName", OleDb.OleDbType.VarChar, 30,
"ContactName")
.Add("@City", OleDb.OleDbType.VarChar, 15, "City")
.Add("@PostalCode", OleDb.OleDbType.VarChar, 10, "PostalCode")
End With
mDa1.InsertCommand = cmdInsert

'Update de Customer table.
Dim updTable As DataTable = mDs.Tables("Customer")
mDa1.Update(updTable.Select(Nothing, Nothing,
DataViewRowState.Deleted))
mDa1.Update(updTable.Select(Nothing, Nothing,
DataViewRowState.ModifiedCurrent))
mDa1.Update(updTable.Select(Nothing, Nothing,
DataViewRowState.Added))



Catch ex As Exception
Console.WriteLine(ex.Message)
Stop
Finally
If Not mDbConn Is Nothing Then mDbConn.Close()
End Try

End Sub

 

[Guest]

I partly solved my own problem, but still having a question.

I turns out that replacing all the parameters "@ColumnName" in '?' solves my
problem. But why isn't it working in the '?' syntax? And I think it is
strange that with @ColumnName syntax, only the update command fails, inserts
and deletes work fine!!!

What is working:
MySql_Update = "UPDATE Customers " & _
"SET CompanyName = ?, ContactName = ?, City = ?,
PostalCode = ? WHERE CustomerID = ?"

cmdUpdate = New OleDbCommand(MySql_Update, mDbConn)
cmdUpdate.CommandType = CommandType.Text
With cmdUpdate.Parameters
.Add("@CompanyName", OleDb.OleDbType.VarChar, 40,
"CompanyName")
.Add("@ContactName", OleDb.OleDbType.VarChar, 30,
"ContactName")
.Add("@City", OleDb.OleDbType.VarChar, 15, "City")
.Add("@PostalCode", OleDb.OleDbType.VarChar, 10, "PostalCode")
.Add("@CustomerID", OleDb.OleDbType.VarChar, 5, "CustomerID")
End With
mDa1.UpdateCommand = cmdUpdate

Coen.

--------------------------------------------------------------------------------------------

 

[Jim Hughes]

With OleDB, Parameters have to be added to the collection in the order they
are defined in the commandtext. The names are meaningless.

You did change the order of the parameters from the first post to the
second. CustomerID went from first to last.

 

[Guest]

Ok Jim, thank you. This makes sense to me.
Regards Coen.