OLEDB: UpdateCommand fails

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

cbusker
Fri Mar 31 09:48:01 CST 2006

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.

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

"Coen" wrote:

> 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

Jim
Fri Mar 31 10:10:52 CST 2006

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.

"Coen" <cbusker@newsgroups.microsoft.com> wrote in message
news:7F867D22-D57A-4482-993B-E3D190E8A155@microsoft.com...
>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.
>
> --------------------------------------------------------------------------------------------
>
> "Coen" wrote:
>
>> 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

cbusker
Fri Mar 31 14:46:02 CST 2006

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

"Jim Hughes" wrote:

> 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.
>
> "Coen" <cbusker@newsgroups.microsoft.com> wrote in message
> news:7F867D22-D57A-4482-993B-E3D190E8A155@microsoft.com...
> >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.
> >
> > --------------------------------------------------------------------------------------------
> >
> > "Coen" wrote:
> >
> >> 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
>
>
>