"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

"Coen" <cbus***@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

"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" <cbus***@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
>
>
>