Visual Basic&database, Can not cancel changes made using DataCombo!!!

Tom Jastrzebsk
#1 / 3

Can not cancel changes made using DataCombo!!!

VB6.0, ADO, Classical scenario:

two recordsets: first - rec2 (Suppliers) used to supply SupplierId value to
be inputted into a second recordset - rec1 (Products)

Problem:

Calling rec1.CancelBatch method does not cancel changes and return
rec1.EditMode to adEditNone UNLES you first move to next record then move
back to edited record.
HOVEVER, repositioning using code (rec1.MoveNext : rec1.MovePrevious or
rec1.Move (0) ) causes Update and CancelUpdate is no longer possible.
When changing SupplierId using DataCombo control rec1.EditMode does not
change from adEditNone to adEditInProgress

Is it a known problem/limitation?
Am I doing something wrong?

Please help.

Tom Jastrzebski

to replicate the problemm:
Create a new project, to References add Microsoft ActiveX DataObjects 2.0
Library
to Components add Microfoft ADO Data Control, Microsoft Data Grid Control
6.0 and
Microsoft Data List Controls 6.0
replace Form1 with the one attached below.

VERSION 5.00
Object = "{CDE57A40-8B86-11D0-B3C6-00A0C90AEA82}#1.0#0"; "MSDATGRD.OCX"
Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "MSADODC.OCX"
Object = "{F0D2F211-CCB0-11D0-A316-00AA00688B10}#1.0#0"; "MSDATLST.OCX"
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 3240
ClientLeft = 60
ClientTop = 345
ClientWidth = 12795
LinkTopic = "Form1"
ScaleHeight = 3240
ScaleWidth = 12795
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command3
Caption = "EditMode:"
Height = 375
Left = 240
TabIndex = 4
Top = 2640
Width = 2175
End
Begin VB.CommandButton Command2
Caption = "Update"
Height = 375
Left = 2640
TabIndex = 3
Top = 2640
Width = 1935
End
Begin MSDataListLib.DataCombo DataCombo1
Height = 360
Left = 2640
TabIndex = 2
Top = 2040
Width = 4335
_ExtentX = 7646
_ExtentY = 635
_Version = 393216
Text = "DataCombo1"
BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
Name = "MS Sans Serif"
Size = 9.75
Charset = 238
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
End
Begin VB.CommandButton Command1
Caption = "Cancel"
Height = 375
Left = 4800
TabIndex = 1
Top = 2640
Width = 2175
End
Begin MSAdodcLib.Adodc Adodc1
Height = 330
Left = 240
Top = 2040
Width = 2175
_ExtentX = 3836
_ExtentY = 582
ConnectMode = 0
CursorLocation = 3
IsolationLevel = -1
ConnectionTimeout= 15
CommandTimeout = 30
CursorType = 3
LockType = 3
CommandType = 2
CursorOptions = 0
CacheSize = 50
MaxRecords = 0
BOFAction = 0
EOFAction = 0
ConnectStringType= 1
Appearance = 1
BackColor = -2147483643
ForeColor = -2147483640
Orientation = 0
Enabled = -1
Connect = "Provider=Microsoft.Jet.OLEDB.3.51;Persist
Security Info=False;Data Source=C:\Program Files\Microsoft Visual
Studio\VB98\Nwind.mdb"
OLEDBString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist
Security Info=False;Data Source=C:\Program Files\Microsoft Visual
Studio\VB98\Nwind.mdb"
OLEDBFile = ""
DataSourceName = ""
OtherAttributes = ""
UserName = ""
Password = ""
RecordSource = "Products"
Caption = "Adodc1"
BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
Name = "MS Sans Serif"
Size = 8.25
Charset = 238
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
_Version = 393216
End
Begin MSDataGridLib.DataGrid DataGrid1
Bindings = "Form1.frx":0000
Height = 1455
Left = 120
TabIndex = 0
Top = 240
Width = 12495
_ExtentX = 22040
_ExtentY = 2566
_Version = 393216
HeadLines = 1
RowHeight = 15
FormatLocked = -1 'True
BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851}
Name = "MS Sans Serif"
Size = 8.25
Charset = 238
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
Name = "MS Sans Serif"
Size = 8.25
Charset = 238
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ColumnCount = 10
BeginProperty Column00
DataField = "ProductID"
Caption = "ProductID"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column01
DataField = "ProductName"
Caption = "ProductName"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column02
DataField = "SupplierID"
Caption = "SupplierID"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column03
DataField = "CategoryID"
Caption = "CategoryID"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column04
DataField = "QuantityPerUnit"
Caption = "QuantityPerUnit"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column05
DataField = "UnitPrice"
Caption = "UnitPrice"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column06
DataField = "UnitsInStock"
Caption = "UnitsInStock"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column07
DataField = "UnitsOnOrder"
Caption = "UnitsOnOrder"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column08
DataField = "ReorderLevel"
Caption = "ReorderLevel"
BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}
Type = 0
Format = ""
HaveTrueFalseNull= 0
FirstDayOfWeek = 0
FirstWeekOfYear = 0
LCID = 1045
SubFormatType = 0
EndProperty
EndProperty
BeginProperty Column09
DataField
...

read more »

Thu, 16 Aug 2001 03:00:00 GMT

Tom Jastrzebsk
#2 / 3

Can not cancel changes made using DataCombo!!!

I looks like finally I found a working solution.

Here it goes:
1. open recordset using adLockBatchOptimistic (if doesn't seem to make a
difference if it's static or dynamic)
rec.Open cmd, , adOpenStatic, adLockBatchOptimistic

2. use following code to Add, Delete, Update and Cancel

Private Sub cmdCancel_Click()
rec.Move (0)
' canceling w/o first moving off added record causes an error if there is
any data control bound to recordset
' like TextBox or DataCombo (interesting that it doesn't apply to DataGrid)
If rec.EditMode = adEditAdd Then rec.MovePrevious
rec.CancelBatch
End Sub

Private Sub cmdUpdate_Click()
rec.UpdateBatch
End Sub

Private Sub cmdDelete_Click()
rec.Delete
rec.UpdateBatch
End Sub

Private Sub cmdAddNew_Click()
rec.AddNew
End Sub

Check it out!

Regards,

Tom Jastrzebski

Mon, 20 Aug 2001 03:00:00 GMT