G
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
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